1. Adapter Toolkit Guide
  2. Using APIs to Create Required Metadata Objects

4 Using APIs to Create Required Metadata Objects

Adapter Domains and the objects they contain are not accessible through the user interface. There is a public PL/SQL API for creating each of the objects required by an adapter.

Oracle Life Sciences Data Hub stores all the program code, parameters, and tables required for an adapter as defined objects within an Adapter Area which itself is contained in an Adapter Domain. Figure 1-1 shows the defined objects.

Additional information on APIs is available in the Oracle Life Sciences Data Hub Application Programming Interface Guide.

Call these APIs in the following order:

  • Retrieving IDs
    Many APIs require IDs as input parameter values. You can retrieve these IDs from the database.
  • Creating a Technology Type
    To create a technology type, you must use an API.
  • Modifying a Technology Type
    If you need to modify your Technology Type, use the public API CDR_PUB_ATK_ADAPTER. MODIFYTECHTYPE.
  • Creating an Adapter Domain
    An Adapter Domain is a container object that holds all the definitional objects required for an adapter.
  • Modifying an Adapter Domain
    If you need to modify your Adapter Domain, use the public API CDR_PUB_ATK_ADAPTER. MODIFYADAPTERDOMAIN.
  • Creating an Adapter Area
    An Adapter Area is a container object that can hold all the definitional objects required for an adapter.
  • Modifying an Adapter Area
    If you need to modify your Adapter Area, use public API CDR_PUB_ATK_ADAPTER.MODIFYADAPTERAREA.
  • Assigning a User Group to the Adapter Area
    In order to create the rest of the required objects, you must belong to a user group that is assigned to the Adapter Area, and you must have a role within the user group that allows you to create and modify each of the required objects.
  • Creating a Work Area
    A Work Area contains instances of all the object definitions required for the adapter.
  • Creating a Program Definition and Instance
    You must create a Program definition in the Adapter Area to store the source code required for the adapter. You must create an instance of the Program definition in the Work Area.
  • Creating a Source Code Definition and Instance
    You must create at least one Source Code object definition inside the Program definition to contain the PL/SQL packages you write.
  • Creating a Variable
    If you need user input to create a Load Set, Data Mart, Program, or Business Area—for example, an Oracle Remote Location name and connection—you must define a Parameter to collect the information. For each Parameter, you must define a Variable for the Parameter to reference.
  • Creating a Parameter
    Use Parameters to allow users to enter values during the definition or execution of a Load Set, Data Mart, Program, or Business Area. You must create a defined Parameter object for each parameter required and handle the user input in your source code.
  • Creating a Parameter Set
    You may create one, two, or three Parameter Sets to collect user input during the definition, installation, or execution of a Load Set, Data Mart, or Program, or the launch of a visualization tool.
  • Creating a Parameter Instance in a Parameter Set
    For each Parameter definition you have created, create a Parameter instance in the appropriate Parameter Set.
  • Creating a Table Definition
    If you are creating a Load Set adapter and the external source data system has fixed data structures, you may want to define those data structures as Tables in the Adapter Area rather than uploading them or forcing the user to create them manually each time a user creates a Load Set.
  • Installing the Work Area
    After you have created the Adapter Domain and all the definitional objects within it, install the Work Area by calling the API CDR_PUB_DF_WORKAREA.INSTALLWACONTROLLER and the Program instance it contains.

Retrieving IDs

Many APIs require IDs as input parameter values. You can retrieve these IDs from the database.

See the following for details:

Parent topic: Using APIs to Create Required Metadata Objects

Getting Your Company ID

The company ID is part of the primary key for every object.

To get your company ID, run the API CDR_PUB_DEF_FACTORY_UTILS.GETCOMPANYID.

Save the value for repeated use or create a local variable for it.

Parent topic: Retrieving IDs

Getting an Object's Prref_Id and Prref_Ver

Use the following query to retrieve these values:

select prref_id, prref_ver from cdr_program_refs_v
where COMPANY_ID = <your_company_id> and 
WA_OBJ_ID = <the_adapter_work_area_id> and 
MASTER_PRREF_OBJ_ID = <the_obj_id_of_the_object> and 
MASTER_PRREF_OBJ_VER = <the_obj_ver_of_the_object>;

The primary purpose of the Prref ID is to provide an execution context for executable objects contained in complex objects, such as Program instances contained in Report Set or Workflow instances. In these cases, the Master Prref Obj ID is the Object ID of the Report Set or Workflow instance that owns the Program instance.

For consistency, all object instances must have a Prref ID. In the case of object instances located directly in a Work Area, the object's Master Prref Obj ID is the same as its Object ID. For example, the Master Prref ID of a Program instance contained in a Work Area is the same as its Object ID; so if you know its Object ID, you know its Prref ID.

Note:

When you create an object using an API, save its Object ID, which is an output parameter value. You may need the ID to get the object's Prref ID or to create child objects.

Parent topic: Retrieving IDs

Creating a Technology Type

To create a technology type, you must use an API.

See the following for details:

Parent topic: Using APIs to Create Required Metadata Objects

Call the Create Technology Type API

To create an Technology Type, call the public API CDR_PUB_ATK_ADAPTER.POPULATETECHTYPES.

Its signature is:

PROCEDURE populateTechTypes (
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE
,p_commit IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE
,p_validation_level IN NUMBER default CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
, pio_techTypeRow IN OUT NOCOPY cdr_tech_types%rowtype
);

param pio_techTypeRow is a mandatory parameter of row type cdr_tech_types table that contains object attributes. Enter values as follows:

  • tech_type_id. Contact Oracle Support to get a 12-digit ID for your technology type that is unique across all adapters developed for use with Oracle Life Sciences Data Hub.

    Note:

    Make a note of the number you enter; you will need it when you create an Adapter Area.
  • tech_name_rc. Enter a name for the new technology type you are creating. You must add the name to the CDR_TECH_TYPES lookup (see Adding Lookup Values).
  • tech version. Enter the correct version of the external system.
  • service_type_rc. The service type required for this technology type. You must add this service type value to the CDR_SERVICE_TYPES lookup (see Adding Lookup Values).
  • build_ide_cfg_function. (Visualization and Program adapters only.) Enter the name of your Pre-installation function, if any; see Build_IDE_Cfg_Function.

    Note:

    For this function name and the others below, do not include the schema name. Enter only package_name.procedure_or_function_name
  • owb_operator. Enter the Oracle Warehouse Builder operator that Oracle LSH should use for this adapter:
    • If the external system is located on the database, enter: CdrPLSQLImmediate_1 These adapters use PL/SQL to execute their defined objects.
    • If the external system is located on an operating system, enter: CdrSERVICE_1. These adapters use the Distributed Processing (DP) Server to execute their defined objects; see the chapter on setting up services in the Oracle LSH System Administrator's Guide for more information.
    • If your adapter is for an IDE, enter: CdrService
  • program_type_rc. Enter the type of defined object created using this adapter: $PROGRAMTYPES$LOADSET, $PROGRAMTYPES$DATA_MART, $PROGRAMTYPES$PROGRAMor $PROGRAMTYPES$BUSAREA.
  • pre_install_function. Enter the name of your Pre-installation function, if any; see Pre_Install_Function.
  • install_function. Enter the name of your Installation function, if any; see Install_Function.
  • post_install_function. Enter the name of your Postinstallation function, if any; see Post_Install_Function.
  • pre_execution_function. Enter the name of your Pre-execution function, if any; see Pre_Execution_Function.
  • execution_function. Enter the name of your Execution function, if any; see Execution_Function.
  • post_execution_function. Enter the name of your Postexecution function, if any; see Post_Execution_Function.

    Note:

    If any of these functions returns an error the job returns an error. If any of these programs returns a warning then the job returns a warning unless another part of the job generated an error.

Parent topic: Creating a Technology Type

Sample Technology Type Settings

The following table shows the values used by a sampling of the adapters shipped with Oracle Life Sciences Data Hub.

You can see all settings for the shipped adapters in CDR_TECH_TYPES_V.

Table 4-1 Technology Types

Column Oracle Tables and Views Load Set Adapter Text Load Set Adapter SAS Load Set Adapter SAS Data Mart Adapter Generic Visualization Business Area Adapter

TECH_NAME_RC

$TECHTYPES$ORACLE

$TECHTYPES$TEXT

$TECHTYPES$SASLOADSET

$TECHTYPES$SASDATAMART

$TECHTYPES$GVA

TECH_VERSION

9.x

1

8.x

6.12

1.0.0.1

SERVICE_TYPE_RC

$SERVICETYPES$PLSQL

$SERVICETYPES$TEXT

$SERVICETYPES$SAS8

$SERVICETYPES$SAS8

Null

BUILD_IDE_CFG_FUNCTION

Null

Null

Null

Null

Null

BUILD_EXE_CFG_FUNCTION

Null

Null

Null

Null

Null

OWB_OPERATOR

CdrPLSQLImmediate_1

CdrSERVICE_1

CdrSERVICE_1

CdrSERVICE_1

CdrSERVICE_1

PROGRAM_TYPE_RC

$PROGRAMTYPES$LOADSET

$PROGRAMTYPES$LOADSET

$PROGRAMTYPES$LOADSET

$PROGRAMTYPES$DATA_MART

$PROGRAMTYPES$BUSAREA

PRE_INSTALL_FUNCTION

Null

Null

Null

Null

Null

INSTALL_FUNCTION

Null

Null

Null

Null

