Writing Primary Source Code in PL/SQL

When a Program is executed, the system launches its primary source code file.

You must use a specific syntax at the beginning of the PL/SQL source code and also write the source code in such a way that it calls every secondary Source Code instance you define and refers to all defined subcomponents by their Oracle name.

Required Syntax: Must Match Definitions: In the primary Source Code of a PL/SQL Program, the source code must begin by providing the Oracle Package name and Oracle Procedure name defined for the Source Code, and declare all Parameters defined in the Program with their data type, as shown in the following example, where the first Parameter is a number and the second Parameter is a varchar2:

create or replace package PACKAGE_NAME asprocedure PROCEDURE_NAME (parameter_1	 number,parameter_2	 varchar2);end PACKAGE_NAME;/create or replace package body PACKAGE_NAME asprocedure MAIN (parameter_1 number,		parameter_2 	varchar2) isbegin

Required Security Syntax: There is a potential security hole in PL/SQL Programs because they can be executed directly in the database outside of Oracle Life Sciences Data Hub security.

Oracle LSH can prevent this if you add a specific code template. You should add this to the beginning of the initialization block of your primary PL/SQL source code, as shown below. When you first install the Program, the system compiles the PL/SQL source code and inserts the actual Program ID generated by the system for the Program. At runtime, the system checks that a database account corresponding to the Program ID has been created. The service instance creates this database account to allow execution of the PL/SQL packages. If the account exists, then the job has been created through proper channels and is allowed to proceed. If it has not, the system does not allow execution to proceed.

If you do not include the recommended code template, when you install the Program, the system looks for either END; or END package_name; beginning at the end of the source code, and inserts the security code at that point. However, at runtime the Program is allowed to run up until that point. Any statements that appear in the initialization block before the security code are allowed to execute.

Add the following template exactly as appears:

BEGIN  /*Package initialization here*/
/* LSH GENERATES SECURITY CODE HERE, DO NOT REMOVE THIS COMMENT. */
/* Define your package initialization here */
NULL;

Insert the above template into the package initialization block of the package body, as follows:

CREATE OR REPLACE PACKAGE pkg1 AS
/* define your procedures here */
PROCEDURE proc1;
END pkg1;
/
CREATE OR REPLACE PACKAGE BODY pkg1 AS
/* define your parameters here */
 
/* define your procedures here */
PROCEDURE proc1 IS
BEGIN
/* Define code here */
NULL;
END proc1;
 
BEGIN  /*Package initialization here*/
/* LSH GENERATES SECURITY CODE HERE, DO NOT REMOVE THIS COMMENT. */
/* Define your package initialization here */
NULL;
END pkg1;

The first time you install the Program, the system updates your source code by inserting the following code, including the actual program_id generated for the program by the system:

IF NVL(SYS_CONTEXT('CDR_RUNTIME', <program-id>), 'X') <> 'Y'
THEN
   RAISE_APPLICATION_ERROR(-20005, 'EXECUTE NOT enabled.');
END IF;

Subcomponent References in PL/SQL: You must refer to the defined subcomponents of the Program in your PL/SQL source code as follows:

• Table Descriptors. For each table you read from or write to in your source code, you must define a source or target Table Descriptor. Refer to each Table Descriptor as if it were a real database table, using its Oracle name. If the Table instance to which a Table Descriptor is mapped has a different name from the Table Descriptor, use the Table Descriptor's name, not the Table instance's.

  Note:

  In a PL/SQL Program a Source Code and a Table Descriptor cannot have the same Oracle name.
• Secondary Source Code. Refer to secondary Source Code instances by their Oracle name.
• Parameters. You must create a defined Parameter for each input and output Parameter you use in your primary source code, and declare them in your source code. Refer to defined Parameters by their Oracle name.
• Planned Outputs. You must create a defined Planned Output for every output generated by the primary source code at execution, including the log file. Refer to each defined Planned Output by its File Name.

API for Ending PL/SQL Programs with a Status of Success, Warning, or Failure: Normally PL/SQL programs end with a status of Success unless there is a system failure or unhandled SQL exception. However, if you are using a Program in a Workflow, you may need to write your code so that the Program completes with a status of Warning or Failure, depending on circumstances. In a Workflow, you can use the completion status of a Program to determine which branch of activities to execute.

Oracle LSH ships with an API for this purpose called:

CDR_EXE_USER_UTILS.setCompletionStatus()

To call the package, enter one of the following lines of code in your source code exactly as it appears below, at the point where you want the Program to return a status of Success, Warning, or Failure:

CDR_EXE_USER_UTILS.setCompletionStatus(1); 
CDR_EXE_USER_UTILS.setCompletionStatus(2); 
CDR_EXE_USER_UTILS.setCompletionStatus(3); 

• CDR_EXE_USER_UTILS.setCompletionStatus(1) returns a status of Success.
• CDR_EXE_USER_UTILS.setCompletionStatus(2) returns a status of Warning.
• CDR_EXE_USER_UTILS.setCompletionStatus(3) returns a status of Failure.