■ Testing PL/SQL Source Code on page 5-27
■ Creating a PL/SQL Package Storage Program on page 5-28 ■ Using a Sharable PL/SQL Package on page 5-28
■ Compiling and Executing a PL/SQL Program on page 5-28
■ Manipulating Documents through a PL/SQL Program on page 5-28 See also: Setting Up Oracle SQL Developer or SQL*Plus as an IDE on page 5-56
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: Example 5–1 Required Beginning of PL/SQL Code in a Primary Source Code File create or replace package PACKAGE_NAME as
procedure PROCEDURE_NAME (parameter_1 number,parameter_2 varchar2); end USER_PACKAGE_A;
/
create or replace package body USER_PACKAGE_A as procedure MAIN (parameter_1 number,
parameter_2 varchar2) is
Note: Each PL/SQL Source Code definition within a particular Program must have a unique Oracle package name.
Defining PL/SQL Programs
begin
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 LSH 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;
Defining PL/SQL Programs
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.
■ 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 (see "Required Syntax: Must Match Definitions" on page 5-25). 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.
Testing PL/SQL Source Code
To test PL/SQL code, you must first map the Table Descriptors to the Table instances and check in and install the Program instance and all the Table instances it reads from and writes to. You can then execute the Program. If the Program writes data to tables, you can check the data; see "Viewing Data" on page 3-69.
If the Program generates a report, you can see the report in the Reports tab. You can link to the report and the log file from the Job ID link in the Job Executions section of your My Home tab.
Note: In a PL/SQL Program a Source Code and a Table Descriptor cannot have the same Oracle name.
Defining PL/SQL Programs