CDR_GV_ADAPTER.INSTALL

POST_INSTALL_FUNCTION

Null

Null

Null

Null

Null

PRE_EXECUTION_FUNCTION

Null

CDR_ATK_Text_Services.ExePreProcessor

CDR_EXE_SAS.buildSasConfigTmpLS

CDR_ATK_DM_SAS_SERVICES.executeDatamart

Null

EXECUTION_FUNCTION

CDR_ATK_oracledb.loadsetprocessing

Null

Null

Null

Null

POST_EXECUTION_FUNCTION

Null

Null

Null

Null

Null

Parent topic: Creating a Technology Type

Modifying a Technology Type

If you need to modify your Technology Type, use the public API CDR_PUB_ATK_ADAPTER. MODIFYTECHTYPE.

Its signature is:

Package cdr_pub_atk_adapter
PROCEDURE modifyTechType(p_api_version IN NUMBER,p_init_msg_list IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE,p_commit IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE,p_validation_level IN NUMBER default CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,x_return_status OUT NOCOPY VARCHAR2,x_msg_count OUT NOCOPY NUMBER,x_msg_data OUT NOCOPY VARCHAR2, pio_techTypeRow IN OUT NOCOPY cdr_tech_types%rowtype);

See the description of parameter pio_techTypeRow in Creating a Technology Type.

Parent topic: Using APIs to Create Required Metadata Objects

Creating an Adapter Domain

An Adapter Domain is a container object that holds all the definitional objects required for an adapter.

Note:

Creating an Adapter Domain and Adapter Area is required for Load Set, Data Mart, and Visualization adapters, but may not be required for IDE adapters if the IDE requires the user to log in.

See the following for details:

Parent topic: Using APIs to Create Required Metadata Objects

Call the Create Adapter Domain API

To create an Adapter Domain, call the public API CDR_PUB_ATK_ADAPTER.CREATEADAPTERDOMAIN. Its signature is:

PROCEDURE createAdapterDomain(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE
,p_commit IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE
,p_validation_level IN NUMBER default CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
, pio_adapterDomainNaming IN OUT NOCOPY cdr_naming_version_obj_type
) ;

It has one mandatory parameter, PIO_ADAPTERDOMAINNAMING, of table type CDR_NAMING_VERSION_OBJ_TYPE. Enter values as follows:

  • company_id = Enter your company ID; see Getting Your Company ID.
  • obj_id = null
  • obj_ver = null
  • object_type_rc = '$OBJTYPES$ADAPTERDOMAIN'
  • name = Enter a name for the Adapter Domain.
  • namespace_obj_id = null
  • namespace_obj_ver = null
  • namespace_start_obj_ver = null
  • namespace_end_obj_ver = cdr_def_constants.cdr_max_def_object_version
  • owning_location_rc = null
  • checked_out_flag_rc = null
  • checked_out_id = null
  • object_subtype_id = null
  • description = Enter a description of the Adapter Domain.
  • copied_from_company_id = null
  • copied_from_obj_id = null
  • copied_from_obj_ver = null
  • ref_company_id = null
  • ref_obj_ver = null
  • object_version_number = 1
  • status_rc = '$NAMING_STATUS$INSTALLABLE'
  • validation_status_rc = null
  • version_label = null

Parent topic: Creating an Adapter Domain

Save the Adapter Domain ID for Future Use

You will need the internal ID for this Adapter Domain when you create the Adapter Area.

Parent topic: Creating an Adapter Domain

Modifying an Adapter Domain

If you need to modify your Adapter Domain, use the public API CDR_PUB_ATK_ADAPTER. MODIFYADAPTERDOMAIN.

Its signature is:

Package cdr_pub_atk_adapter
 
PROCEDURE modifyAdapterDomain(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE
,p_commit IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE
,p_validation_level IN NUMBER default CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
, pio_adapterDomainNaming IN OUT NOCOPY cdr_naming_version_obj_type
) ;

See the description of parameter pio_adapterDomainNaming in Creating an Adapter Domain.

Parent topic: Using APIs to Create Required Metadata Objects

Creating an Adapter Area

An Adapter Area is a container object that can hold all the definitional objects required for an adapter.

It is similar in function to an Oracle Life Sciences Data Hub Application Area, which is described in the Oracle LSH Implementation Guide.

Normally you need only one Adapter Area in an Adapter Domain. However, if you are developing more than one adapter for a single external system, (like the multiple shipped Oracle Clinical adapters) you should create one Adapter Area for each adapter.

For details, see the following:

Parent topic: Using APIs to Create Required Metadata Objects

Call the Create Adapter Area API

You must create a new Adapter Area using the public API CDR_PUB_ATK_ADAPTER.CREATEADAPTERAREA.

Its signature is:

PROCEDURE createAdapterArea(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE
,p_commit IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE
,p_validation_level IN NUMBER default CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
, pio_adapterAreaNaming IN OUT NOCOPY cdr_naming_version_obj_type
, pio_adapterAreaRow IN OUT NOCOPY cdr_adapter_areas%rowtype
) ;

Enter parameter values as follows:

Note:

