25 Job Execution
This section contains the following topics:
- Create and Execute Output LOBs
- Retrieve Information about Ongoing Jobs
- Set Execution Statuses
- Submit Messages
- Create Submission Records
Parent topic: Common APIs
Create and Execute Output LOBs
This is a public API for uploading output CLOBs into Oracle LSH. These output CLOBs are generated by Oracle LSH executables outside the Oracle LSH database. For example, a PDF file output from a SAS print program or a SAS CPORT file output from a Data Mart. It also includes APIs that adapters may need to call during the execution of a Load Set, Data Mart, or Program.
This section contains the following topics:
- Create a Binary Output
- Upload an Output BLOB
- Upload an Output CLOB
- Upload a LOB to a Temporary Table
- Download a Job Output BLOB
- Queue a Job
- Wait for a Job to Complete
- Generate an XML Payload
Parent topic: Job Execution
Create a Binary Output
Use this API to create a binary output object.
Name
CDR_PUB_EXE_EXTERNAL.CreateBinaryOutput
Signature
PROCEDURE CREATEBINARYOUTPUT( 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_NCOMPANYID IN NUMBER, PI_VFILENAME IN VARCHAR2, PI_VDATA IN RAW := NULL );
Parameters
This API has standard parameters (see "Standard Parameters") and the following parameters:
-
PI_NCOMPANYID (Mandatory) Enter Company Id.
-
PI_VFILENAME (Mandatory) Enter the File Name.
-
PI_VDATA (Optional) Enter the RAW input to stream up the output.
Parent topic: Create and Execute Output LOBs
Upload an Output BLOB
Use this API to upload an output BLOB generated by an external processing engine into Oracle LSH.
Name
CDR_PUB_EXE_EXTERNAL.UploadBlobOutput
Signature
PROCEDURE UPLOADBLOBOUTPUT( 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_NJOBID IN VARCHAR2, PI_VFILENAME IN VARCHAR2, PI_NPRREFID IN VARCHAR2, PIO_BLOBSTREAM IN OUT BLOB );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PI_NJOBID (Mandatory) Enter the JOB_ID of the job that generated the output.
- PI_VFILENAME (Mandatory) Enter the filename associated with the Planned Output.
- PI_NPRREFID (Mandatory) Enter the PRREF_ID of the Oracle LSH executable. This attribute is available from the cdr_jobs_v view.
- PIO_BLOBSTREAM (Mandatory) This is a parameter of type BLOB. Enter BLOB to be uploaded.
Parent topic: Create and Execute Output LOBs
Upload an Output CLOB
Use this API to upload an output CLOB into Oracle LSH.
Name
CDR_PUB_EXE_EXTERNAL.UploadClobOutput
Signature
PROCEDURE UPLOADCLOBOUTPUT( 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_NJOBID IN VARCHAR2, PI_VFILENAME IN VARCHAR2, PI_NPRREFID IN VARCHAR2, PIO_CLOBSTREAM IN OUT CLOB );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PI_NJOBID (Mandatory) Enter the JOB_ID of the job that generated the output.
- PI_VFILENAME (Mandatory) Enter the filename associated with the Planned Output.
- PI_NPRREFID (Mandatory) Enter the PRREFID of the Oracle LSH executable. This attribute is available from the CDR_JOBS table.
- PIO_CLOBSTREAM (Mandatory) Enter the variable name for the CLOB in the database.
Parent topic: Create and Execute Output LOBs
Upload a LOB to a Temporary Table
Use this API to upload one or more BLOBs or CLOBs (binary or character large objects) to a temporary table.
Name
CDR_PUB_EXE_EXTERNAL.CreateTempLobs
Signature
PROCEDURE CREATETEMPLOBS( 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_NJOBID IN NUMBER, PI_BZIP IN BOOLEAN, PI_BLOBNAMES IN CDR_VC_LIST_COLL, PI_BLOBENTRIES IN CDR_BLOB_LIST_COLL, PI_CLOBNAMES IN CDR_VC_LIST_COLL, PI_CLOBENTRIES IN CDR_CLOB_LIST_COLL );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PI_NJOBID (Mandatory) Enter the Job_ID. You can get the Job ID by running CDR_PUB_EXE_RUNTIME.GETCURRENTLYEXECUTINGJOBID.
- PI_BZIP Is the input lobs in zip format? Enter TRUE or FALSE.
- PI_BLOBNAMES This parameter is of type cdr_vc_list_coll which is a collection of varchar2(2000). The varchar contains the BLOB file name.
- PI_BLOBENTRIES This parameter is a collection of cdr_blob_list_coll which is a collection of BLOB types. This parameter contains the actual file BLOBs. This is always a collection so even if you are uploading a single CLOB, initialize the collection with that BLOB and pass it here.
- PI_CLOBNAMES This parameter is of type cdr_vc_list_coll which is a collection of varchar2(2000). The varchar contains the CLOB file name.
- PI_CLOBENTRIES This parameter is a collection of cdr_clob_list_coll which is a collection of CLOB type. This contains the actual file CLOBs. This is always a collection so even if you are uploading a single CLOB, initialize the collection with that CLOB and pass it here.
Parent topic: Create and Execute Output LOBs
Download a Job Output BLOB
Use this API to download a job output BLOB.
Name
CDR_PUB_EXE_EXTERNAL.DownloadTempBlob
Signature
FUNCTION DOWNLOADTEMPBLOB( 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, PI_NOBJID IN NUMBER, PO_VFILENAME OUT VARCHAR2 ) RETURN BLOB;
Return
Type BLOB
Description returns the output file as a BLOB.
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PI_NOBJID (Mandatory) Enter the tmp_blob_id of the job output LOB. You can
use the following query to get this
ID:
SELECT tmp_blob_id FROM cdr_temp_blobs_v WHERE job_id = pi_nJobId;
- PO_VFILENAME (Mandatory) This Out parameter contains the file name of the downloaded LOB file.
Parent topic: Create and Execute Output LOBs
Queue a Job
Use this API to queue the job into a service location for execution.
Name
CDR_PUB_EXE_EXTERNAL.SendJob
Signature
PROCEDURE SENDJOB( 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_VRECEIVER IN VARCHAR2, PI_NJOBID IN NUMBER, PI_VPAYLOAD IN VARCHAR2 );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PI_VRECEIVER (Mandatory) Enter the name of the Service Location.
- PI_NJOBID (Mandatory) Enter the Job_ID. You can get the Job ID by running CDR_PUB_EXE_RUNTIME.GETCURRENTLYEXECUTINGJOBID.
- PI_VPAYLOAD (Mandatory)Enter the actual XML Payload generated by the CDR_PUB_EXE_EXTERNAL.GenerateXMLPayload API.
Parent topic: Create and Execute Output LOBs
Wait for a Job to Complete
Use this API to enable synchronous execution so that program control waits for a Job to complete before proceeding with the rest of the logic.
Name
CDR_PUB_EXE_EXTERNAL.WaitForFinalStatus
Signature
PROCEDURE WAITFORFINALSTATUS( 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_NJOBID IN NUMBER, PI_NTIMEOUT IN NUMBER, PI_NSERVICEINSTANCEID IN NUMBER );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PI_NJOBID (Mandatory) Enter the Job ID of the Job in process. The Job_ID is returned by the API CDR_PUB_EXE_SUBMISSION.CREATE SUBMISSION and in the UI.
- PI_NTIMEOUT (Mandatory) Enter in seconds, the job completion period before the API times out.
- PI_NSERVICEINSTANCEID (Mandatory) Enter the service instance that is processing the job. Get the ID from the cdr_jobs_v view using the Job_Id.
Parent topic: Create and Execute Output LOBs
Generate an XML Payload
Use this API to generate the required XML payload for a job execution.
Name
CDR_PUB_EXE_EXTERNAL.GenerateXmlPayload
Signature
FUNCTION GENERATEXMLPAYLOAD( 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, PI_NJOBID IN NUMBER, PI_VJOBTYPE IN VARCHAR2, PI_NDOWNLOADCONFIGID IN NUMBER, PI_VPROGRAM IN VARCHAR2, PI_VWORKDIRECTORY IN VARCHAR2, PI_VRUNSCRIPT IN VARCHAR2, PI_VOUTPUTPATH IN VARCHAR2, PI_VPRIORITY IN VARCHAR2, PI_VSCHEMA IN VARCHAR2, PI_VUSERID IN VARCHAR2, PI_VSUBDIRECTORIES IN CDR_VC_LIST_COLL, PI_NSURROGATEJOBID IN CDR_JOBS_V.JOB_ID%TYPE := NULL, PI_VTECHTYPE IN VARCHAR2 := NULL, PI_NSURROGATEPRREFID IN CDR_JOBS_V.PRREF_ID%TYPE := NULL ) RETURN CLOB;
Return
Type CLOB Description Returns the generated XML as a CLOB.
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PI_NJOBID (Mandatory) Enter the Job_ID.
- PI_VJOBTYPE (Mandatory) Enter the Job Type—either tmp for an internal job or exe for a user-executed job
- PI_NDOWNLOADCONFIGID (Optional) If the entire job configuration is stored in
cdr_temp_blobs (against the Job_ID), you can enter the ID of the BLOB for the job.
Query CDR_TEMP_BLOBS_V to get the value. If you do not enter a BLOB ID, enter 0.
- PI_VPROGRAM (Mandatory) Enter the command to be executed on the server.
- PI_VWORKDIRECTORY (Mandatory) Enter the full path of the root folder in the DP server Home under which the job folder gets created.
- PI_VRUNSCRIPT (Mandatory) Enter the name of the main script to be executed for the job.
- PI_VOUTPUTPATH (Mandatory) Enter the path where the output will be generated, relative to the work directory/ folder.
- PI_VPRIORITY (Mandatory) Enter the job priority. The possible values are the lookup codes in the lookup type 'CDR_JOB_PRIORITIES'.
- PI_VSCHEMA (Mandatory) Enter the ID of the ZZ_Schema allocated for the current job. You can query CDR_JOBS_V to get this ID.
- PI_VUSERID (Mandatory) Enter the UserId that is executing the job. Use the API CDR_PUB_DEF_FACTORY_SUPPORT.GETUSERID to get this ID.
- PI_VSUBDIRECTORIES This is a collection of type CDR_VC_LIST_COLL. Enter the names of subdirectories that should be created under the Job directory in the DP Server.
- PI_NSURROGATEJOBID SurrogateJobId. (Optional) Enter the job ID under which the created outputs will be uploaded if it is different from the current job; for example, a master job.
- PI_VTECHTYPE (Mandatory) Enter the TechType name being used for the job.
- PI_NSURROGATEPRREFID SurrogatePrrefId(Optional) Enter the ID of the master job for this job, if any.
Parent topic: Create and Execute Output LOBs
Retrieve Information about Ongoing Jobs
This is a public interface to retrieve information about an ongoing job.
This section contains the following topics:
- Get an Ongoing Job ID
- Get Currently Executing Parameters
- Get Information About a Job
- Get Additional Job Information (Overloaded)
Parent topic: Job Execution
Get an Ongoing Job ID
Use this API to retrieve the job ID of the job that is currently running.
Name
CDR_PUB_EXE_RUNTIME.GetCurrentlyExecutingJobID
Signature
FUNCTION GETCURRENTLYEXECUTINGJOBID( 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 ) RETURN CDR_JOBS.JOB_ID%TYPE;
Return
Type CDR_JOBS.JOB_ID%TYPE
Description the job ID of the job which is executing in the current session.
Parameters
This API has standard parameters. See Standard Parameters) for details.
Parent topic: Retrieve Information about Ongoing Jobs
Get Currently Executing Parameters
Use this API to retrieve the parameters in use by the job that is currently running.
Name
CDR_PUB_EXE_RUNTIME.GetCurrentlyExecutingParams
Signature
FUNCTION GETCURRENTLYEXECUTINGPARAMS( 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 ) RETURN CDR_PARAMETER_VALUES_COLL;
Return
Type CDR_PARAMETER_VALUES_COLL
Parameters
This API has standard parameters. See Standard Parameters) for details.
Parent topic: Retrieve Information about Ongoing Jobs
Get Information About a Job
Use this API to retrieve information about a Job by passing its JOB_ID.
Name
CDR_PUB_EXE_RUNTIME.GetJobInfo
Signature
PROCEDURE GETJOBINFO( 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_NJOBID IN CDR_JOBS.JOB_ID%TYPE, PO_RSUBMISSION_V OUT CDR_SUBMISSIONS_V%ROWTYPE, PO_RJOB_V OUT CDR_JOBS_V%ROWTYPE );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PI_NJOBID (Mandatory) Enter the JOB_ID of the job you want information about. You can get the Job ID by running CDR_PUB_EXE_RUNTIME. Get Currently Executing Job ID.
- PO_RSUBMISSION_V This is an output parameter. The API returns the row corresponding to the SUBMISSION_ID associated with the JOB_ID value in CDR_JOBS from the CDR_SUBMISSIONS table.
- PO_RJOB_V This is an output parameter. The API returns the row corresponding to the JOB_ID from the CDR_JOBS table.
Parent topic: Retrieve Information about Ongoing Jobs
Get Additional Job Information (Overloaded)
Use this API to retrieve information about a Job, including whether it is a Master job or a Top Level job, by passing its JOB_ID.
Name
CDR_PUB_EXE_RUNTIME.GetJobInfo
Signature
PROCEDURE GETJOBINFO( 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_NJOBID IN CDR_JOBS.JOB_ID%TYPE, PO_RSUBMISSION OUT CDR_SUBMISSIONS%ROWTYPE, PO_RJOB OUT CDR_JOBS%ROWTYPE, PO_BISTOPLEVELJOB OUT BOOLEAN, PO_BISMASTERJOB OUT BOOLEAN );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PI_NJOBID (Mandatory) Enter the JOB ID.
- PO_RSUBMISSION This is an output parameter. The API returns the row corresponding to the SUBMISSION_ID (associated with the JOB_ID value in CDR_JOBS) from the CDR_SUBMISSIONS table.
- PO_RJOB This is an output parameter. The API returns the row corresponding to the JOB_ID from the CDR_JOBS table.
- PO_BISTOPLEVELJOB This is an output parameter. The API returns "T" if the Job is a Top Level Job.
- PO_BISMASTERJOB This is an output parameter. The API returns "T" if the job is a Master job.
Parent topic: Retrieve Information about Ongoing Jobs
Set Execution Statuses
This package contains APIs for setting and retrieving the execution status of Oracle LSH Programs. It also contains APIs for setting output parameters from external tools.
This section contains the following topics:
- Set a User-specific Completion Status
- Set a Customized Output Title
- Set a Customized Output Subtitle
- Set an Output Parameter
- Get a Completion Status
Parent topic: Job Execution
Set a User-specific Completion Status
Use this API to set the completion status to a user specified value. Valid status values are: 1 for OK; 2 for OK With Warnings; 3 for Failure.
Name
CDR_PUB_EXE_USER_UTILS.SetCompletionStatus
Signature
PROCEDURE SETCOMPLETIONSTATUS( 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_NCOMPLETIONSTATUS IN NUMBER := 1 );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameter:
PI_NCOMPLETIONSTATUS (Mandatory) Enter the completion status value. Valid values are 1, 2, or 3.
Parent topic: Set Execution Statuses
Set a Customized Output Title
Use this API to set a title for individual RSE outputs.
Name
CDR_PUB_EXE_USER_UTILS.SetCustomOutputTitle
Signature
PROCEDURE SETCUSTOMOUTPUTTITLE( 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_VFILEREF IN CDR_PLANNED_OUTPUTS.FILEREF%TYPE, PI_VVALUE IN VARCHAR2 );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PI_VFILEREF (Mandatory) Enter the file reference name of the Planned Output to which you want to assign the title.
- PI_VVALUE (Mandatory) Enter the title text.
Parent topic: Set Execution Statuses
Set a Customized Output Subtitle
Use this API to set a subtitle for individual RSE outputs.
Name
CDR_PUB_EXE_USER_UTILS.SetCustomOutputSubtitle
Signature
PROCEDURE SETCUSTOMOUTPUTSUBTITLE( 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_VFILEREF IN CDR_PLANNED_OUTPUTS.FILEREF%TYPE, PI_VVALUE IN VARCHAR2 );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PI_VFILEREF (Mandatory) Enter the file reference name of the Planned Output to which you want to assign the subtitle.
- PI_VVALUE (Mandatory) Enter the subtitle text.
Parent topic: Set Execution Statuses
Set an Output Parameter
Use this API to send custom parameters and their values to Oracle LSH from external sources such as SAS. Parameter names and their values passed to this API get added to the cdr_temp_output_params table.
Name
CDR_PUB_EXE_USER_UTILS.SetOutputParams
Signature
PROCEDURE SETOUTPUTPARAMS( 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_VPARAMNAME IN CDR_TEMP_OUTPUT_PARAMS.PARAMETER_NAME%TYPE, PI_VPARAMVALUE IN CDR_TEMP_OUTPUT_PARAMS.PARAMETER_VALUE%TYPE );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PI_VPARAMNAME (Mandatory) Enter the parameter name.
- PI_VPARAMVALUE (Mandatory) Enter a value for the parameter.
Parent topic: Set Execution Statuses
Get a Completion Status
Use this API to retrieve a completion status value. The completion status value can only be one of the following: 1 for OK; 2 for OK With Warnings; 3 for Failure.
Name
CDR_PUB_EXE_USER_UTILS.GetCompletionStatus
Signature
FUNCTION GETCOMPLETIONSTATUS( 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 ) RETURN NUMBER;
Return
Type NUMBER
Description Completion Status. The values are 1: OK 2: OK_WITH_WARNINGS 3: FAILURE
Parameters
This API has standard parameters. See Standard Parameters) for details.
Parent topic: Set Execution Statuses
Submit Messages
This package contains one API related to the submission of messages. See the following topic for details:
Parent topic: Job Execution
Submit a Message
Use this API to add a submission request to the message queue.
Name
CDR_PUB_EXE_MSG_API.Submit_Message
Signature
PROCEDURE SUBMIT_MESSAGE( 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_MSG IN VARCHAR2 := NULL );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameter:
PI_MSG (Mandatory). Enter in XML format, the message that you want to add to the message queue.
Parent topic: Submit Messages
Create Submission Records
This package contains procedures to log messages during a job run and to create a submission record.
This section contains the following topics:
Parent topic: Job Execution
Start a Job
Use this API to start the execution of a job.
Name
CDR_PUB_EXE_SUBMISSION.StartJob
Signature
PROCEDURE STARTJOB( 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_VEXECMODE IN VARCHAR2 := 'NONE', PI_NJOBID IN CDR_JOBS_V.JOB_ID%TYPE, PI_NSTREAMID IN NUMBER := NULL );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PI_VEXECMODE (Mandatory) Enter the mode of execution. There are four possible values: SYNCHRONOUS (start the job and wait for completion), QUEUE (enqueue the job on the LSH queues), DIRECT (directly submit the job to OWB but do not wait), STREAM (The stream ID has to be set if this mode is used).
- PI_NJOBID (Mandatory) Enter the Job_ID of the job to be executed.
- PI_NSTREAMID Enter the OWB Stream ID. The default value is Null. This parameter is required only if mode=STREAM.
Parent topic: Create Submission Records
Create a Submission, Get Job ID
Use this API to create a submission before starting the job.
Name
CDR_PUB_EXE_SUBMISSION.CreateSubmission
Signature
FUNCTION CREATESUBMISSION( 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, PIO_OSUBMISSION IN OUT CDR_SUBMISSION_OBJ_TYPE, PIO_COSUBDETAILS IN OUT CDR_SUBMISSION_DETAILS_COLL, PI_JOBCONTEXTRC IN CDR_JOBS_V.JOB_CONTEXT_RC%TYPE, PI_EXECMODE IN VARCHAR2, PI_REFRESHTS IN CDR_JOBS_V.REFRESH_TS%TYPE ) RETURN CDR_JOBS_V.JOB_ID%TYPE;
Return
CDR_JOBS_V.JOB_ID%TYPE: The Job ID.
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PIO_OSUBMISSION (Mandatory) This parameter is of type
CDR_SUBMISSION_OBJ_TYPE.
Enter values for the following attributes: COMPANY_ID, EXECUTION_SETUP_OBJ_ID, EXECUTION_SETUP_OBJ_VER, WA_OBJ_ID, MASTER_PRREF_ID, MASTER_PRREF_VER, ACTIVE_FLAG_RC, SUBMISSION_TYPE_RC.
You can query MASTER_PRREF_ID and MASTER_PRREF_VER from CDR_PROGRAM_REFS_V with the Object ID and version of the object to be executed as the Prref Obj Id and Prref Obj ver.
- PIO_COSUBDETAILS (Mandatory) This parameter is part of a collection of
runtime parameters. It is of type CDR_SUBMISSION_DETAILS_COLL. Enter values for the
runtime parameters that you want to include in the job submission.
The required attributes are: COMPANY_ID, SUBMISSION_ID, PRREF_ID, PRREF_VER, PARAMETER_REF_OBJ_ID, PARAMETER_REF_OBJ_VER, PARAMETER_VALUE.
- PI_JOBCONTEXTRC (Mandatory) Enter the Job Context.
The possible values are the lookup_codes inside the lookup_type CDR_JOB_CONTEXTS: $JOBCONTEXT$BACKCHAIN, $JOBCONTEXT$COMPONENT, $JOBCONTEXT$SCHEDULED; $JOBCONTEXT$SUBMISSION.
- PI_EXECMODE (Mandatory) Enter a value for the execution mode. There are 3 possible values: SYNCHRONOUS (start the job and wait for completion), QUEUE (enqueue the job on the LSH queues), DIRECT (directly submit the job to OWB but would not wait).
- PI_REFRESHTS (Mandatory) Enter the Refresh timestamp you want to have associated with the job. It is normally 'sysdate,' but if you want to explicitly set a timestamp you can enter it here; for example, for a recovery job.
Parent topic: Create Submission Records
Create a Submission
This API creates a Submission from the initial Job.
Name
CDR_PUB_EXE_SUBMISSION.CreateSubmission
Signature
PROCEDURE CREATESUBMISSION( 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_OSUBMISSION IN OUT CDR_SUBMISSION_OBJ_TYPE, PIO_COPROGRAMSUBDETAILS IN OUT CDR_SUBMISSION_DETAILS_COLL, PIO_COSYSTEMSUBDETAILS IN OUT CDR_SUBMISSION_DETAILS_COLL, PI_NOAACCOUNTID IN CDR_SUBMISSIONS.OA_ACCOUNT_ID%TYPE := NULL, PI_COSNAPSHOT IN CDR_SNAPSHOT_TABLE_COLL := CDR_SNAPSHOT_TABLE_COLL() );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PIO_OSUBMISSION Object containing Submission attributes like submission_type_rc, execution_setup_id, master_prref_id etc.
- PIO_COPROGRAMSUBDETAILS This is part of submission details which is a collection of runtime parameters.
- PIO_COSYSTEMSUBDETAILS This is part of submission details which is a collection of system parameters.
- PI_NOAACCOUNTID Enter the OA account ID of the user making the submission.
- PI_COSNAPSHOT This is a collection of snapshots attributes like Tables Obj_id, Obj_Ver, source_master_job_Id etc.
Parent topic: Create Submission Records
Add a Job Log
Use this API to populate the cdr_job_log with the log entry for a given job.
Name
CDR_PUB_EXE_SUBMISSION.AddJobLogEntry
Signature
PROCEDURE ADDJOBLOGENTRY( 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_NCOMPANYID IN CDR_JOB_LOG.COMPANY_ID%TYPE, PI_NJOBID IN CDR_JOB_LOG.JOB_ID%TYPE, PI_VLOGENTRY IN VARCHAR2 );
Parameters
This API has standard parameters (see Standard Parameters) and the following parameters:
- PI_NCOMPANYID Enter the Company ID.
- PI_NJOBID Enter the Job ID.
- PI_VLOGENTRY Enter a Log Entry. This accepts a text message.
Parent topic: Create Submission Records