Using Application Programming Interfaces

About APIs

Oracle LSH includes a set of APIs that enable you to do most of the things you can do through the user interface, including creating, modifying, and installing objects.

You can call Oracle LSH APIs from source code in a defined Program in Oracle LSH. In this case, no additional security or setup is required.

If you have an Oracle LSH database account with certain privileges, you can also develop programs that call APIs in a tool outside of Oracle LSH; such as SAS, Oracle SQL Developer, or SQL*Plus. You can then see views of all the Oracle LSH data you need, including data from both the LSH (CDR) schema and, for classification data, the TMS schema. You can make the programs you write available to other people from the external tool. See Calling APIs from Outside the Oracle Life Sciences Data Hub.

This section contains the following topics:

Parent topic: Using Application Programming Interfaces

Example 1: Using APIs to Perform Multiple Tasks at Once

You can write a package that calls multiple APIs to do with one execution what it would take many tasks in the user interface (UI) to do; for example, create a Domain, an Application Area inside the Domain, a Work Area inside the Application Area, and multiple Load Sets, Tables, and Programs, each with a definition in the Application Area and an instance in the Work Area, and install the Work Area. If you have a standard structure for Project/Therapeutic Area Domains, for example, you may want to work this way. However, remember that you can also copy a Domain and all its contents at once in the user interface.

Using APIs is even more attractive when you want to create, for example, multiple objects with variations or large complex objects such as Report Sets. You can create a spreadsheet to store all the variable information and load its data into an Oracle LSH Table instance using a Text Load Set. In your program, use a loop to read all the spreadsheet data and call the relevant Oracle LSH APIs to create the objects.

Parent topic: About APIs

Example 2: Calling APIs from an External System's UI

Example 2: Calling APIs from an External System's UI: You may want to allow people in your company to perform actions on Oracle LSH objects from an external system.

For example, instead of requiring that SAS developers check out Source Code in Oracle LSH before opening the SAS IDE from an Oracle LSH Program, you may want to add a button to the SAS user interface that calls the API for checking out the Source Code object when clicked. Then, if the program is located in a schema with Execute privileges on the security API, any user with SAS, a database account in Oracle LSH, and normal Oracle LSH object security privileges on the Source Code definition, can check out the Source Code definition directly from SAS.

Parent topic: About APIs

Understand Oracle LSH Functionality

To use Oracle LSH APIs, you must understand basic Oracle LSH functionality including:

Object Ownership. You must create container objects before creating the objects they contain, because to create any object you must identify its namespace (parent or container) object. For example, begin by defining a Domain, then an Application Area, then a Work Area, and then create a Table definition in the Application Area and an instance of it in the Work Area. You can use a single API to create both the Table definition and an instance of it. For details, see "Object Ownership" in the Oracle Life Sciences Data Hub Application Developer's Guide.
Installation. You must create an instance of an object definition and install it before you can execute or store data in the object.
Mapping. All executable objects must contain one Table Descriptor for each Table instance they read from and write to, and the Table Descriptors and Table instances must be mapped. For details, see "Defining and Mapping Table Descriptors" in the Oracle Life Sciences Data Hub Application Developer's Guide.
Checking Objects In and Out. You must check objects out to modify them and check them in before you install and use them. For details, see "Understanding Object Versions and Checkin/Checkout" in the Oracle Life Sciences Data Hub Application Developer's Guide.
Security. All objects require user group assignments to control user access. For details, see "Applying Security to Objects and Outputs" in the Oracle Life Sciences Data Hub Application Developer's Guide.
Classification. To enable objects to appear in the Reports tab of the user interface for end users to run them and view their outputs, you must classify them. Classifications can also be used in searching for objects. For details, see "Classifying Objects and Outputs" in the Oracle Life Sciences Data Hub Application Developer's Guide.
Validation. Objects should be validated according to your company policy whether they are created in the user interface or with APIs. For details, see "Validating Objects and Outputs" in the Oracle Life Sciences Data Hub Application Developer's Guide.
Object-Specific Information. Further information on each object type is included in other chapters of the Oracle Life Sciences Data Hub Application Developer's Guide.

Parent topic: About APIs

Tips

The section provides advice on committing APIs and getting attribute values for namespace objects. It contains the following topics:

Parent topic: Using Application Programming Interfaces

Committing APIs

The standard parameter P_COMMIT is found in every API; see Standard Parameters. Its default value is FALSE, which prevents the API from committing. This is useful if you are creating multiple objects in a single program execution, to enable you to do a rollback. However, if you want the API to commit, pass the value FND_API.G_TRUE.

Parent topic: Tips

Get Attribute Values for Namespace Objects

Many APIs require internal IDs for the namespace object of the object you are creating. Use Get a Naming Version Object. to get these details. However, you must first determine what the namespace object is, using the object ownership diagram and explanation in the Oracle Life Sciences Data Hub Application Developer's Guide or Oracle Health Sciences Data Management Workbench Study Setup Guide, then query for the namespace object ID and object version using its name and its namespace's name and object type, in CDR_DF_NAMING_V.