When you supply the name of a function or procedure you have written, do not include the schema name. Enter only package_name.procedure_or_function_name
  • PIO_ADAPTERAREANAMING. This is a mandatory parameter of table type CDR_NAMING_VERSION_OBJ_TYPE. Enter attribute values as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null

      Note:

      The system generates this ID. Save the output parameter value. You will need it to create objects inside the Adapter Area.
    • obj_ver = null
    • object_type_rc = '$OBJTYPES$ADAPTERAREA'
    • name = Enter a name for the Adapter Area. The name appears in the UI in the Adapter drop-down list on the object's Create page.
    • namespace_obj_id = Your_Adapter_Domain_ID
    • namespace_obj_ver = 1
    • namespace_start_obj_ver = 1
    • namespace_end_obj_ver= cdr_def_constants.cdr_max_def_object_version
    • owning_location_rc = null
    • checked_out_flag_rc = '$YESNO$NO'
    • checked_out_id = null
    • object_subtype_id = null
    • description = 'Enter_a_Description_for_the_Program'
    • copied_from_company_id = null
    • copied_from_obj_id = null
    • copied_from_obj_ver = null
    • ref_company_id = null
    • ref_obj_ver = null
    • object_version_number = 1
    • status_rc = null
    • validation_status_rc = null
    • version_label = null
  • PIO_ADAPTERAREAROW. This is a parameter of row type CDR_ADAPTER_AREAS table. Enter object attribute values as follows:

    Note:

    Adding a row to the Adapter Areas table is required for Load Set, Data Mart, and Visualization adapters, but not for IDE adapters.
    • company_id. Enter_your_company_ID
    • obj_id. Set to Null. The system enters the value.
    • obj_ver. Enter 1. Or, if you are updating the adapter, increment the version number by 1.
    • adapter_name. Enter a name for your adapter.
    • adapter_version. Enter the version number of the external system. This is for your information only; this field has no effect.

    • adapter_type. Enter one of the following adapter types:

      • LOADSET for Load Set adapters
      • DATAMART for Data Mart adapters
      • BUSAREA for Business Area adapters
      • PROGRAM for Program adapters
    • tech_type_id. Enter the tech type ID for your adapter.
    • allow_column_upload. (Applies only to Load Set adapters.) Enter YES, NO, or FILE. If set to NO, a user defining a Load Set using this adapter cannot upload column data structures from an external system. If you want the user to be able to upload these data structures, enter YES if the source system is a database and FILE if the source system stores data in files, such as SAS data sets or xml files.

      If set to YES or FILE, the Upload Column button appears in the Table Descriptor properties page. Set to NO if your adapter is not a Load Set-type adapter or if it is a Load Set adapter but the table structure is predefined, as in some of the Oracle Clinical adapters; see Planning Data Structures.

      The following shipped Load Set adapters use a column upload function: Oracle Clinical Data Extract Oracle Views, Oracle Clinical Data Extract SAS Views, Oracle Tables and Views, and SAS.

    • column_upload_function. Applies only to Load Set adapters. If the Allow_Column_Upload flag is set to YES or FILE, write a function to upload data structures and enter the schema_id.package_name of the function.
    • allow_manual_tab_desc_flag. Enter YES or NO. Set to YES to enable the Add button in the Table Descriptors subtab in user interface, so that users can manually define Table Descriptors. Set to NO to render the button inactive.
    • auto_add_tab_desc_lov. Applies only to Load Set adapters. See Object Definition Functions and Procedures for information.
    • allow_auto_add_tab_desc. Enter YES or NO. If set to NO, the Definer will not be able to choose from a list of Tables to create Table Descriptors. If set to YES, you must write a program to create a list of appropriate Tables and enter its name in Auto_Add_Tab_Desc_ Function. You must also create a program to create the selected Table Descriptors and enter its name in Auto_Add_Tab_Desc_LOV.
    • auto_add_tab_desc_function. Applies only to Load Set adapters. See Object Definition Functions and Procedures for information.
    • currency_function. See Object Definition Functions and Procedures for information.
    • define_time_function. See Object Definition Functions and Procedures for information.
    • define_time_connect_flag. Enter YES if, in order to define an object through this adapter, it is necessary to connect to a remote database. This is the case for Oracle-based Load Set adapters, for example, to get a list of tables on a remote database.

      If you enter YES, you must create a Parameter instance in the define-time Parameter Set to collect the remote location information; see Table Descriptor Define-Time Parameters. You must then use the Parameter value(s) in your code; for example, in the Auto_Add_Tab_Desc_Function to return the list of tables from the remote system for the user to select.

      Enter NO if no remote connection is required during object definition.

    • install_time_connect_flag. Enter YES if, in order to install an object created through this adapter, it is necessary to connect to a remote database. This is the case for Orace-based Load Set adapters, for example, if the Definer chooses to map Table Descriptors to Table instances defined as views. If set to YES, you must create a Parameter instance in the define-time Parameter Set to collect the remote location information; see Table Descriptor Define-Time Parameters.

      Enter NO if no remote connection is required during object installation.

    • runtime_connect_flag. Enter YES if, in order to run an object created through this adapter, it is necessary to connect to a remote database. This is the case for SAS and Text Load Set adapters that load a file. If set to YES, you must create a Parameter in the runtime Parameter Set to collect a value for the remote location; see Object Execution Functions and Procedures.

      Enter NO if no remote connection is required to run the object.

    • tables_as_views_flag. (Applies only to Load Set adapters.) Enter YES to allow the Definer to create the Load Set's target Table instances as passthrough views to tables in the external system, so that the user can view data in the external system. Entering YES here adds the item "Create Table as a View" from the Process Type drop-down in the properties page of Table instances mapped to target Table Descriptors.

      Enter NO if the external system cannot support this functionality or if you do not want to use it.

      The following shipped adapters have this flag set to YES: Oracle Clinical Labs, Oracle Clinical Data Extract Oracle Views, and Oracle Tables and Views.

    • tables_as_views_function. Set to Null. This function is not currently used, even by Load Sets that support tables as views.
    • active_flag_rc. Enter '$YESNO$YES'

      Note:

      You can always set this flag to YES. The adapter will not actually become available for use until you have assigned user groups to the Adapter Area.

    • def_param_flag_rc. Enter '$YESNO$YES' if this adapter has a Parameter Set called PARAMETERSET_LOADSETLEVEL_DEF. See Object Definition Functions and Procedures for further information. The system then displays the Parameters in this Parameter Set as define-time attributes of Load Sets, Data Marts, Program, or Business Areas created with this adapter.

      Enter '$YESNO$NO' if this adapter does not have a Parameter Set called PARAMETERSET_LOADSETLEVEL_DEF.

    • run_param_flag_rc. Enter '$YESNO$YES' if this adapter has a Parameter Set called PARAMETERSET_LOADSETLEVEL_RUN. See Object Execution Functions and Procedures for further information. The system then displays the Parameters in this Parameter Set as runtime Parameters in the Parameters subtab of Load Sets, Data Marts, Program, or Business Areas and in the Execution Setup of Load Sets or Data Marts created with this adapter.

      Enter '$YESNO$NO' if this adapter does not have a Parameter Set called PARAMETERSET_LOADSETLEVEL_RUN.

    • td_param_flag_rc. (Applies only to Load Set adapters.) Enter '$YESNO$YES' if this adapter has a Parameter Set called PARAMETERSET_OPERATORLEVEL. See Table Descriptor Define-Time Parameters for further information. The system then displays the Parameters in this Parameter Set as define-time attributes of Load Sets created with this adapter.

      Enter '$YESNO$NO' if this adapter does not have a Parameter Set called PARAMETERSET_OPERATORLEVEL

    • status_recalc_function. See Object Definition Functions and Procedures for information.
    • security_recalc_flag_rc. Enter '$YESNO$YES' if you write a Security Recalulation function to synchronize Oracle Life Sciences Data Hub security with the security of the external system; for example, for a Business Area adapter.

      Enter '$YESNO$NO' if there is no security synchronization between Oracle LSH and the external system. See Synchronizing Security with Integrated Environments.

    • security_recalc_function. See Object Execution Functions and Procedures for information.
    • install_schema_flag_rc. If your adapter requires a dedicated Oracle database schema, enter '$YESNO$YES'. If not, enter '$YESNO$NO'.

      If you set this flag to '$YESNO$YES', Oracle LSH creates an additional schema dedicated to this adapter when you install the Work Area containing the Business Area or other adapter-related object, and gives the additional schema the same name as the Work Area schema plus the suffix you specify for install_schema_suffix. You can use the install_functions to populate this schema with whatever objects your adapter requires. Oracle LSH reinstalls the schema each time the Work Area is installed, using the same installation type specified for the Work Area. No more than one additional schema per adapter type is created per Work Area.

    • install_schema_suffix. If you set the install_schema_flag_rc to '$YESNO$YES', enter text. The system appends this text to the Work Area schema name to create the name for the additional schema.

Parent topic: Creating an Adapter Area

Save the Adapter Area ID for Future Use

The input/output Parameter PIO_SOURCECDRNAMING returns the Object ID (obj_id) of the Adapter Area.

You will need this ID each time you create an object in the Adapter Area.

Parent topic: Creating an Adapter Area

Sample Adapter Settings

The following table shows the Adapter Area settings used by some of the adapters shipped with Oracle Life Sciences Data Hub.

You can see all settings for the shipped adapters in CDR_ADAPTER_AREAS_V.

Note:

The system treats No and Null the same way.

Table 4-2 Sample Adapter Settings

Column Name Oracle Tables and Views Load Set Adapter Text Load Set Adapter SAS Load Set Adapter SAS Data Mart Adapter Generic Visualization Business Area Adapter

OBJ_VER

1

1

1

1

1

ADAPTER_NAME

ORACLE_DATABASE

Text

SAS

SAS EXPORT

GENERIC_VISUALIZATION

ADAPTER_VERSION

1

1

8.2

1

1.0.1.2

ADAPTER_TYPE

LOADSET

LOADSET

LOADSET

DATAMART

BUSAREA

TECHNOLOGY_TYPE_ID

(Generated)

(Generated)

(Generated)

(Generated)

(Generated)

ALLOW_COLUMN_UPLOAD

YES

NO

File

NO

NO

COLUMN_UPLOAD_FUNCTION

CDR_ATK_OracleDB_Services.UploadColumns

Null

CDR_ATK_SAS_Services.UploadOperatorColumns

NO

Null

ALLOW_MANUAL_TAB_DESC_FLAG

YES

YES

YES

YES

NO

AUTO_ADD_TAB_DESC_LOV

CDR_ATK_OracleDB_Services.CreateMultipleOperators

NO

NO

NO

NO

ALLOW_AUTO_ADD_TAB_DESC

YES

NO

NO

YES

NO

AUTO_ADD_TAB_DESC_FUNCTION

CDR_ATK_OracleDB_Services.GetDataOperList

Null

Null

NO

Null

CURRENCY_FUNCTION

Null

Null

Null

NO

Null

DEFINE_TIME_FUNCTION

CDR_ATK_OracleDB_Services.CreateLogFilePlannedOutput

CDR_ATK_Text_Services.CreateLogFilePlannedOutput

CDR_ATK_SAS_Services.CreateLogFilePlannedOutput

CDR_ATK_DM_SAS_SERVICES.createPlannedOutput

Null

DEFINE_TIME_CONNECT_FLAG

YES

NO

NO

NO

NO

INSTALL_TIME_CONNECT_FLAG

YES

NO

NO

NO

NO

RUNTIME_CONNECT_FLAG

YES

NO

NO

NO

NO

TABLES_AS_VIEWS_FLAG

YES

NO

NO

NO

NO

TABLES_AS_VIEWS_FUNCTION

Null

Null

Null

NO

Null

ACTIVE_FLAG_RC

$YESNO$YES

$YESNO$YES

$YESNO$YES

$YESNO$YES

$YESNO$YES

DEF_PARAM_FLAG_RC

$YESNO$YES

$YESNO$YES

$YESNO$YES

$YESNO$YES

$YESNO$YES

RUN_PARAM_FLAG_RC

$YESNO$YES

$YESNO$YES

$YESNO$YES

$YESNO$YES

$YESNO$NO

TD_PARAM_FLAG_RC

$YESNO$YES

$YESNO$NO

$YESNO$NO

$YESNO$NO

$YESNO$NO

STATUS_RECALC_FUNCTION

Null

Null

Null

CDR_ATK_DM_SAS_SERVICES.synchronizeDatamart

Null

SECURITY_RECALC_FLAG_RC

$YESNO$NO

$YESNO$NO

$YESNO$NO

$YESNO$NO

$YESNO$NO

SECURITY_RECALC_FUNC

Null

Null

Null

Null

Null

INSTALL_SCHEMA_FLAG_RC

$YESNO$YES

$YESNO$NO

$YESNO$NO

$YESNO$NO

$YESNO$NO

INSTALL_SCHEMA_SUFFIX

Null

Null

Null

Null

Null

Parent topic: Creating an Adapter Area

Modifying an Adapter Area

If you need to modify your Adapter Area, use public API CDR_PUB_ATK_ADAPTER.MODIFYADAPTERAREA.

Its signature is:

Package cdr_pub_atk_adapter
PROCEDURE modifyAdapterArea(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE
,p_commit IN VARCHAR2 default CDR_PUB_DEF_CONSTANTS.G_FALSE
,p_validation_level IN NUMBER default CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
, pio_adapterAreaNaming IN OUT NOCOPY cdr_naming_version_obj_type
, pio_adapterAreaRow IN OUT NOCOPY cdr_adapter_areas%rowtype
) ;

See the description of the parameters in Creating an Adapter Area.

Parent topic: Using APIs to Create Required Metadata Objects

Assigning a User Group to the Adapter Area

