Oracle® Life Sciences Data Hub Adapter Toolkit Guide Release 2.3 Part Number E35307-01 |
|
|
View PDF |
This section contains the following topics:
Oracle Life Sciences Data Hub (Oracle LSH) 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, "Adapter Components and Their Relationships" shows the defined 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. Call these APIs in the order they are given in this section. Additional information on APIs is available in the Oracle Life Sciences Data Hub Application Programming Interface Guide.
Many APIs require IDs as input parameter values. This section explains how to retrieve these IDs from the database.
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.
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.This section contains the following topics:
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 LSH.
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 onlypackage_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.
program_type_rc. Enter the type of defined object created using this adapter: $PROGRAMTYPES$LOADSET
, $PROGRAMTYPES$DATA_MART
, $PROGRAMTYPES$PROGRAM
or $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.The following table shows the values used by a sampling of the adapters shipped with Oracle LSH. You can see all settings for the shipped adapters in CDR_TECH_TYPES_V.
Table 4-1 Sample Technology Type Settings
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 |
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".
Do the following:
An Adapter Domain is a container object that holds all the definitional objects required for an adapter.
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
You will need the internal ID for this Adapter Domain when you create the Adapter Area.
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".
Do the following:
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 LSH 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.
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 onlypackage_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:
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 toYES
. 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 LSH 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.
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.
The following table shows the Adapter Area settings used by some of the adapters shipped with Oracle LSH. 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 Area 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 |
|
|
File |
|
|
COLUMN_UPLOAD_FUNCTION |
CDR_ATK_OracleDB_Services.UploadColumns |
Null |
CDR_ATK_SAS_Services.UploadOperatorColumns |
|
Null |
ALLOW_MANUAL_TAB_DESC_FLAG |
|
|
|
|
|
AUTO_ADD_TAB_DESC_LOV |
CDR_ATK_OracleDB_Services.CreateMultipleOperators |
|
|
|
|
ALLOW_AUTO_ADD_TAB_DESC |
|
|
|
|
|
AUTO_ADD_TAB_DESC_FUNCTION |
CDR_ATK_OracleDB_Services.GetDataOperList |
Null |
Null |
|
Null |
CURRENCY_FUNCTION |
Null |
Null |
Null |
|
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 |
|
|
|
|
|
INSTALL_TIME_CONNECT_FLAG |
|
|
|
|
|
RUNTIME_CONNECT_FLAG |
|
|
|
|
|
TABLES_AS_VIEWS_FLAG |
|
|
|
|
|
TABLES_AS_VIEWS_FUNCTION |
Null |
Null |
Null |
|
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 |
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".
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.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.User interface instructions are included in the chapter on setting up adapters in the Oracle LSH System Administrator's Guide.
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.
This section contains the following topics:
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.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
The input/output Parameter PIO_SOURCECDRNAMING returns the Object ID (obj_id
) of the Work Area.
This section contains the following topics:
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.
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.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 = nulll
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;
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.
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
This section contains the following topics:
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.
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
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.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
This section contains the following topics:
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.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
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.
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
This section contains the following topics:
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".
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
You will need this ID for the namespace_obj_id when you create Parameter instances inside this Parameter Set. Oracle LSH 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
For each Parameter definition you have created, create a Parameter instance in the appropriate Parameter Set.
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
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 LSH 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.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
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
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 Chapter 6, "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'