Parent topic: Tips

Calling APIs from Outside the Oracle Life Sciences Data Hub

This section contains the following topics:

Parent topic: Using Application Programming Interfaces

Security Setup Required

To run any API package from a tool outside of Oracle LSH, such as SAS, SQL Developer, or SQL*Plus, your system administrator needs to do the following:

Set up an Oracle LSH database account linked to your LSH user account; see "Creating Database Accounts" in the Oracle Life Sciences Data Hub System Administrator's Guide.
Grant your Oracle LSH database account Execute privileges on the API security package CDR_PUB_API_INITIALIZATION.
To run APIs that insert, delete, or modify Oracle LSH classification hierarchy terms, you need security access to the Oracle Thesaurus Management System (TMS) instance that is installed as part of Oracle LSH. The Oracle LSH classification system is based on TMS.
Note:
This step is not required for Oracle DMW custom programs. DMW does not use the LSH classification system.

Ask your system administrator to run the script tmsadduser.sql to do the following:
- Create a TMS user account with the same name as your Oracle LSH database account so that your account is entered in the TMS_ACCOUNTS and OPA_ACCOUNTS tables.
- Give your TMS user account superuser privileges in the TMS_ACCOUNTS table.
- Grant your TMS user account the TMS_MAINTAIN_PRIV database role.

Parent topic: Calling APIs from Outside the Oracle Life Sciences Data Hub

Calling the Security API Package

For every program that you run from outside Oracle LSH to call an Oracle LSH API, you must first call a special security API: CDR_PUB_API_INITIALIZATION. This API contains three functions:

EnableApis
DisableApis
AreApisEnabled

When you initialize any API, the AreApisEnabled function of the security API, CDR_PUB_API_INITIALIZATION, is called to check the calling program. If the program does not have the EnableAPIs flag set to True, the initialization fails.

To set the EnableApis flag to True, call the EnableApis function from your program. To call the EnableApis function, you need a schema/user account with an Execute privilege on the CDR_PUB_API_INITIALIZATION API granted by a system administrator.

Therefore, when you write a program that calls an API and is intended for use outside Oracle LSH, set the EnableApis flag to True in your program and then set it to False at the end to force the security check on the schema the next time the program is run:

Begin the body with the following code to call the function to enable APIs:
call CDR_PUB_API_INITIALIZATION.enableApis (arguments);
The arguments are described in CDR_PUB_API_INITIALIZATION itself.
At the end of the body, disable APIs with the following code:
CDR_PUB_API_INTIALIZATION.disableApis (arguments);

See Example 1-1.

Parent topic: Calling APIs from Outside the Oracle Life Sciences Data Hub

Calling APIs from SAS

If you need to call multiple APIs from SAS, you may want to use a PL/SQL wrapper around the API calls so that you only call PL/SQL once. The Oracle Life Sciences Data Hub Application Developer's Guide has two examples. In the Report Sets chapter, see "Passing Values from a Program Instance to the Report Set for Post-Processing" and in the Programs chapter see "Calling an API to Capture Output Parameter Values."

Parent topic: Calling APIs from Outside the Oracle Life Sciences Data Hub

Using a Permanent Schema for Deploying Programs that Call APIs

When you develop a program outside Oracle LSH that will call Oracle LSH APIs, you can use your own schema in the external tool (such as SQL*Plus, SQL Developer, or SAS) to run and test the program, if you have Execute privileges on CDR_PUB_API_INITIALIZATION. When you are ready to allow other people to run it, copy it into a different location.

Oracle recommends setting up one or more permanent, publicly available schemas in the Oracle LSH database for the purpose of compiling and storing programs that call Oracle LSH APIs. Grant each schema Execute privileges on CDR_PUB_API_INITIALIZATION. This approach has the following advantages:

If a user manually runs your program, he or she must enter the program location and name explicitly. This will be much easier if the user knows which schema contains such programs.
If you set up the program to run automatically when a user clicks a button in the external system's user interface, for example, you must hardcode the program's name and location into the code.
You can grant Execute on CDR_PUB_API_INITIALIZATION to a controlled number of schemas.

Parent topic: Calling APIs from Outside the Oracle Life Sciences Data Hub

Calling APIs from Defined Programs

If you develop and run a Program that calls an API within Oracle LSH—that is, in the defined Source Code of a defined Program object—no security is required beyond normal Oracle LSH object security. You do not need Execute privileges on the CDR_PUB_API_INITIALIZATION API, and you do not need to enable APIs in your Program code.

Note:

Within Oracle LSH, the calls to CDR_PUB_API_INITIALIZATION are unnecessary and in fact a program that includes such a call will not compile because the Work Area schema does not have Execute privileges on CDR_PUB_API_INITIALIZATION.

You do need to install the Program before you can run it, as you do any defined Program in Oracle LSH.

You can write packages in an Oracle LSH Program that do anything with APIs that you could do in a package outside Oracle LSH. For example, you could create an instance of a Program definition whose Source Code created a Work Area, several Load Sets, and a Program to merge the data, instead of defining the Work Area, Load Sets and Program through the Oracle LSH user interface.