In order to create the rest of the required objects, you must belong to a user group that is assigned to the Adapter Area, and you must have a role within the user group that allows you to create and modify each of the required objects.

Note:

Only the people who will build the adapter should have these privileges within a user group assigned to the Adapter Area. If the same people will later define Load Sets, Data Marts, Program, or Business Areas that use this adapter, you may want to remove them from this user group or ask them to always log in as a different user in a different user group so that they do not inadvertently modify a Parameter definition and invalidate the adapter.

See Assigning User Groups to the Adapter Area.

You can assign user groups to Adapters in the user interface or by calling an API.

Note:

If you are creating an adapter to be used by other companies, use the API method to assign a user group. In the installation script for your adapter, create an input parameter to accept one or more user group IDs in the customer company.

For more details, see the following:

Parent topic: Using APIs to Create Required Metadata Objects

User Interface Method

User interface instructions are included in the chapter on setting up adapters in the Oracle LSH System Administrator's Guide.

Parent topic: Assigning a User Group to the Adapter Area

API Method

Call the API CDR_PUB_SECURITY_PKG.ASSIGNUSRGRPTOOBJ. Its signature is:

PROCEDURE ASSIGNUSRGRPTOOBJ( 
  P_API_VERSION  IN    NUMBER, 
  P_INIT_MSG_LIST  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL  IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
  X_RETURN_STATUS  OUT    VARCHAR2, 
  X_MSG_COUNT  OUT    NUMBER, 
  X_MSG_DATA  OUT    VARCHAR2, 
  PI_BASEOBJECTTYPE  IN OUT    CDR_BASE_OBJ_TYPE, 
  PI_CDROBJUGCOLL  IN    CDR_OBJ_UG_COLL 
);

Enter values for the parameter PI_BASEOBJECTTYPE as follows:

  • company_id. Set to null.
  • obj_id. Enter the obj_id of the Adapter Area.
  • obj_ver. Enter 1
  • object_version_number. Set to null.
  • namespace_obj_id. Enter the obj_id of the Adapter Domain
  • namespace_object_ver. Enter 1

Enter values for the parameter PI_CDROBJUGCOLL as follows:

  • ug_company_id. Set to null.
  • obj_company_id. Set to null.
  • user_group_id. Enter the user_group_id of the user group.
  • obj_id. Enter the obj_id of the Adapter Area.
  • exclusion_flag. Set to 'N'.
  • object_version_number. Set to null.

Parent topic: Assigning a User Group to the Adapter Area

Creating a Work Area

A Work Area contains instances of all the object definitions required for the adapter.

Work Areas are described in the Oracle LSH Implementation Guide.

Note:

Save the Obj_ID of the Work Area for use when you create objects inside it.

For more information, see the following:

Parent topic: Using APIs to Create Required Metadata Objects

Call the Create Work Area API

To create a Work Area, call the API CDR_PUB_DF_WORKAREA.CREATEWORKAREA.

Its signature is:

PROCEDURE CREATEWORKAREA( 
     P_API_VERSION             IN    NUMBER, 
     P_INIT_MSG_LIST           IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
     P_COMMIT                  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
     P_VALIDATION_LEVEL        IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
     X_RETURN_STATUS           OUT    VARCHAR2, 
     X_MSG_COUNT               OUT    NUMBER, 
     X_MSG_DATA                OUT    VARCHAR2, 
     PIO_SOURCECDRNAMING       IN OUT    CDR_NAMING_VERSION_OBJ_TYPE, 
     PIO_WORKAREAOBJTYPE       IN OUT    CDR_WORKAREA_OBJ_TYPE, 
     PI_DEFCLASSIFICATIONCOLL  IN    CDR_CLASSIFICATIONS_COLL 
);

Enter Parameter values as follows:

  • PIO_SOURCECDRNAMING. Enter CDR_NAMING_VERSION_OBJ_TYPE values for the Work Area, as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = null
    • object_type_rc = '$OBJTYPES$WORKAREA'
    • name = 'Enter_a_name_for_the_Work_Area'
    • namespace_obj_id = Your_Adapter_Area's_obj_id
    • namespace_obj_ver = 1
    • namespace_start_obj_ver = 1
    • namespace_end_obj_ver= cdr_def_constants.cdr_max_def_object_version
    • owning_location_rc = null
    • checked_out_flag_rc = '$YESNO$NO'
    • checked_out_id = null
    • object_subtype_id = null
    • description = 'Enter_a_Description_for_the_Work_Area'
    • copied_from_company_id = null
    • copied_from_obj_id = null
    • copied_from_obj_ver = null
    • ref_company_id = null
    • ref_obj_ver = null
    • object_version_number = 1
    • status_rc = '$NAMING_STATUS$INSTALLABLE'
    • validation_status_rc = null
    • version_label = null

  • PI_WORKAREAOBJTYPE. Enter CDR_WORKAREA_OBJ_TYPE values as follows:

    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = 1
    • label = 'Standard'
    • workarea_status_rc =null
    • last_status_change_ts = sysdate
    • usage_intent_rc = '$SYSVALDNSTEPS$PRODUCTION'

      Note:

      If you set the Usage Intent to Production, you can still modify your source code and defined objects as necessary and reinstall the Work Area. Then when you have finished developing the adapter, if you want to upgrade all objects' validation status to Production, you can do so. If you prefer, set Usage Intent to Development now and use the API CDR_PUB_DF_WORKAREA.UPDATEUSAGEINTENT to change it to Production later.

      An Adapter Area can contain only one Work Area.

    • cloned_from_company_id = null
    • cloned_from_obj_id = null
    • cloned_from_obj_ver = null
    • wa_runtime_status_rc = null

Parent topic: Creating a Work Area

Save the Work Area ID for Future Use

The input/output Parameter PIO_SOURCECDRNAMING returns the Object ID (obj_id) of the Work Area.

Parent topic: Creating a Work Area

Creating a Program Definition and Instance

You must create a Program definition in the Adapter Area to store the source code required for the adapter. You must create an instance of the Program definition in the Work Area.

For details, see the following:

Parent topic: Using APIs to Create Required Metadata Objects

Query for the Tech Type ID

Before you call the API, run the following query to retrieve the local tech type ID for the PL/SQL technology type:

select tech_type_id from cdr_tech_types where tech_name_rc='$TECHTYPES$PLSQL';

Note:

Save the tech type ID for use in creating Source Code definitions as well as the Program definition.

Parent topic: Creating a Program Definition and Instance

Call the Create Program API

To create a Program definition and an instance of it, call the API CDR_PUB_DF_PROGRAM.CREATEPROGRAM.

Its signature is:

PROCEDURE CREATEPROGRAM( 
     P_API_VERSION              IN    NUMBER, 
     P_INIT_MSG_LIST            IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE,
     P_COMMIT                   IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE,
     P_VALIDATION_LEVEL         IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
     X_RETURN_STATUS            OUT    VARCHAR2, 
     X_MSG_COUNT                OUT    NUMBER, 
     X_MSG_DATA                 OUT    VARCHAR2, 
     PIO_SOURCECDRNAMING        IN OUT CDR_NAMING_VERSION_OBJ_TYPE, 
     PI_CDRPRGOBJTYPE           IN    CDR_PROGRAM_OBJ_TYPE, 
     PI_CREATEOBJECT            IN    VARCHAR2, 
     PI_INSTANCE_SUBTYPE_ID     IN    CDR_NAMINGS.OBJECT_SUBTYPE_ID%TYPE, 
     PI_DEFCLASSIFICATIONCOLL   IN    CDR_CLASSIFICATIONS_COLL, 
     PI_INSTCLASSIFICATIONCOLL  IN    CDR_CLASSIFICATIONS_COLL 
);

Enter Parameter values as follows:

  • PIO_SOURCECDRNAMING. Enter CDR_NAMING_VERSION_OBJ_TYPE values that apply to the Program definition, as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = null
    • object_type_rc = null
    • name = 'Enter_a_name_for_the_Program'
    • namespace_obj_id = Your_WorkArea's_obj_ID
    • namespace_obj_ver = 1
    • namespace_start_obj_ver = 1
    • namespace_end_obj_ver= cdr_def_constants.cdr_max_def_object_version
    • owning_location_rc = null
    • checked_out_flag_rc = '$YESNO$NO'
    • checked_out_id = null
    • object_subtype_id = null
    • description = 'Enter_a_Description_for_the_Program'
    • copied_from_company_id = null
    • copied_from_obj_id = null
    • copied_from_obj_ver = null
    • ref_company_id = null
    • ref_obj_ver = null
    • object_version_number = 1
    • status_rc = '$NAMING_STATUS$INSTALLABLE'
    • validation_status_rc = null
    • version_label = null
  • PI_CDRPRGOBJTYPE. Enter CDR_PROGRAM_OBJ_TYPE values that apply to the Program definition, as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = 1
    • tech_type_id = Enter_your_Tech_Type_ID
    • manual_validation_flag_rc = '$YESNO$NO'
  • PI_CREATEOBJECT = BOTH
  • PI_DEFCLASSIFICATIONCOLL = null
  • PI_INSTCLASSIFICATIONCOLL = null

Parent topic: Creating a Program Definition and Instance

Save the Program Definition and Instance IDs for Future Use

The input/output Parameter PIO_SOURCECDRNAMING returns the Object ID (obj_id) of the Program instance.

To get the Object ID of the Program definition, use the following query:

select ref_obj_id,ref_obj_ver from cdr_naming_versions where company_id =  your_company_idand obj_id = your_program_instance_obj_id and obj_ver=1;

Parent topic: Creating a Program Definition and Instance

Creating a Source Code Definition and Instance

You must create at least one Source Code object definition inside the Program definition to contain the PL/SQL packages you write.

Each Source Code definition must contain one and only one package. Each package can contain any number of functions and procedures, in any combination you choose.

It is not necessary to mark any Source Code as Primary because the Program is never executed as a whole.

See the following topic for additional information:

Parent topic: Using APIs to Create Required Metadata Objects

Call the Create Source Code API

To create a Source Code definition and an instance of it, call the API CDR_PUB_DF_SOURCEC)DE.CREATESOURCECODE.

Its signature is:

PROCEDURE CREATESOURCECODE( 
     P_API_VERSION            IN    NUMBER, 
     P_INIT_MSG_LIST          IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
     P_COMMIT                 IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
     P_VALIDATION_LEVEL       IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
     X_RETURN_STATUS          OUT    VARCHAR2, 
     X_MSG_COUNT              OUT    NUMBER, 
     X_MSG_DATA               OUT    VARCHAR2, 
     PIO_SCREF_SOURCECDRNAMING IN OUT    CDR_NAMING_VERSION_OBJ_TYPE, 
     PI_CDRSCOBJTYPE           IN OUT    CDR_SRCCODE_OBJ_TYPE, 
     PIO_CDRSCREFOBJTYPE       IN OUT    CDR_SRCCODE_REF_OBJ_TYPE, 
     PI_CREATEOBJECT           IN    VARCHAR2, 
     PI_DEFINITON_SUBTYPE_ID   IN    CDR_NAMINGS.OBJECT_SUBTYPE_ID%TYPE, 
     PI_VLOBMODE               IN    VARCHAR2 := NULL, 
     PIO_CDRSCBLOB             IN OUT    CDR_SRCCODE_BLOB_OBJ_TYPE, 
     PIO_CDRSCCLOB             IN OUT    CDR_SRCCODE_CLOB_OBJ_TYPE 
);

Enter Parameter values as follows:

  • PIO_SCREF_SOURCECDRNAMING. Enter CDR_NAMING_VERSION_OBJ_TYPE values that apply to the Source Code definition, as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = null
    • object_type_rc = '$OBJTYPES$SRCCDEREF'
    • name = 'Enter_a_name_for_the_Source_Code'
    • namespace_obj_id = Enter_your_Program_Definition's_obj_id
    • namespace_obj_ver = 1
    • namespace_start_obj_ver = 1
    • namespace_end_obj_ver= cdr_def_constants.cdr_max_def_object_version
    • owning_location_rc = null
    • checked_out_flag_rc = '$YESNO$NO'
    • checked_out_id = null
    • object_subtype_id = null
    • description = 'Enter_a_Description_for_the_Source_Code'
    • copied_from_company_id = null
    • copied_from_obj_id = null
    • copied_from_obj_ver = null
    • ref_company_id = null
    • ref_obj_ver = null
    • object_version_number = 1
    • status_rc = '$NAMING_STATUS$INSTALLABLE'
    • validation_status_rc = null
    • version_label = null
  • PI_CDRSCOBJTYPE. Enter CDR_SRCCODE_OBJ_TYPE values that apply to the Source Code definition, as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = 1
    • tech_type_id = Enter_your_Tech_Type_ID
    • srccode_type_rc = '$FILETYPES$SQL'
    • shareable_flag_rc = '$YESNO$NO'
    • oracle_package_name = 'Enter_the_package_name'
    • oracle_procedure_name = 'Enter_the_name_of_the_procedure_inside_the_package'
  • PIO_CDRSCREFOBJTYPE. Enter CDR_SRCCODE_REF_OBJ_TYPE values that apply to the Source Code instance, as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = 1
    • position = 1
    • primary_flag_rc = '$YESNO$NO'

      Note:

      Only one Source Code in any Program can have its Primary flag set to Yes. In the case of an adapter Program, there is no true primary Source Code because the Program as a whole is never executed. The adapter calls the functions one at a time as needed. So you can set this flag to No for all Source Code definitions in the adapter.
    • static_flag_rc = '$YESNO$NO'
    • static_program_company_id = null
    • static_program_obj_id = null

      static_program_obj_ver_id = null

    • fileref = null
  • PI_CREATEOBJECT. Enter "BOTH".
  • PI_DEFINITON_SUBTYPE_ID. Null
  • PI_VLOBMODE. Enter 'DIRECT'.
  • PIO_CDRSCBLOB. Null. You are uploading a PL/SQL package, which is a CLOB.
  • PIO_CDRSCCLOB. This is a compound object of type CDR_SRCCODE_CLOB_OBJ_TYPE. Enter values as follows for one PL/SQL package containing the custom functions and procedures you have written for the adapter.
    • file_name = name_of_the_source_code_file
    • file_clob = source_code_text
    • sc_obj_id = null
    • sc_obj_ver = null

Parent topic: Creating a Source Code Definition and Instance

Creating a Variable

If you need user input to create a Load Set, Data Mart, Program, or Business Area—for example, an Oracle Remote Location name and connection—you must define a Parameter to collect the information. For each Parameter, you must define a Variable for the Parameter to reference.

For details, see the following:

Parent topic: Using APIs to Create Required Metadata Objects

Call the Create Variable API

To create a Variable, call the API CDR_PUB_DF_VARIABLE.CREATEVARIABLE.

Its signature is:

PROCEDURE CREATEVARIABLE( 
  P_API_VERSION             IN    NUMBER, 
  P_INIT_MSG_LIST           IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT                  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL        IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,
  X_RETURN_STATUS           OUT    VARCHAR2, 
  X_MSG_COUNT               OUT    NUMBER, 
  X_MSG_DATA                OUT    VARCHAR2, 
  PIO_NAMING                IN OUT    CDR_NAMING_VERSION_OBJ_TYPE, 
  PIO_VARIABLE              IN OUT    CDR_VAR_OBJ_TYPE, 
  PI_DEFCLASSIFICATIONCOLL  IN    CDR_CLASSIFICATIONS_COLL 
);

Enter Parameter values as follows:

  • PIO_NAMING. Enter CDR_NAMING_VERSION_OBJ_TYPE values as follows:

    • company_id = Enter_your_company_ID

    • obj_id = null

    • obj_ver = null

    • object_type_rc = '$OBJTYPES$CDRVAR'

    • name = 'Enter_a_name_for_the_Variable'

      Note:

      For the Name and Description attributes, enter a meaningful value appropriate for each Variable. This is important because you will probably create many Variables and you will need to reference them from Parameter definitions and, if you define Tables in your adapter, Table Columns.
    • namespace_obj_id = Your_Adapter_Area's_obj_id
    • namespace_obj_ver = 1
    • namespace_start_obj_ver = 1
    • namespace_end_obj_ver= cdr_def_constants.cdr_max_def_object_version
    • owning_location_rc = null
    • checked_out_flag_rc = '$YESNO$NO'
    • checked_out_id = null
    • object_subtype_id = null
    • description = 'Enter_a_Description_for_the_Source_Code'
    • copied_from_company_id = null
    • copied_from_obj_id = null
    • copied_from_obj_ver = null
    • ref_company_id = null
    • ref_obj_ver = null
    • object_version_number = 1
    • status_rc = '$NAMING_STATUS$INSTALLABLE'
    • validation_status_rc = null
    • version_label = null
  • PIO_VARIABLE. Enter CDR_VAR_OBJ_TYPE values that apply to the Source Code definition, as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = 1
    • oracle_name = 'Enter_an_Oracle_Name'
    • oracle_datatype_rc = 'Enter_one_of_the_valid_values'

      Note:

      The valid Oracle_Datatype_RC values are:
      • $ORADATATYPES$VARCHAR2
      • $ORADATATYPES$NUMBER
      • $ORADATATYPES$DATE
    • length = Enter_the_Variable_length
    • precision = null (unless the variable is of data type number and requires a value for precision)
    • sas_format = If the data type is varchar2, enter '$Char.length'If the data type is number, enter '$Num.length'

      If you are creating an adapter to a SAS system, use the following default SAS formatting rules:Varchar2(10) becomes $10; Number(10,5) becomes 10.5;Date becomes datetime.

    • sas_v6_name = 'Enter_SAS_v6_name'

      Note:

      The SAS_V6_Name cannot be longer than 8 characters.

    • sas_v8_name = 'Enter_SAS_v8_name'
    • sas_label = 'Enter_Sas_Label'
    • nullable_flag = Enter '$YESNO$YES' or '$YESNO$NO' depending on whether or not you want the variable to be nullable or mandatory.
    • default_value = null

Parent topic: Creating a Variable

Save the Variable ID for Future Use

The input/output Parameter PIO_NAMING returns the Object ID (obj_id) and version number (obj_ver) of the Variable.

You will need this ID when you define a Parameter based on this Variable. Be careful to save a meaningful name with the ID, as you may have many Variables.

Note:

If you are creating Tables in your adapter, you can create Variables and Table Columns at the same time. You do not need to create Variables first.

Parent topic: Creating a Variable

Check in the Variable

You must check in the Variable so that other objects can reference it.