Parent topic: Using Application Programming Interfaces

Code Example Using Security and Error Message APIs

There are two utility Oracle LSH APIs that you call in conjunction with other Oracle LSH APIs:

CDR_PUB_API_INITIALIZATION. This API is required for developing and running programs that call any Oracle LSH API from outside Oracle LSH. See Calling the Security API Package for further information.
CDR_PUB_MSG_PUB. This API returns error messages from other Oracle LSH APIs called in the same package.

The following code provides an example of calling the API to define a Work Area and each of the utility APIs.

Example 1-1 Program that Calls the API to Define a Work Area and Calls the Security and Error Message APIs

CDR_PUB_DF_WORKAREA.CREATEWORKAREA (
                   P_API_VERSION=>1,
                   P_INIT_MSG_LIST=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
P_COMMIT=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
                   P_VALIDATION_LEVEL=>CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,
X_RETURN_STATUS  => X_RETURN_STATUS,
X_MSG_COUNT    => X_MSG_COUNT,
X_MSG_DATA    => X_MSG_DATA,
PIO_SOURCECDRNAMING =>VARWANSOBJ,
PIO_WORKAREAOBJTYPE =>VARWAOBJ,
PI_DEFCLASSIFICATIONCOLL => NULL);
      IF X_RETURN_STATUS <> 'S' THEN
       DBMS_OUTPUT.PUT_LINE('ERROR FOUND IN CREATEPROGRAM');
     END IF ;
     X_MSG_COUNT := CDR_PUB_MSG_PUB.COUNT_MSG(                                  
                   P_API_VERSION=>1,                   P_INIT_MSG_LIST=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
P_COMMIT=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
                   P_VALIDATION_LEVEL=>CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL
                   );
     IF X_MSG_COUNT >= 1 THEN
     FOR I IN 1..X_MSG_COUNT LOOP
        IF I =1 THEN
          X_MSG_DATA := CDR_PUB_MSG_PUB.GET(
                    P_API_VERSION=>1,
                   P_INIT_MSG_LIST=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
P_COMMIT=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
                   P_VALIDATION_LEVEL=>CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,
P_MSG_INDEX =>CDR_PUB_MSG_PUB.G_FIRST,
P_ENCODED =>CDR_PUB_DEF_CONSTANTS.G_FALSE);
        ELSIF I = X_MSG_COUNT THEN
          X_MSG_DATA := CDR_PUB_MSG_PUB.GET(
                    P_API_VERSION=>1,
                   P_INIT_MSG_LIST=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
P_COMMIT=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
                   P_VALIDATION_LEVEL=>CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,
P_MSG_INDEX =>CDR_PUB_MSG_PUB.G_LAST,
P_ENCODED =>CDR_PUB_DEF_CONSTANTS.G_FALSE);
        ELSE
          X_MSG_DATA := CDR_PUB_MSG_PUB.GET(
                   P_API_VERSION=>1,
                   P_INIT_MSG_LIST=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
P_COMMIT=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
                   P_VALIDATION_LEVEL=>CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,
P_MSG_INDEX =>CDR_PUB_MSG_PUB.G_NEXT,
P_ENCODED =>CDR_PUB_DEF_CONSTANTS.G_FALSE);
        END IF ;
        DBMS_OUTPUT.PUT_LINE('MESSAGE:'||I ||' : '|| X_MSG_DATA);
     END LOOP;
    END IF;
    CDR_PUB_API_INITIALIZATION.DISABLEAPIS(
                   P_API_VERSION=>1,
                   P_INIT_MSG_LIST=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
P_COMMIT=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
                   P_VALIDATION_LEVEL=>CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,
X_RETURN_STATUS  => X_RETURN_STATUS,
X_MSG_COUNT    => X_MSG_COUNT,
X_MSG_DATA    => X_MSG_DATA);
EXCEPTION
WHEN OTHERS THEN
  CDR_PUB_API_INITIALIZATION.DISABLEAPIS(
                   P_API_VERSION=>1,
                   P_INIT_MSG_LIST=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
P_COMMIT=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
                   P_VALIDATION_LEVEL=>CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,
X_RETURN_STATUS  => X_RETURN_STATUS,
X_MSG_COUNT    => X_MSG_COUNT,
X_MSG_DATA    => X_MSG_DATA);
END MY_PROCEDURE;
 
BEGIN -- PACKAGE INIT BLOCK
CDR_PUB_API_INITIALIZATION.ENABLEAPIS(
                   P_API_VERSION=>1,
                   P_INIT_MSG_LIST=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
P_COMMIT=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
                   P_VALIDATION_LEVEL=>CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,
X_RETURN_STATUS  => X_RETURN_STATUS,
X_MSG_COUNT    => X_MSG_COUNT,
X_MSG_DATA    => X_MSG_DATA);
END MY_PACKAGE;

Parent topic: Using Application Programming Interfaces