Call API CDR_PUB_DF_VARIABLE.CHECKIN. Enter values as follows:

  • PIO_BASEOBJECT. Enter CDR_BASE_OBJ_TYPE values to identify the variable.

    • company_id = Enter_your_company_ID

    • obj_id = Enter_the_variable's_obj_id
    • obj_ver = 1
    • object_version_number = 1
    • namespace_obj_id = Enter_your_Adapter_Area's_obj_id
    • namespace_obj_ver = 1
  • PI_COMMENT = null

Parent topic: Creating a Variable

Creating a Parameter

Use Parameters to allow users to enter values during the definition or execution of a Load Set, Data Mart, Program, or Business Area. You must create a defined Parameter object for each parameter required and handle the user input in your source code.

Parameter definitions are usually contained directly in the Adapter Area. Alternatively, if you are creating multiple adapters for a single external system and more than one of them use the same Parameter definition, you may want to create the Parameter definition directly in the Adapter Domain.

Note:

If you are creating an adapter that must upload files, define a Parameter with its Parameter Type set to either BINARY_FILE or TEXT_FILE, as appropriate, and create an instance of it in the runtime Parameter Set (see Creating a Parameter Set.) Create a second Parameter called TMP_BLOB_ID, for example, and create an instance of it in the same Parameter Set. Write code to upload the file to a temporary location and store the ID for the file as the valueof TMP_BLOB_ID. Use this value in your pre-execution function and execution function.

For details, see the following:

Parent topic: Using APIs to Create Required Metadata Objects

Call the Create Parameter API

To create a Parameter, call the API CDR_PUB_DF_PARAMETER.CREATEPARAMETER.

Its signature is:

PROCEDURE CREATEPARAMETER( 
     P_API_VERSION             IN    NUMBER, 
     P_INIT_MSG_LIST           IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
     P_COMMIT                  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
     P_VALIDATION_LEVEL        IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
     X_RETURN_STATUS           OUT    VARCHAR2, 
     X_MSG_COUNT               OUT    NUMBER, 
     X_MSG_DATA                OUT    VARCHAR2, 
     PIO_PARAMNAMING           IN OUT    CDR_NAMING_VERSION_OBJ_TYPE, 
     PIO_CDRPARAMOBJTYPE       IN OUT    CDR_PARAMETER_OBJ_TYPE, 
     PI_CREATE_OBJECT          IN    VARCHAR2, 
     PI_INSTANCE_SUBTYPE_ID    IN    CDR_NAMINGS.OBJECT_SUBTYPE_ID%TYPE, 
     PI_PARENTNAMING           IN OUT    CDR_BASE_OBJ_TYPE, 
     PO_DEFCLASSIFICATIONCOLL  IN    CDR_CLASSIFICATIONS_COLL 
);

Enter parameter values as follows:

  • PIO_PARAMNAMING. Enter CDR_NAMING_VERSION_OBJ_TYPE values as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = null
    • object_type_rc = '$OBJTYPES$PARAMETER'
    • name = 'Enter_a_name_for_the_Parameter'

      Note:

      For the Name and Description attributes, enter a meaningful value appropriate for each Parameter. This is important because you will probably create many Parameters and you will need to create instances of them.
    • namespace_obj_id = Enter_your_Adapter_Area's_obj_id
    • namespace_obj_ver = 1
    • namespace_start_obj_ver = 1
    • namespace_end_obj_ver= cdr_def_constants.cdr_max_def_object_version
    • owning_location_rc = null
    • checked_out_flag_rc = '$YESNO$NO'
    • checked_out_id = null
    • object_subtype_id = null
    • description = 'Enter_a_Description_for_the_Parameter'
    • copied_from_company_id = null
    • copied_from_obj_id = null
    • copied_from_obj_ver = null
    • ref_company_id = Enter_your_company_ID
    • ref_obj_id = Enter_the_obj_ID_of_the_Variable_this_Parameter_references
    • ref_obj_ver = 1
    • object_version_number = 1
    • status_rc = '$NAMING_STATUS$INSTALLABLE'
    • validation_status_rc = null
    • version_label = null
  • PIO_CDRPARAMOBJTYPE. Enter CDR_PARAMETER_OBJ_TYPE values that apply to the Parameter definition, as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = 1
    • prompt = 'Enter_the_label_you_want_to_appear_in_the_UI'
    • allowed_values_rc (Required) This attribute determines what type of value the Parameter will support. For further information, see the chapter on Parameters in the Oracle LSH Application Developer's Guide. The allowed attribute values are:
      • $PARAMALLOWVALS$PGMGENLOV (Program-generated list of values)
      • $PARAMALLOWVALS$STATICLOV (Static list of values)
      • $PARAMALLOWVALS$SINGLEVALUE ( Single value)
    • lov_company_id = null
    • lov_id = null
    • lov_ver = null
    • lov_prg_inst_company_id = null or, if you are defining a programmatically generated list of values, enter the company_id of the Program instance that you need to run to generate the LOV.
    • lov_prg_inst_id = null or, if you are defining a programmatically generated list of values, enter the obj_id of the Program instance that you need to run to generate the LOV.
    • lov_prg_inst_ver = null or, if you are defining a programmatically generated list of values, enter the obj_ver of the Program instance that you need to run to generate the LOV.
    • lov_sc_ref_company_id = null or, if you are defining a programmatically generated list of values, enter the company_id of the relevant Source Code instance in the Program instance.
    • lov_sc_ref_id = null or, if you are defining a programmatically generated list of values, enter the obj_id of the relevant Source Code instance in the Program instance.
    • lov_sc_ref_ver = null or, if you are defining a programmatically generated list of values, enter the obj_ver of the relevant Source Code instance in the Program instance.
    • lov_cla_level_id = null or, if you are defining a list of values based on terms in a classification hierarchy level, enter the level_id of the of the relevant level.
    • lov_default_cla_id = null
    • lov_multi_flag_rc Enter $YESNO$YES if the Parameter supports either a static list of values or a program-generated list of values and you want to support selecting more than one value at a time in the user interface.

      Enter $YESNO$NO if the Parameter supports only a single value.

    • validation_rule_rc Enter one of the following values:
      • $VALDNRULES$NONE (no validation rule defined)
      • $VALDNRULES$USEALLOWEDVALS (the parameter's value will be validated against the list of values defined for the parameter, either Programatic or Static)
      • $VALDNRULES$PROGRAMMATIC (the parameter's value will be validated against a list of values generated by source code different from the source code that generates the Parameter's list of values—if any)
    • val_prg_inst_company_id = null or, if you are validating user-entered values programmatically, enter the company_id of the Program instance that you run to perform the validation.
    • val_prg_inst_id = null or, if you are validating user-entered values programmatically, enter the obj_id of the Program instance that you run to perform the validation.
    • val_prg_inst_ver = null or, if you are validating user-entered values programmatically, enter the obj_ver of the Program instance that you run to perform the validation.
    • val_sc_ref_company_id = null or, if you are validating user-entered values programmatically, enter the company_id of the relevant Source Code instance in the Program instance.
    • val_sc_ref_id = null or, if you are validating user-entered values programmatically, enter the obj_id of the relevant Source Code instance in the Program instance.
    • val_sc_ref_ver = null or, if you are validating user-entered values programmatically, enter the obj_ver of the relevant Source Code instance in the Program instance.
    • input_output_rc = '$PARAMDIRECTS$INOUT'
    • read_only_flag_rc = '$YESNO$NO'
    • visible_flag_rc = '$YESNO$YES'
    • mandatory_flag_rc = '$YESNO$YES'
    • default_value = null (or enter a default value if you want one)
    • position = null
    • param_type_rc = null

      Note:

      By default the system sets the Parameter type to Scalar.
    • auto_share_field_flag_rc = null
  • PI_CREATEOBJECT Enter DEFN.
  • PI_INSTANCE_SUBTYPE_ID = null
  • PI_PARENTNAMING Enter CDR_BASE_OBJ_TYPE values that apply to your Adapter Area as follows:
    • company_id = Enter_the_company_id_of_your_Adapter_Area
    • obj_id = Enter_the_obj_id_of_your_Adapter_Area
    • obj_ver = 1
  • PO_DEFCLASSIFICATIONCOLL Null

Parent topic: Creating a Parameter

Save the Parameter ID for Future Use

The input/output Parameter PIO_PARAMNAMING returns the Object ID (obj_id) and version number (obj_ver) of the Parameter.

You will need this ID when you define a Parameter instance based on this Parameter. Be careful to save a meaningful name with the ID, as you may have many Parameters.

Parent topic: Creating a Parameter

Check in the Parameter

You must check in the Parameter so that other objects can reference it.

Call API CDR_PUB_DF_PARAMETER.CHECKINPARAMETER. Its signature is:

PROCEDURE CHECKINPARAMETER( 
  P_API_VERSION       IN    NUMBER, 
  P_INIT_MSG_LIST     IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT            IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL  IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
  X_RETURN_STATUS     OUT    VARCHAR2, 
  X_MSG_COUNT         OUT    NUMBER, 
  X_MSG_DATA          OUT    VARCHAR2, 
  PIO_CDRNAMING       IN OUT    CDR_BASE_OBJ_TYPE, 
  PI_COMMENT          IN    VARCHAR2 
);

Enter values as follows:

  • PIO_BASEOBJECT. Enter CDR_BASE_OBJ_TYPE values to identify the variable.
    • company_id = Enter_your_company_ID
    • obj_id = Enter_the_Parameter's_obj_id
    • obj_ver = 1
    • object_version_number = 1
    • namespace_obj_id = Enter_your_Adapter_Area's_obj_id
    • namespace_obj_ver = 1
  • PI_COMMENT = null

Parent topic: Creating a Parameter

Creating a Parameter Set

You may create one, two, or three Parameter Sets to collect user input during the definition, installation, or execution of a Load Set, Data Mart, or Program, or the launch of a visualization tool.

You must give these Parameter Sets specific names and attribute values so that the system can use them properly; see Planning Parameters and Parameter Sets.

For more details, see the following:

Parent topic: Using APIs to Create Required Metadata Objects

Call the Create Parameter Set API

To create a Parameter, call the API CDR_PUB_DF_PARAMETER_SET.CREATEPARAMETERSET.

Its signature is:

PROCEDURE CREATEPARAMETERSET( 
  P_API_VERSION        IN    NUMBER, 
  P_INIT_MSG_LIST      IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT             IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL   IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,
  X_RETURN_STATUS      OUT   VARCHAR2, 
  X_MSG_COUNT          OUT   NUMBER, 
  X_MSG_DATA           OUT   VARCHAR2, 
  PIO_SOURCECDRNAMING  IN OUT    CDR_NAMING_VERSION_OBJ_TYPE, 
  PI_CDRPSOBJTYPE      IN    CDR_PARAM_SETS_OBJ_TYPE, 
  PI_CREATEOBJECT      IN    VARCHAR2, 
  PI_INSTANCE_SUBTYPE_ID    IN   CDR_NAMINGS.OBJECT_SUBTYPE_ID%TYPE, 
  PI_DEFCLASSIFICATIONCOLL  IN   CDR_CLASSIFICATIONS_COLL 
);

Enter parameter values as follows:

  • PIO_PARAMNAMING. Enter CDR_NAMING_VERSION_OBJ_TYPE values as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = null
    • object_type_rc = null
    • name = 'Enter_one_of_the_required_names_for_the_Parameter_Set'

      Note:

      The valid values are:
      • PARAMETERSET_LOADSETLEVEL_DEF
      • PARAMETERSET_OPERATORLEVEL
      • PARAMETERSET_LOADSETLEVEL_RUN
    • namespace_obj_id = Enter_your_Adapter_Area's_obj_id
    • namespace_obj_ver = 1
    • namespace_start_obj_ver = 1
    • namespace_end_obj_ver= cdr_def_constants.cdr_max_def_object_version
    • owning_location_rc = null
    • checked_out_flag_rc = '$YESNO$NO'
    • checked_out_id = null
    • object_subtype_id = null
    • description = 'Enter_the_same_value_that_you_entered_for_the_Parameter_Set_name'
    • copied_from_company_id = null
    • copied_from_obj_id = null
    • copied_from_obj_ver = null
    • ref_company_id = Enter_your_company_ID
    • ref_obj_id = Enter_the_obj_ID_of_the_Variable_this_Parameter_references
    • ref_obj_ver = 1
    • object_version_number = 1
    • status_rc = '$NAMING_STATUS$INSTALLABLE'
    • validation_status_rc = null
    • version_label = null
  • PI_CDRPSOBJTYPE. Enter CDR_PARAM_SETS_OBJ_TYPE values that apply to the Parameter Set definition, as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = 1
    • usage = 'Enter_the_usage_value_required_for_Parameter_Sets_with_this_name'

      Note:

      The valid values are:
      • DEFINITION for Parameter Sets named PARAMETERSET_LOADSETLEVEL_DEF
      • OPERATOR for Parameter Sets named PARAMETERSET_OPERATORLEVEL
      • EXECUTION for Parameter Sets named PARAMETERSET_LOADSETLEVEL_RUN
    • pr_ref_id = null
    • pr_ref_ver = null
    • parameter_set_type_rc = null

Parent topic: Creating a Parameter Set

Save the Parameter Set ID for Future Use

You will need this ID for the namespace_obj_id when you create Parameter instances inside this Parameter Set.

Oracle Life Sciences Data Hub creates standard IDs for Adapter Parameter Sets:

  • PS1ID for Parameter Sets named PARAMETERSET_LOADSETLEVEL_DEF
  • PS2ID for Parameter Sets named PARAMETERSET_OPERATORLEVEL
  • PS3ID for Parameter Sets named PARAMETERSET_LOADSETLEVEL_RUN

Parent topic: Creating a Parameter Set

Creating a Parameter Instance in a Parameter Set

For each Parameter definition you have created, create a Parameter instance in the appropriate Parameter Set.

For more details, see the following:

Parent topic: Using APIs to Create Required Metadata Objects

Call the Create Parameter API

To create a Parameter, call the API CDR_PUB_DF_PARAMETER.CREATEPARAMETER.

Enter parameter values as follows:

PROCEDURE CREATEPARAMETER( 
  P_API_VERSION        IN    NUMBER, 
  P_INIT_MSG_LIST      IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT             IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL   IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,
  X_RETURN_STATUS      OUT   VARCHAR2, 
  X_MSG_COUNT          OUT   NUMBER, 
  X_MSG_DATA           OUT   VARCHAR2, 
  PIO_PARAMNAMING      IN OUT         CDR_NAMING_VERSION_OBJ_TYPE, 
  PIO_CDRPARAMOBJTYPE  IN OUT         CDR_PARAMETER_OBJ_TYPE, 
  PI_CREATE_OBJECT     IN    VARCHAR2, 
  PI_INSTANCE_SUBTYPE_ID    IN        CDR_NAMINGS.OBJECT_SUBTYPE_ID%TYPE, 
  PI_PARENTNAMING           IN OUT    CDR_BASE_OBJ_TYPE, 
  PO_DEFCLASSIFICATIONCOLL  IN        CDR_CLASSIFICATIONS_COLL 
);
  • PIO_PARAMNAMING. Enter CDR_NAMING_VERSION_OBJ_TYPE values as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = null
    • object_type_rc = '$OBJTYPES$PARAMREF'
    • name = 'Enter_a_name_for_the_Parameter_instance'
    • namespace_obj_id = Enter_the_Parameter_Set_ID
    • namespace_obj_ver = 1
    • namespace_start_obj_ver = 1
    • namespace_end_obj_ver= cdr_def_constants.cdr_max_def_object_version
    • owning_location_rc = null
    • checked_out_flag_rc = '$YESNO$NO'
    • checked_out_id = null
    • object_subtype_id = null
    • description = 'Enter_a_Description_for_the_Parameter_instance'
    • copied_from_company_id = null
    • copied_from_obj_id = null
    • copied_from_obj_ver = null
    • ref_company_id = Enter_your_company_ID
    • ref_obj_id = Enter_the_obj_ID_of_the_Parameter_definition_this_Parameter_instance_references
    • ref_obj_ver = 1
    • object_version_number = 1
    • status_rc = '$NAMING_STATUS$INSTALLABLE'
    • validation_status_rc = null
    • version_label = null
  • PIO_CDRPARAMOBJTYPE. Do not enter any values.
  • PI_CREATEOBJECT. Enter INST.
  • PI_INSTANCE_SUBTYPE_ID. Null
  • PI_PARENTNAMING. Enter the following CDR_BASE_OBJ_TYPE values. The first four apply to the Parameter Set into which you are putting the Parameter instance. The last two (namespace) attributes apply to the Adapter Area, which is the parent of the Parameter Set.
    • company_id = Enter your company ID; see Getting Your Company ID.
    • obj_id = Enter the Object ID of the Parameter Set.
    • obj_ver = 1
    • object_version_number = 1
    • namespace_obj_id = Enter the Object ID of the Adapter Area.
    • namespace_obj_ver = 1
  • PO_DEFCLASSIFICATIONCOLL. Null

Parent topic: Creating a Parameter Instance in a Parameter Set

Creating a Table Definition

If you are creating a Load Set adapter and the external source data system has fixed data structures, you may want to define those data structures as Tables in the Adapter Area rather than uploading them or forcing the user to create them manually each time a user creates a Load Set.

This enhances performance, reduces the possibility of error, and eliminates the need to connect to a remote database during Load Set definition.

You can either create a Table Descriptor for every Table definition in every Load Set of this type, or you can create a list of values and allow the person defining the Load Set to select which Table Descriptors he or she wants.

To create a list of values:

  • Write a procedure to retrieve a list of all the Table definitions in the Adapter Area and to insert them into a list values so that the user can select some or all of them to be loaded by the Load Set.
  • When you create the Adapter Area, enter the procedure's name as the value for auto_add_tab_desc_function.

    Note:

    The above description is correct. The intended functions for the above column and the Auto_Add_Tab_Desc_LOV column are reversed.
  • When you create the Adapter Area, set allow_auto_add_tab_desc to YES.

In addition, you must:

  • Write a procedure to create Table Descriptors in Oracle Life Sciences Data Hub based on each Table definition required.
  • When you create the Adapter Area, enter this procedure's name as the value for auto_add_tab_desc_lov.

Note:

Data Mart and Business Area adapters do not require Table definitions because their source tables are within Oracle LSH.

For more details, see the following:

Parent topic: Using APIs to Create Required Metadata Objects

Call the Create Table API

To create a Table definition, call the API CDR_PUB_DF_TABLE.CREATETABLEDEFINITION.

Enter parameter values as follows:

PROCEDURE CREATETABLEDEFINITION( 
  P_API_VERSION        IN    NUMBER, 
  P_INIT_MSG_LIST      IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT             IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL   IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,
  X_RETURN_STATUS      OUT   VARCHAR2, 
  X_MSG_COUNT          OUT   NUMBER, 
  X_MSG_DATA           OUT   VARCHAR2, 
  PIO_NAMING           IN OUT      CDR_NAMING_VERSION_OBJ_TYPE, 
  PIO_TABLE            IN OUT      CDR_TABLE_OBJ_TYPE, 
  PI_INSTANCESUBTYPEID       IN    NUMBER, 
  PI_DEFCLASSIFICATIONCOLL   IN    CDR_CLASSIFICATIONS_COLL, 
  PI_INSTCLASSIFICATIONCOLL  IN    CDR_CLASSIFICATIONS_COLL 
);
  • PIO_NAMING. Enter CDR_NAMING_VERSION_OBJ_TYPE values that apply to the Program definition, as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = null
    • object_type_rc = '$OBJTYPES$TABLE'
    • name = 'Enter_a_name_for_the_Table'
    • namespace_obj_id = Enter_your_Adapter_Area's_obj_id
    • namespace_obj_ver = 1
    • namespace_start_obj_ver = 1
    • namespace_end_obj_ver= cdr_def_constants.cdr_max_def_object_version
    • owning_location_rc = null
    • checked_out_flag_rc = '$YESNO$NO'
    • checked_out_id = null
    • object_subtype_id = null
    • description = 'Enter_a_Description_for_the_Table'
    • copied_from_company_id = null
    • copied_from_obj_id = null
    • copied_from_obj_ver = null
    • ref_company_id = null
    • ref_obj_ver = null
    • object_version_number = 1
    • status_rc = '$NAMING_STATUS$INSTALLABLE'
    • validation_status_rc = null
    • version_label = null
  • PI_TABLE. Enter CDR_TABLE_OBJ_TYPE values as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = 1
    • oracle_name = 'Enter_an_Oracle_name_for_the_Table'
    • sas_name = 'Enter_a_SAS_name_for_the_Table'
    • sas_label = 'Enter_a_SAS_label_for_the_Table'
    • sas_v6_flag = '$YESNO$YES' if you are using SAS v6 or '$YESNO$NO' if you are using a more recent SAS version
    • audit_tabc_company_id = null
    • audit_tabc_obj_id = null
    • audit_tabc_obj_ver = null
    • snapshot_flag_rc = 'set_to_$YESNO$YES_to_allow_snapshots or $YESNO$NO_prevent_them'
    • process_type_rc = Valid values are: $PROCESSTYPES$RELOAD (Reload), $PROCESSTYPES$STAGINGWAUDIT (Staging with Audit), $PROCESSTYPES$STAGINGWOAUDIT (Staging without Audit), $PROCESSTYPES$TRANSWOAUDIT (Transactional without Audit), $PROCESSTYPES$TRANSWAUDIT (Transactional with Audit)
    • blinding_flag_rc = Enter_$YESNO$YES_if_the_adapter_will_load_blinded_data_or_$YESNO$NO_if_it_will_not
  • PI_INSTANCESUBTYPEID. Null
  • PI_DEFCLASSIFICATIONCOLL. Null
  • PI_INSTCLASSIFICATIONCOLL. Null

Parent topic: Creating a Table Definition

Call the Create Column API

Call the API CDR_PUB_DF_TABLE.CREATECOLUMN to create Columns for the Table, one at a time.

You can create both the Column and the Variable on which it is based at the same time.

  • PIO_NAMING. Enter CDR_NAMING_VERSION_OBJ_TYPE values as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = null
    • object_type_rc = '$OBJTYPES$COLUMN'
    • name = 'Enter_a_name_for_the_Column'
    • namespace_obj_id = Enter_the_Table's_obj_ID
    • namespace_obj_ver = 1
    • namespace_start_obj_ver = 1
    • namespace_end_obj_ver= cdr_def_constants.cdr_max_def_object_version
    • owning_location_rc = null
    • checked_out_flag_rc = '$YESNO$NO'
    • checked_out_id = null
    • object_subtype_id = null
    • description = 'Enter_a_Description_for_the_Column'
    • copied_from_company_id = null
    • copied_from_obj_id = null
    • copied_from_obj_ver = null
    • ref_company_id = null
    • ref_obj_ver = null
    • object_version_number = 1
    • status_rc = '$NAMING_STATUS$INSTALLABLE'
    • validation_status_rc = null
    • version_label = null
  • PIO_VARIABLE. Enter CDR_VAR_OBJ_TYPE values to define the Variable as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = 1
    • oracle_name = 'Enter_an_Oracle_name_for_the_Table'
    • oracle_datatype = 'Enter one: $ORADATATYPES$VARCHAR2, $ORADATATYPES$NUMBER, $ORADATATYPES$DATE'
    • length = 'Enter_a_length_for_the_Table'
    • precision = 'If_your_Variable's_datatype_is_NUMBER,_enter_its_precision'
    • sas_v6_name = 'Enter_your_variable's_SASv6_name_(up_to_8_chars)'
    • sas_v8_name = 'Enter_your_variable's_SASv8_name_(up_to_32_chars)'
    • sas_label = 'Enter_your_variable's_SAS_label_(up_to_256_chars)'
    • sas_format = 'Enter_your_variable's_SAS_format'
    • nullable_flag = 'Enter_$YESNO$YES_if_the_value_can_be_ null_or_$YESNO$NO_if_not'
    • default value = Set_to_Null_or_enter_a_default_value_for_the_variable
  • PIO_COLUMN. Enter CDR_COLUMNS_OBJ_TYPE values to define the Variable as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = 1
    • position_number = 'Enter_your_column's_position_number_in_the_Table'
    • nullable_flag = 'Enter_$YESNO$YES_if_the_value_can_be_ null_or_$YESNO$NO_if_not'
  • PI_CREATETYPE Enter BOTH to create a Variable and a Column at the same time.
  • PI_DEFCLASSIFICATIONCOLL Null

Parent topic: Creating a Table Definition

Installing the Work Area

After you have created the Adapter Domain and all the definitional objects within it, install the Work Area by calling the API CDR_PUB_DF_WORKAREA.INSTALLWACONTROLLER and the Program instance it contains.

This API also checks in the Program instance and its definition.

Note:

If the Program is checked out, which it normally is at this point, and the person running this API is different from the person who created the Program, Work Area installation fails. The person who created(or most recently checked out) the Program definition must either explicitly check in the Program or run the Work Area installation. See Checking In Objects and Setting Their Validation Status for further information.

To create a Work Area, call the API CDR_PUB_DF_WORKAREA.CREATEWORKAREA. Its signature is:

PROCEDURE INSTALLWACONTROLLER( 
  P_API_VERSION       IN     NUMBER, 
  P_INIT_MSG_LIST     IN     VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT            IN     VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL  IN     NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,
  X_RETURN_STATUS     OUT    VARCHAR2, 
  X_MSG_COUNT         OUT    NUMBER, 
  X_MSG_DATA          OUT    VARCHAR2, 
  PIO_OWABASENAMING   IN OUT    CDR_BASE_OBJ_TYPE, 
  PI_VINSTALLMODE     IN    CDR_INSTALLATIONS.INSTALLATION_MODE_RC%TYPE, 
  PI_VFORCEREGEN      IN    CDR_INSTALLATIONS.FORCE_REGEN_FLAG_RC%TYPE, 
  PI_VBATCH           IN    CDR_INSTALLATIONS.BATCH_FLAG_RC%TYPE, 
  PI_VACTION          IN    CDR_INST_ELEMENTS.INSTALL_ACTION_RC%TYPE, 
  PI_COINSTDETAILS    IN    CDR_INSTALLATION_DETAILS_COLL 
);

Enter values as follows:

  • PIO_OWABASENAMING. Enter CDR_BASE_OBJ_TYPE values as follows:
    • company_id = Enter_your_company_ID
    • obj_id = Enter_the_Work_Area's_obj_ID
    • obj_ver = Enter_the_Work_Area's_obj_ver
    • object_version_number = Get_this_value_from_cdr_df_naming_v
    • namespace_obj_id = Enter_your_Adapter_Area's_object_ID
    • namespace_obj_ver = Enter_your_Adapter_Area's_obj_ver
  • PI_VINSTALLMODE. Enter '$INSTALLMODE$UPGRADE'
  • PI_VFORCEREGEN. Enter '$YESNO$YES'
  • PI_VBATCH. Enter '$YESNO$NO'
  • PI_VACTION. Enter one of the following values:
    • '$INSTALLCMD$COMPLETE' Enter this value if this is the first time the Work Area is being installed, or if the last installation was successful, or if the last installation failed and you want to continue from the last successfully completed phase.
    • '$INSTALLCMD$CANCEL' Enter this value if the last installation failed and you want to begin the installation process from the beginning.

      Note:

      You must then run the API again with PI_VACTION set to '$INSTALLCMD$COMPLETE'.
  • PI_COINSTDETAILS. This is a collection of CDR_INST_DET_OBJ_TYPEs. For each object in the Work Area that you want to install, initialize a CDR_INST_DET_OBJ_TYPE and then extend the collection. Normally an adapter has only a single Program instance in the Work Area. Enter values to identify it as follows:
    • company_id = Enter_your_company_ID
    • obj_id = null
    • obj_ver = 1
    • object_type_rc = For Programs, enter '$OBJTYPES$PROGRAMINST'.
    • omit_from_install_flag_rc = '$YESNO$NO'
    • install_action_rc = '$INSTOBJACT$REPLACE'

Parent topic: Using APIs to Create Required Metadata Objects