Pro*COBOL Precompiler Programmer's Guide
Release 8.1.5

A68023-01

Library

Product

Contents

Index

Prev Next

6
Embedded PL/SQL

This chapter shows you how to improve performance by embedding PL/SQL transaction processing blocks in your program. This chapter has the following sections:

Embedding PL/SQL

Pro*COBOL treats a PL/SQL block like a single embedded SQL statement. So, you can place a PL/SQL block anywhere in a host program that you can place a SQL statement.

To embed a PL/SQL block in your host program, declare the variables to be shared with PL/SQL and bracket the PL/SQL block with the EXEC SQL EXECUTE and END-EXEC keywords.

Host Variables

Inside a PL/SQL block, host variables are global to the entire block and can be used anywhere a PL/SQL variable is allowed. Like host variables in a SQL statement, host variables in a PL/SQL block must be prefixed with a colon. The colon sets host variables apart from PL/SQL variables and database objects.

VARCHAR Variables

When entering a PL/SQL block, Oracle8i automatically checks the length fields of VARCHAR host variables, so you must set the length fields before the block is entered. For input variables, set the length field to the length of the value stored in the string field. For output variables, set the length field to the maximum length allowed by the string field.

Indicator Variables

In a PL/SQL block, you cannot refer to an indicator variable by itself; it must be appended to its associated host variable. Also, if you refer to a host variable with its indicator variable, you must always refer to it that way in the same block.

Handling NULLs

When entering a block, if an indicator variable has a value of -1, PL/SQL automatically assigns a NULL to the host variable. When exiting the block, if a host variable is NULL, PL/SQL automatically assigns a value of -1 to the indicator variable.

Handling Truncated Values

PL/SQL does not raise an exception when a truncated string value is assigned to a host variable. However, if you use an indicator variable, PL/SQL sets it to the original length of the string.

SQLCHECK

You must specify SQLCHECK=SEMANTICS when precompiling a program with an embedded PL/SQL block. You must also use the USERID option. For more information, see Chapter 14, "Precompiler Options".

Advantages of PL/SQL

This section looks at some of the features and benefits offered by PL/SQL, such as

For more information about PL/SQL, see PL/SQL User's Guide and Reference.

Better Performance

PL/SQL can help you reduce overhead, improve performance, and increase productivity. For example, without PL/SQL, Oracle8i must process SQL statements one at a time. Each SQL statement results in another call to the Server and higher overhead. However, with PL/SQL, you can send an entire block of SQL statements to the server. This minimizes communication between your application and the server.

Integration with Oracle8i

PL/SQL is tightly integrated with the server. For example, most PL/SQL datatypes are native to the data dictionary. Furthermore, you can use the %TYPE attribute to base variable declarations on column definitions stored in the data dictionary, as the following example shows:

job_title  emp.job%TYPE; 

That way, you need not know the exact datatype of the column. Furthermore, if a column definition changes, the variable declaration changes accordingly and automatically. This provides data independence, reduces maintenance costs, and allows programs to adapt as the database changes.

Cursor FOR Loops

With PL/SQL, you need not use the DECLARE, OPEN, FETCH, and CLOSE statements to define and manipulate a cursor. Instead, you can use a cursor FOR loop, which implicitly declares its loop index as a record, opens the cursor associated with a given query, repeatedly fetches data from the cursor into the record, then closes the cursor. An example follows:

DECLARE 
    ... 
BEGIN 
    FOR emprec IN (SELECT empno, sal, comm FROM emp) LOOP 
        IF emprec.comm / emprec.sal > 0.25 THEN ... 
        ... 
    END LOOP; 
END; 

Notice that you use dot notation to reference fields in the record.

Subprograms

PL/SQL has two types of subprograms called procedures and functions, which aid application development by letting you isolate operations. Generally, you use a procedure to perform an action and a function to compute a value.

Procedures and functions provide extensibility. That is, they let you tailor the PL/SQL language to suit your needs. For example, if you need a procedure that creates a new department, just write your own as follows:

PROCEDURE create_dept 
    (new_dname  IN CHAR(14), 
     new_loc    IN CHAR(13), 
     new_deptno OUT NUMBER(2)) IS 
BEGIN 
    SELECT deptno_seq.NEXTVAL INTO new_deptno FROM dual; 
    INSERT INTO dept VALUES (new_deptno, new_dname, new_loc); 
END create_dept; 

When called, this procedure accepts a new department name and location, selects the next value in a department-number database sequence, inserts the new number, name, and location into the dept table, then returns the new number to the caller.

You can store subprograms in the database (using CREATE FUNCTION and CREATE PROCEDURE) that can be called from multiple applications without needing to be re-compiled each time.

Parameter Modes

You use parameter modes to define the behavior of formal parameters. There are three parameter modes: IN (the default), OUT, and IN OUT. An IN parameter lets you pass values to the subprogram being called. An OUT parameter lets you return values to the caller of a subprogram. An IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller.

The datatype of each actual parameter must be convertible to the datatype of its corresponding formal parameter. Table 6-1 shows the legal conversions between datatypes.

Packages

PL/SQL lets you bundle logically related types, program objects, and subprograms into a package. Packages can be compiled and stored in a database, where their contents can be shared by multiple applications.

Packages usually have two parts: a specification and a body. The specification is the interface to your applications; it declares the types, constants, variables, exceptions, cursors, and subprograms available for use. The body defines cursors and subprograms and so implements the specification. In the following example, you "package" two employment procedures:

PACKAGE emp_actions IS  -- package specification 
    PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...); 
    PROCEDURE fire_employee (emp_id NUMBER); 
END emp_actions; 

PACKAGE BODY emp_actions IS  -- package body 
    PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS 
    BEGIN 
        INSERT INTO emp VALUES (empno, ename, ...); 
    END hire_employee; 
    PROCEDURE fire_employee (emp_id NUMBER) IS 
    BEGIN 
        DELETE FROM emp WHERE empno = emp_id; 
    END fire_employee; 
END emp_actions; 

Only the declarations in the package specification are visible and accessible to applications. Implementation details in the package body are hidden and inaccessible.

PL/SQL Tables

PL/SQL provides a composite datatype named TABLE. Objects of type TABLE are called PL/SQL tables, which are modeled as (but not the same as) database tables. PL/SQL tables have only one column and use a primary key to give you array-like access to rows. The column can belong to any scalar type (such as CHAR, DATE, or NUMBER), but the primary key must belong to type BINARY_INTEGER.

You can declare PL/SQL table types in the declarative part of any block, procedure, function, or package. In the following example, you declare a TABLE type called NumTabTyp:

DECLARE 
    TYPE NumTabTyp IS TABLE OF NUMBER 
        INDEX BY BINARY_INTEGER; 
    ... 
BEGIN 
    ... 
END; 

Once you define type NumTabTyp, you can declare PL/SQL tables of that type, as the next example shows:

num_tab  NumTabTyp; 

The identifier num_tab represents an entire PL/SQL table.

You reference rows in a PL/SQL table using array-like syntax to specify the primary key value. For example, you reference the ninth row in the PL/SQL table named num_tab as follows:

num_tab(9) ... 

User-Defined Records

You can use the %ROWTYPE attribute to declare a record that represents a row in a database table or a row fetched by a cursor. However, you cannot specify the datatypes of fields in the record or define fields of your own. The composite datatype RECORD lifts those restrictions.

Objects of type RECORD are called records. Unlike PL/SQL tables, records have uniquely named fields, which can belong to different datatypes. For example, suppose you have different kinds of data about an employee such as name, salary, hire date, and so on. This data is dissimilar in type but logically related. A record that contains such fields as the name, salary, and hire date of an employee would let you treat the data as a logical unit.

You can declare record types and objects in the declarative part of any block, procedure, function, or package. In the following example, you declare a RECORD type called DeptRecTyp:

DECLARE 
    TYPE DeptRecTyp IS RECORD 
        (deptno  NUMBER(4) NOT NULL := 10,  -- must initialize
         dname   CHAR(9), 
         loc     CHAR(14)); 

Notice that the field declarations are like variable declarations. Each field has a unique name and specific datatype. You can add the NOT NULL option to any field declaration and so prevent the assigning of NULLs to that field. However, you must initialize NOT NULL fields.

Once you define type DeptRecTyp, you can declare records of that type, as the next example shows:

dept_rec  DeptRecTyp; 

The identifier dept_rec represents an entire record.

You use dot notation to reference individual fields in a record. For example, you reference the dname field in the dept_rec record as follows:

dept_rec.dname ... 

Embedding PL/SQL Blocks

Pro*COBOL treats a PL/SQL block like a single embedded SQL statement. So, you can place a PL/SQL block anywhere in a host program that you can place a SQL statement.

To embed a PL/SQL block in your host program, simply bracket the PL/SQL block with the keywords EXEC SQL EXECUTE and END-EXEC as follows:

     EXEC SQL EXECUTE 
         DECLARE 
         ... 
         BEGIN 
         ... 
         END;
     END-EXEC. 

When your program embeds PL/SQL blocks, you must specify the precompiler option SQLCHECK=SEMANTICS because PL/SQL must be parsed by Pro*COBOL. To connect to the server, you must also specify the option USERID. For more information, see "Using Pro*COBOL Precompiler Options".

Host Variables and PL/SQL

Host variables are the key to communication between a host language and a PL/SQL block. Host variables can be shared with PL/SQL, meaning that PL/SQL can set and reference host variables.

For example, you can prompt a user for information and use host variables to pass that information to a PL/SQL block. Then, PL/SQL can access the database and use host variables to pass the results back to your host program.

Inside a PL/SQL block, host variables are treated as global to the entire block and can be used anywhere a PL/SQL variable is allowed. However, character host variables cannot exceed 255 characters in length. Like host variables in a SQL statement, host variables in a PL/SQL block must be prefixed with a colon. The colon sets host variables apart from PL/SQL variables and database objects.

PL/SQL Examples

The following example illustrates the use of host variables with PL/SQL. The program prompts the user for an employee number, then displays the job title, hire date, and salary of that employee.

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
 01 USERNAME   PIC X(20) VARYING.
 01 PASSWORD   PIC X(20) VARYING.
 01 EMP-NUMBER PIC S9(4) COMP.
 01 JOB-TITLE  PIC X(20) VARYING.
 01 HIRE-DATE  PIC X(9) VARYING.
 01 SALARY     PIC S9(6)V99
                          DISPLAY SIGN LEADING SEPARATE.
     EXEC SQL END DECLARE SECTION END-EXEC. 
     EXEC SQL INCLUDE SQLCA END-EXEC. 
     ...
     DISPLAY 'Username? ' WITH NO ADVANCING. 
     ACCEPT USERNAME. 
     DISPLAY 'Password? ' WITH NO ADVANCING.
     ACCEPT PASSWORD. 
     EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR. 
     EXEC SQL
         CONNECT :USERNAME IDENTIFIED BY :PASSWORD
     END-EXEC.
     DISPLAY 'Connected to Oracle'. 
 PERFORM
     DISPLAY 'Employee Number (0 to end)? 'WITH NO ADVANCING 
     ACCEPTd EMP-NUMBER 
     IF EMP-NUMBER = 0  
         EXEC SQL COMMIT WORK RELEASE END-EXEC 
         DISPLAY 'Exiting program' 
         STOP RUN
     END-IF. 
*    ---------------- begin PL/SQL block ----------------- 
     EXEC SQL EXECUTE 
        BEGIN 
            SELECT job, hiredate, sal 
                INTO :JOB-TITLE, :HIRE-DATE, :SALARY 
                FROM EMP 
                WHERE EMPNO = :EMP-NUMBER; 
        END; 
     END-EXEC. 
*    ---------------- end PL/SQL block ----------------- 
     DISPLAY 'Number  Job Title  Hire Date  Salary'. 
     DISPLAY '------------------------------------'. 
     DISPLAY EMP-NUMBER, JOB-TITLE, HIRE-DATE, SALARY. 
 END-PERFORM. 
     ...
 SQL-ERROR.
     EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. 
     EXEC SQL ROLLBACK WORK RELEASE END-EXEC. 
     DISPLAY 'Processing error'. 
     STOP RUN.

Notice that the host variable EMP-NUMBER is set before the PL/SQL block is entered, and the host variables JOB-TITLE, HIRE-DATE, and SALARY are set inside the block.

A More Complex Example

In the example below, you prompt the user for a bank account number, transaction type, and transaction amount, then debit or credit the account. If the account does not exist, you raise an exception. When the transaction is complete, you display its status.

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
 01 USERNAME   PIC X(20) VARYING.
 01 ACCT-NUM   PIC S9(4) COMP.
 01 TRANS-TYPE PIC X(1).
 01 TRANS-AMT  PIC PIC S9(6)V99
                       DISPLAY SIGN LEADING SEPARATE.
 01 STATUS     PIC X(80) VARYING.
     EXEC SQL END DECLARE SECTION END-EXEC.
     EXEC SQL INCLUDE SQLCA END-EXEC. 
     DISPLAY 'Username? 'WITH NO ADVANCING. 
     ACCEPT USERNAME. 
     DISPLAY 'Password? '. 
     ACCEPT PASSWORD. 
     EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR. 
     EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD. 
     PERFORM 
     DISPLAY 'Account Number (0 to end)? '
         WITH NO ADVANCING 
     ACCEPT ACCT_NUM 
     IF ACCT-NUM = 0
         EXEC SQL COMMIT WORK RELEASE END-EXEC 
         DISPLAY 'Exiting program' WITH NO ADVANCING 
         STOP RUN 
     END-IF.
     DISPLAY 'Transaction Type - D)ebit or C)redit? ' 
         WITH NO ADVANCING
     ACCEPT TRANS-TYPE 
     DISPLAY 'Transaction Amount? ' 
     ACCEPT trans_amt 
*   --------------------- begin PL/SQL block ------------------- 
     EXEC SQL EXECUTE 
        DECLARE 
            old_bal      NUMBER(9,2); 
            err_msg      CHAR(70); 
            nonexistent  EXCEPTION; 
        BEGIN 
           :TRANS-TYP-TYPE = 'C' THEN       -- credit the account 
                UPDATE accts SET bal = bal + :TRANS-AMT 
                    WHERE acctid = :acct-num; 
                IF SQL%ROWCOUNT = 0 THEN    -- no rows affected 
                    RAISE nonexistent; 
                ELSE 
                    :STATUs := 'Credit applied'; 
                END IF; 
            ELSIF :TRANS-TYPe = 'D' THEN    -- debit the account 
                SELECT bal INTO old_bal FROM accts 
                    WHERE acctid = :ACCT-NUM; 
                IF old_bal >= :TRANS-AMT THEN   -- enough funds 
                    UPDATE accts SET bal = bal - :TRANS-AMT 
                        WHERE acctid = :ACCT-NUM; 
                    :STATUS := 'Debit applied'; 
                ELSE 
                    :STATUS := 'Insufficient funds'; 
                END IF; 
            ELSE 
              :STATUS := 'Invalid type: ' || :TRANS-TYPE; 
            END IF; 
            COMMIT; 
        EXCEPTION 
            WHEN NO_DATA_FOUND OR nonexistent THEN 
                :STATUS := 'Nonexistent account'; 
            WHEN OTHERS THEN 
                err_msg := SUBSTR(SQLERRM, 1, 70); 
               :STATUS := 'Error: ' || err_msg; 
        END; 
     END-EXEC. 
*     ------------------- end PL/SQL block ----------------------- 
     DISPLAY 'Status: ', STATUS 
 END-PERFORM.
     ...
 SQL-ERROR.
     EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. 
     EXEC SQL ROLLBACK WORK RELEASE END-EXEC. 
     DISPLAY 'Processing error'. 
     STOP RUN. 

VARCHAR Pseudotype

Recall that you can use the VARCHAR pseudotype to declare variable-length character strings. If the VARCHAR is an input host variable, you must tell Pro*COBOL what length to expect. So, set the length field to the actual length of the value stored in the string field.

If the VARCHAR is an output host variable, Pro*COBOL automatically sets the length field. However, to use a VARCHAR output host variable in your PL/SQL block, you must initialize the length field before entering the block. So, set the length field to the declared (maximum) length of the VARCHAR, as shown in the following example:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
        01 EMP-NUM   PIC S9(4) COMP.
        01 EMP-NAME  PIC X(10) VARYING.
        01 SALARY    PIC S9(6)V99
                     DISPLAY SIGN LEADING SEPARATE.
     ... 
     EXEC SQL END DECLARE SECTION END-EXEC.
 PROCEDURE DIVISION.
     ...
* -- initialize length field 
     MOVE 10 TO EMP-NAME-LEN.
     EXEC SQL EXECUTE 
     BEGIN 
        SELECT ename, sal INTO :EMP-NAME, :SALARY 
            FROM emp 
            WHERE empno = :EMP-NUM; 
        ... 
     END; 
     END-EXEC. 

Indicator Variables and PL/SQL

PL/SQL does not need indicator variables because it can manipulate NULLs. For example, within PL/SQL, you can use the IS NULL operator to test for NULLs, as follows:

IF variable IS NULL THEN ... 

You can use the assignment operator (:=) to assign NULLs, as follows:

variable := NULL; 

However, host languages need indicator variables because they cannot manipulate NULLs. Embedded PL/SQL meets this need by letting you use indicator variables to

When used in a PL/SQL block, indicator variables are subject to the following rules:

In the following example, the indicator variable IND-COMM appears with its host variable COMMISSION in the SELECT statement, so it must appear that way in the IF statement:

     EXEC SQL EXECUTE 
     BEGIN 
        SELECT ename, comm 
            INTO :EMP-NAME, :COMMISSION:IND-COMM FROM emp 
            WHERE empno = :EMP-NUM; 
        IF :COMMISSION:IND-COMM IS NULL THEN ... 
        ... 
     END; 
     END-EXEC. 

Notice that PL/SQL treats :COMMISSION:IND-COMM like any other simple variable. Though you cannot refer directly to an indicator variable inside a PL/SQL block, PL/SQL checks the value of the indicator variable when entering the block and sets the value correctly when exiting the block.

Handling NULLs

When entering a block, if an indicator variable has a value of -1, PL/SQL automatically assigns a NULL to the host variable. When exiting the block, if a host variable is NULL, PL/SQL automatically assigns a value of -1 to the indicator variable. In the next example, if IND-SAL had a value of -1 before the PL/SQL block was entered, the salary_missing exception is raised. An exception is a named error condition.

    EXEC SQL EXECUTE 
    BEGIN 
        IF :SALARY:IND-SAL IS NULL THEN 
            RAISE salary_missing; 
        END IF; 
        ... 
    END; 
    END-EXEC. 

Handling Truncated Values

PL/SQL does not raise an exception when a truncated string value is assigned to a host variable. However, if you use an indicator variable, PL/SQL sets it to the original length of the string. In the following example, the host program will be able to tell, by checking the value of IND-NAME, if a truncated value was assigned to EMP-NAME:

     EXEC SQL EXECUTE 
     DECLARE 
        ... 
        new_name  CHAR(10); 
     BEGIN 
        ... 
        :EMP_NAME:IND-NAME := new_name; 
        ... 
     END; 
     END-EXEC. 

Host Tables and PL/SQL

You can pass input host tables and indicator tables to a PL/SQL block. They can be indexed by a PL/SQL variable of type BINARY_INTEGER or by a host variable compatible with that type. Normally, the entire host table is passed to PL/SQL, but you can use the ARRAYLEN statement (discussed later) to specify a smaller table dimension.

Furthermore, you can use a subprogram call to assign all the values in a host table to rows in a PL/SQL table. Given that the table subscript range is m .. n, the corresponding PL/SQL table index range is always 1 .. (n - m + 1). For example, if the table subscript range is 5 .. 10, the corresponding PL/SQL table index range is 1 .. (10 - 5 + 1) or 1 .. 6.

Note: Pro*COBOL does not check your usage of host tables. For instance, no index range checking is done.

In the example below, you pass a host table named salary to a PL/SQL block, which uses the host table in a function call. The function is named median because it finds the middle value in a series of numbers. Its formal parameters include a PL/SQL table named num_tab. The function call assigns all the values in the actual parameter salary to rows in the formal parameter num_tab.

    EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
    ... 
01 SALARY OCCURS 100 TIMES PIC S9(6)V99
        DISPLAY SIGN LEADING SEPARATE. 
        01 MEDIAN-SALARY  PIC S9(6)V99
        DISPLAY SIGN LEADING SEPARATE.   
     EXEC SQL END DECLARE SECTION END-EXEC. 
* -- populate the host table 
     EXEC SQL EXECUTE 
     DECLARE 
        TYPE NumTabTyp IS TABLE OF REAL 
            INDEX BY BINARY_INTEGER; 
        n  BINARY_INTEGER; 
        ... 
        FUNCTION median (num_tab NumTabTyp, n INTEGER) 
            RETURN REAL IS 
        BEGIN 
* -- compute median 
        END; 
     BEGIN 
        n := 100; 
        :MEDIAN-SALARY := median(:SALARY    END; 
     END-EXEC. 

You can also use a subprogram call to assign all row values in a PL/SQL table to corresponding elements in a host table. For an example, see "Stored PL/SQL and Java Subprograms".

Table 6-1 shows the legal conversions between row values in a PL/SQL table and elements in a host table. For example, a host table of type LONG is compatible with a PL/SQL table of type VARCHAR2, LONG, RAW, or LONG RAW. Notably, it is not compatible with a PL/SQL table of type CHAR.

Table 6-1 Legal Datatype Conversions
PL/SQL Table  
Host table  

CHAR  

DATE  

LONG  

LONG RAW  

NUMBER  

RAW  

ROWID  

VARCHAR2  

CHARF  

X  

 

 

 

 

 

 

 

CHARZ  

X  

 

 

 

 

 

 

 

DATE  

 

X  

 

 

 

 

 

 

DECIMAL  

 

 

 

 

X  

 

 

 

DISPLAY  

 

 

 

 

X  

 

 

 

FLOAT  

 

 

 

 

X  

 

 

 

INTEGER  

 

 

 

 

X  

 

 

 

LONG  

X  

 

X  

 

 

 

 

 

LONG VARCHAR  

 

 

X  

X  

 

X  

 

X  

LONG VARRAW  

 

 

 

X  

 

X  

 

 

NUMBER  

 

 

 

 

X  

 

 

 

RAW  

 

 

 

X  

 

X  

 

 

ROWID  

 

 

 

 

 

 

X  

 

STRING  

 

 

X  

X  

 

X  

 

X  

UNSIGNED  

 

 

 

 

X  

 

 

 

VARCHAR  

 

 

X  

X  

 

X  

 

X  

VARCHAR2  

 

 

X  

X  

 

X  

 

X  

VARNUM  

 

 

 

 

X  

 

 

 

VARRAW  

 

 

 

X  

 

X  

 

 

ARRAYLEN Statement

Suppose you must pass an input host table to a PL/SQL block for processing. By default, when binding such a host table, Pro*COBOL use its declared dimension. However, you might not want to process the entire table. In that case, you can use the ARRAYLEN statement to specify a smaller table dimension. ARRAYLEN associates the host table with a host variable, which stores the smaller dimension. The statement syntax is:

     EXEC SQL ARRAYLEN host_array (dimension) EXECUTE END-EXEC. 

where dimension is a 4-byte, integer host variable, not a literal or an expression.

The ARRAYLEN statement must appear somewhere after the declarations of host_array and dimension. You cannot specify an offset into the host table. However, you might be able to use COBOL features for that purpose.

In the following example, you use ARRAYLEN to override the default dimension of a host table named BONUS:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
 01 BONUS OCCURS 100 TIMES PIC S9(6)V99
         DISPLAY SIGN LEADING SEPARATE.
 01 MY-DIM  PIC S9(4) COMP. 
     ...
     EXEC SQL ARRAYLEN BONUS (MY-DIM) END-EXEC. 
     EXEC SQL END DECLARE SECTION END-EXEC. 
* -- populate the host table 
     ... 
* -- set smaller table dimension 
     MOVE 25 TO MY-DIM.
     EXEC SQL EXECUTE 
     DECLARE 
        TYPE NumTabTyp IS TABLE OF REAL 
            INDEX BY BINARY_INTEGER; 
        median_bonus  REAL; 
        FUNCTION median (num_tab NumTabTyp, n INTEGER) 
            RETURN REAL IS 
        BEGIN 
* -- compute median 
        END; 
     BEGIN 
        median_bonus := median(:BONUS, :MY-DIM); 
         ... 
     END; 
     END-EXEC. 

Only 25 table elements are passed to the PL/SQL block because ARRAYLEN reduces the host table from 100 to 25 elements. As a result, when the PL/SQL block is sent to the server for execution, a much smaller host table is sent along. This saves time and, in a networked environment, reduces network traffic.

Optional Keyword EXECUTE

Host tables used in a dynamic SQL method 2 EXEC SQL EXECUTE statement may have two different interpretations based on the presence or absence of the optional keyword EXECUTE. See "Using Method 2".

By default (if the EXECUTE keyword is absent):

If the keyword EXECUTE is present:

For example, given the following PL/SQL procedure:

  CREATE OR REPLACE PACKAGE pkg AS  
     TYPE tab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;  
     PROCEDURE proc1 (parm1 tab, parm2 NUMBER, parm3 tab);  
  END;  
  

The following Pro*COBOL example demonstrates how host tables can be used to determine how many times a given PL/SQL block is executed. In this case, the PL/SQL block will be execute 3 times resulting in 3 new rows in the emp table.

       ... 
 01  DYNSTMT   PIC X(80) VARYING. 
 01  EMPNOTAB  PIC S9(4) COMPUTATIONAL OCCURS 5 TIMES. 
 01  ENAMETAB  PIC X(10) OCCURS 3 TIMES. 
 01  DIM       PIC S9(9) COMP VALUE 2. 
       ... 
           MOVE 1111 TO EMPNOTAB(1). 
           MOVE 2222 TO EMPNOTAB(2). 
           MOVE 3333 TO EMPNOTAB(3). 
           MOVE 4444 TO EMPNOTAB(4). 
           MOVE 5555 TO EMPNOTAB(5). 
  
           MOVE "MICKEY" TO ENAMETAB(1). 
           MOVE "MINNIE" TO ENAMETAB(2). 
           MOVE "GOOFY" TO ENAMETAB(3). 
  
           MOVE "BEGIN INSERT INTO emp(empno, ename) VALUES :b1, :b2; END;" 
             TO DYNSTMT-ARR. 
           MOVE 57 TO DYNSTMT-LEN. 
   
           EXEC SQL PREPARE s1 FROM :DYNSTMT END-EXEC. 
           EXEC SQL EXECUTE s1 USING :EMPNOTAB, :ENAMETAB END-EXEC. 
       ... 
  

The following Pro*COBOL example demonstrates how to bind a host table to a PL/SQL index table through dynamic method 2. Note the presence of the ARRAYLEN...EXECUTE statement for all host arrays specified in the EXEC SQL EXECUTE statement.

       ... 
 01  DYNSTMT   PIC X(80) VARYING. 
 01  II        PIC S9(4) COMP VALUE 2. 
 01  INTTAB    PIC S9(9) COMP OCCURS 3 TIMES. 
 01  DIM       PIC S9(9) COMP VALUE 3. 
  
           EXEC SQL ARRAYLEN INTTAB (DIM) EXECUTE END-EXEC. 
       ... 
           MOVE 1 TO INTTAB(1). 
           MOVE 2 TO INTTAB(2). 
           MOVE 3 TO INTTAB(3). 
  
           MOVE "BEGIN pkg.proc1 (:v1, :v2, :v3); end;"; 
             TO DYNSTMT-ARR. 
           MOVE 37 TO DYNSTMT-LEN. 
   
           EXEC SQL PREPARE s1 FROM :DYNSTMT END-EXEC. 
           EXEC SQL EXECUTE s1 USING :INTTAB, :II, :INTTAB END-EXEC. 
       ... 
  

However, the following Pro*COBOL example will result in a precompile-time error because there is no ARRAYLEN...EXECUTE statement for INTTAB2.

       ... 
 01  DYNSTMT   PIC X(80) VARYING. 
 01  INTTAB    PIC S9(9) COMP OCCURS 3 TIMES. 
 01  INTTAB2   PIC S9(9) COMP OCCURS 3 TIMES. 
 01  DIM       PIC S9(9) COMP VALUE 3. 
  
           EXEC SQL ARRAYLEN INTTAB (DIM) EXECUTE END-EXEC. 
       ... 
           MOVE 1 TO INTTAB(1). 
           MOVE 2 TO INTTAB(2). 
           MOVE 3 TO INTTAB(3). 
  
           MOVE "BEGIN pkg.proc1 (:v1, :v2, :v3); end;"; 
             TO DYNSTMT-ARR. 
           MOVE 37 TO DYNSTMT-LEN. 
   
           EXEC SQL PREPARE s1 FROM :DYNSTMT END-EXEC. 
           EXEC SQL EXECUTE s1 USING :INTTAB, :INTTAB2, :INTTAB END-EXEC. 
       ... 

Cursor Usage in Embedded PL/SQL

The maximum number of cursors your program can use simultaneously is determined by the database initialization parameter OPEN_CURSORS. While executing an embedded PL/SQL block, one cursor. the parent cursor, is associated with the entire block and one cursor, the child cursor, is associated with each SQL statement in the embedded PL/SQL block. Both parent and child cursors count toward the OPEN_CURSORS limit. Figure 6-1, "Maximum Cursors in Use" shows you how to calculate the maximum number of cursors used.

Figure 6-1 Maximum Cursors in Use


If your program exceeds the limit imposed by OPEN_CURSORS, you get an error: You can avoid this error by specifying the RELEASE_CURSOR=YES and HOLD_CURSOR=NO options. If you do not want to precompile the entire program with RELEASE_CURSOR set to YES, simply reset it to NO after each PL/SQL block, as follows:

     EXEC ORACLE OPTION (RELEASE_CURSOR=YES) END-EXEC. 
* --  first embedded PL/SQL block 
     EXEC ORACLE OPTION (RELEASE_CURSOR=NO)END-EXEC. 
* --  embedded SQL statements
     EXEC ORACLE OPTION (RELEASE_CURSOR=YES)END-EXEC. 
* --  second embedded PL/SQL block 
     EXEC ORACLE OPTION (RELEASE_CURSOR=NO)END-EXEC. 
* --  embedded SQL statements 

For more details on this subject, see "Embedded PL/SQL Considerations".

Stored PL/SQL and Java Subprograms

Unlike anonymous blocks, PL/SQL subprograms (procedures and functions) and Java methods, can be compiled separately, stored in the database, and invoked.

A subprogram explicitly created using an Oracle tool such as SQL*Plus is called a stored subprogram. Once compiled and stored in the data dictionary, it is a database object, which can be re-executed without being re-compiled.

When a subprogram within a PL/SQL block or stored subprogram is sent to the database by your application, it is called an inline subprogram. Pro*COBOL compiles the inline subprogram and caches it in the System Global Area (SGA), but does not store the source or object code in the data dictionary.

Subprograms defined within a package are considered part of the package, and so are called packaged subprograms. Stored subprograms not defined within a package are called stand-alone subprograms.

Creating Stored Subprograms

You can embed the SQL statements CREATE FUNCTION, CREATE PROCEDURE, and CREATE PACKAGE in a COBOL program, as the following example shows:

     EXEC SQL CREATE 
     FUNCTION sal_ok (salary REAL, title CHAR) 
      RETURN BOOLEAN AS 
        min_sal  REAL; 
        max_sal  REAL; 
     BEGIN 
        SELECT losal, hisal INTO min_sal, max_sal 
            FROM sals 
            WHERE job = title; 
        RETURN (salary >= min_sal) AND 
               (salary <= max_sal); 
     END sal_ok; 
     END-EXEC. 

Notice that the embedded CREATE {FUNCTION | PROCEDURE | PACKAGE} statement is a hybrid. Like all other embedded CREATE statements, it begins with the keywords EXEC SQL (not EXEC SQL EXECUTE). But, unlike other embedded CREATE statements, it ends with the PL/SQL terminator END-EXEC.

In the example below, you create a package that contains a procedure named get_employees, which fetches a batch of rows from the emp table. The batch size is determined by the caller of the procedure, which might be another stored subprogram or a client application program.

The procedure declares three PL/SQL tables as OUT formal parameters, then fetches a batch of employee data into the PL/SQL tables. The matching actual parameters are host tables. When the procedure finishes, it automatically assigns all row values in the PL/SQL tables to corresponding elements in the host tables.

     EXEC SQL CREATE OR REPLACE PACKAGE emp_actions AS 
       TYPE CharArrayTyp IS TABLE OF VARCHAR2(10) 
           INDEX BY BINARY_INTEGER; 
       TYPE NumArrayTyp IS TABLE OF FLOAT 
           INDEX BY BINARY_INTEGER; 
       PROCEDURE get_employees( 
           dept_number IN     INTEGER, 
           batch_size  IN     INTEGER, 
           found       IN OUT INTEGER, 
           done_fetch  OUT    INTEGER, 
           emp_name    OUT    CharArrayTyp, 
           job-title   OUT    CharArrayTyp, 
           salary      OUT    NumArrayTyp); 
       END emp_actions; 
     END-EXEC. 
     EXEC SQL CREATE OR REPLACE PACKAGE BODY emp_actions AS 
           CURSOR get_emp (dept_number IN INTEGER) IS 
           SELECT ename, job, sal FROM emp 
               WHERE deptno = dept_number; 
       PROCEDURE get_employees( 
           dept_number IN     INTEGER, 
           batch_size  IN     INTEGER, 
           found       IN OUT INTEGER, 
           done_fetch  OUT    INTEGER, 
           emp_name    OUT    CharArrayTyp, 
           job_title   OUT    CharArrayTyp, 
           salary      OUT    NumArrayTyp) IS 
       BEGIN 
           IF NOT get_emp%ISOPEN THEN 
               OPEN get_emp(dept_number); 
           END IF; 
           done_fetch := 0; 
           found := 0; 
           FOR i IN 1..batch_size LOOP 
               FETCH get_emp INTO emp_name(i), 
                   job_title(i), salary(i); 
               IF get_emp%NOTFOUND THEN 
                   CLOSE get_emp; 
                   done_fetch := 1; 
                   EXIT; 
               ELSE 
                   found := found + 1; 
               END IF; 
           END LOOP; 
       END get_employees; 
     END emp_actions; 
     END-EXEC. 

You specify the REPLACE clause in the CREATE statement to redefine an existing package without having to drop the package, recreate it, and re-grant privileges on it. For the full syntax of the CREATE statement see the Oracle8i SQL Reference.

If an embedded CREATE {FUNCTION | PROCEDURE | PACKAGE} statement fails, Oracle8i generates a warning, not an error.

Calling a Stored PL/SQL or Java Subprogram

To call a stored subprogram from your host program, you can use either an anonymous PL/SQL block, or the CALL embedded SQL statement.

Anonymous PL/SQL Block

In the following example, you call a stand-alone procedure named raise_salary:

     EXEC SQL EXECUTE 
     BEGIN 
        raise_salary(:emp_id, :increase); 
     END; 
     END-EXEC. 

Notice that stored subprograms can take parameters. In this example, the actual parameters emp_id and increase are host variables.

In the next example, the procedure raise_salary is stored in a package named emp_actions, so you must use dot notation to fully qualify the procedure call:

     EXEC SQL EXECUTE 
     BEGIN 
        emp_actions.raise_salary(:emp_id, :increase); 
     END; 
     END-EXEC. 

An actual IN parameter can be a literal, host variable, host table, PL/SQL constant or variable, PL/SQL table, PL/SQL user-defined record, subprogram call, or expression. However, an actual OUT parameter cannot be a literal, subprogram call, or expression.

You must use precompiler option SQLCHECK=SEMANTICS with an embedded PL/SQL block.

Sample Program 9: Calling a Stored Procedure

Before trying the sample program, you must create a PL/SQL package named calldemo, by running a script named CALLDEMO.SQL, which is supplied with Pro*COBOL and shown below. The script can be found in the Pro*COBOL demo library. Check your system-specific Oracle documentation for exact spelling of the script.

CREATE OR REPLACE PACKAGE calldemo AS

   TYPE name_array IS TABLE OF emp.ename%type
       INDEX BY BINARY_INTEGER;
   TYPE job_array IS TABLE OF emp.job%type
       INDEX BY BINARY_INTEGER;
   TYPE sal_array IS TABLE OF emp.sal%type
       INDEX BY BINARY_INTEGER;

   PROCEDURE get_employees(
     dept_number IN     number,    -- department to query
     batch_size  IN     INTEGER,   -- rows at a time
     found       IN OUT INTEGER,   -- rows actually returned
     done_fetch  OUT    INTEGER,   -- all done flag
     emp_name    OUT    name_array,
     job         OUT    job_array,
     sal         OUT    sal_array);

END calldemo;
/

CREATE OR REPLACE PACKAGE BODY calldemo AS

   CURSOR get_emp (dept_number IN number) IS
       SELECT ename, job, sal FROM emp
           WHERE deptno = dept_number;


   -- Procedure "get_employees" fetches a batch of employee
   -- rows (batch size is determined by the client/caller
   -- of the procedure).  It can be called from other
   -- stored procedures or client application programs.
   -- The procedure opens the cursor if it is not
   -- already open, fetches a batch of rows, and
   -- returns the number of rows actually retrieved. At
   -- end of fetch, the procedure closes the cursor.

   PROCEDURE get_employees(
     dept_number IN     number,
     batch_size  IN     INTEGER,
     found       IN OUT INTEGER,
     done_fetch  OUT    INTEGER,
     emp_name    OUT    name_array,
     job         OUT    job_array,
     sal         OUT    sal_array) IS

   BEGIN
       IF NOT get_emp%ISOPEN THEN      -- open the cursor if
           OPEN get_emp(dept_number);  -- not already open
       END IF;

       -- Fetch up to "batch_size" rows into PL/SQL table,
       -- tallying rows found as they are retrieved. When all
       -- rows have been fetched, close the cursor and exit
       -- the loop, returning only the last set of rows found.

       done_fetch := 0;  -- set the done flag FALSE
       found := 0;

       FOR i IN 1..batch_size LOOP
           FETCH get_emp INTO emp_name(i), job(i), sal(i);
           IF get_emp%NOTFOUND THEN    -- if no row was found
               CLOSE get_emp;
               done_fetch := 1;   -- indicate all done
               EXIT;
           ELSE
               found := found + 1;  -- count row
           END IF;
       END LOOP;
   END;
END;
/

The following sample program connects to the database, prompts the user for a department number, then calls a PL/SQL procedure named get_employees, which is stored in package calldemo. The procedure declares three PL/SQL tables as OUT formal parameters, then fetches a batch of employee data into the PL/SQL tables. The matching actual parameters are host tables. When the procedure finishes, row values in the PL/SQL tables are automatically assigned to the corresponding elements in the host tables. The program calls the procedure repeatedly, displaying each batch of employee data, until no more data is found.

      *****************************************************************
      * Sample Program 9: Calling a Stored Procedure
      * 
      * This program connects to ORACLE, prompts the user for a
      * department number, then calls a PL/SQL stored procedure named
      * GET_EMPLOYEES, which is stored in package CALLDEMO.  The 
      * procedure declares three PL/SQL tables ast OUT formal
      * parameters, then fetches a batch of employee data into the 
      * PL/SQL tables.  The matching actual parameters are host tables.
      * When the procedure finishes, it automatically assigns all row
      * values in the PL/SQL tables to corresponding elements in the
      * host tables.  The program calls the procedure repeatedly,
      * displaying each batch of employee data, until no more data
      * is found.
      * Use option picx=varchar2 when precompiling this sample program.
      *****************************************************************

       IDENTIFICATION DIVISION.
       PROGRAM-ID. CALL-STORED-PROC.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01  USERNAME         PIC X(15) VARYING.
       01  PASSWD           PIC X(15) VARYING.
       01  DEPT-NUM         PIC S9(9) COMP.
       01  EMP-TABLES.
           05  EMP-NAME     OCCURS 10 TIMES PIC X(10).
           05  JOB-TITLE    OCCURS 10 TIMES PIC X(10).

           05  SALARY       OCCURS 10 TIMES COMP-2.

       01  DONE-FLAG        PIC S9(9) COMP.
       01  TABLE-SIZE       PIC S9(9) COMP VALUE 10.
       01  NUM-RET          PIC S9(9) COMP.
       01  SQLCODE          PIC S9(9) COMP.
           EXEC SQL END DECLARE SECTION END-EXEC.

       01  COUNTER          PIC S9(9) COMP.
       01  DISPLAY-VARIABLES.
           05  D-EMP-NAME   PIC X(10).
           05  D-JOB-TITLE  PIC X(10).

           05  D-SALARY     PIC Z(5)9.

           05  D-DEPT-NUM   PIC 9(2).

           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.

       BEGIN-PGM.
           EXEC SQL WHENEVER SQLERROR DO
               PERFORM SQL-ERROR END-EXEC.

           PERFORM LOGON.
           PERFORM INIT-TABLES VARYING COUNTER FROM 1 BY 1
               UNTIL COUNTER > 10.
           PERFORM GET-DEPT-NUM.
           PERFORM DISPLAY-HEADER.
           MOVE ZERO TO DONE-FLAG.
           MOVE ZERO TO NUM-RET.
           PERFORM FETCH-BATCH UNTIL DONE-FLAG = 1.
           PERFORM LOGOFF.

       INIT-TABLES.
           MOVE SPACE TO EMP-NAME(COUNTER).
           MOVE SPACE TO JOB-TITLE(COUNTER).
           MOVE ZERO TO SALARY(COUNTER).

       GET-DEPT-NUM.
           MOVE ZERO TO DEPT-NUM.
           DISPLAY " ".
           DISPLAY "ENTER DEPARTMENT NUMBER: "
               WITH NO ADVANCING.

           ACCEPT D-DEPT-NUM.

           MOVE D-DEPT-NUM TO DEPT-NUM.

       DISPLAY-HEADER.
           DISPLAY " ".
           DISPLAY "EMPLOYEE    JOB TITLE    SALARY".
           DISPLAY "--------    ---------    ------".

       FETCH-BATCH.
           EXEC SQL EXECUTE
               BEGIN
                   CALLDEMO.GET_EMPLOYEES
                       (:DEPT-NUM, :TABLE-SIZE,
                        :NUM-RET,  :DONE-FLAG,
                        :EMP-NAME, :JOB-TITLE, :SALARY);
               END;
           END-EXEC.
           PERFORM PRINT-ROWS VARYING COUNTER FROM 1 BY 1
               UNTIL COUNTER > NUM-RET.

       PRINT-ROWS.
           MOVE EMP-NAME(COUNTER) TO D-EMP-NAME.
           MOVE JOB-TITLE(COUNTER) TO D-JOB-TITLE.
           MOVE SALARY(COUNTER) TO D-SALARY.
           DISPLAY D-EMP-NAME, "  ",
                   D-JOB-TITLE, "   ",
                   D-SALARY.

       LOGON.
           MOVE "SCOTT" TO USERNAME-ARR.
           MOVE 5 TO USERNAME-LEN.
           MOVE "TIGER" TO PASSWD-ARR.
           MOVE 5 TO PASSWD-LEN.
           EXEC SQL
               CONNECT :USERNAME IDENTIFIED BY :PASSWD
           END-EXEC.
           DISPLAY " ".
           DISPLAY "CONNECTED TO ORACLE AS USER: ", USERNAME-ARR.

       LOGOFF.
           DISPLAY " ".
           DISPLAY "HAVE A GOOD DAY.".
           DISPLAY " ".
           EXEC SQL COMMIT WORK RELEASE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

Remember, the datatype of each actual parameter must be convertible to the datatype of its corresponding formal parameter. Also, before a stored subprogram exits, all OUT formal parameters must be assigned values. Otherwise, the values of corresponding actual parameters are indeterminate.

Remote Access

PL/SQL lets you access remote databases via database links. Typically, database links are established by your DBA and stored in the data dictionary. A database link tells your program where the remote database is located, the path to it, and what username and password to use. In the following example, you use the database link dallas to call the raise_salary procedure:

     EXEC SQL EXECUTE 
        BEGIN 
        raise_salary@dallas(:emp_id, :increase); 
        END; 
     END-EXEC. 

You can create synonyms to provide location transparency for remote subprograms, as the following example shows:

 CREATE PUBLIC SYNONYM raise_salary FOR raise_salary@dallas;

CALL Statement

The concepts presented above for the embedded PL/SQL block holds true for the CALL statement. The CALL embedded SQL statement has the form:

      EXEC SQL 
        CALL [schema.] [package.]stored_proc[@db_link](arg1, ...) 
        [INTO :ret_var [[INDICATOR]:ret_ind]] 
     END-EXEC.

where

schema

the schema containing the procedure

package

the package containing the procedure

stored_proc

is the Java or PL/SQL stored procedure to be called

db_link

is the optional remote database link

arg1...

is the list of arguments (variables, literals, or expressions) passed,

ret_var

is the optional host variable which receives the result

ind_var

the optional indicator variable for ret_var.

You can use either SQLCHECK=SYNTAX, or SQLCHECK=SEMANTICS with the CALL statement.

CALL Example

If you have created a PL/SQL function fact (stored in the package mathpkg) that takes an integer as input and returns its factorial in an integer:

     EXEC SQL CREATE OR REPLACE PACKAGE BODY mathpkg as 
       function fact(n IN INTEGER) RETURN INTEGER AS
         BEGIN
           IF (n <= 0) then return 1;
           ELSE return n * fact(n - 1);
           END IF;
         END fact;
       END mathpkge;
     END-EXEC.

then to use fact in a Pro*COBOL application:

...

          01 N      PIC S9(4) COMP.
          01 FACT   PIC S9(9) COMP.
...
     EXEC SQL CALL mathpkge.fact(:N) INTO :FACT END-EXEC.
...

For more information about this statement, see "CALL (Executable Embedded SQL)". For a complete explanation of passing arguments and other issues, see Oracle8i Application Developer's Guide - Fundamentals, "External Routines" chapter.

Getting Information about Stored Subprograms

In Chapter 4, "Datatypes and Host Variables", you learned how to embed OCI calls in your host program. After calling the library routine SQLLDA to set up the LDA, you can use the OCI call ODESSP to get useful information about a stored subprogram. When you call ODESSP, you must pass it a valid LDA and the name of the subprogram. For packaged subprograms, you must also pass the name of the package. ODESSP returns information about each subprogram parameter such as its datatype, size, position, and so on. For details, see Oracle Call Interface Programmer's Guide.

You can also use the procedure describe_procedure in package DBMS_DESCRIBE, which is supplied with Oracle8i. For more information, see the Oracle8i Application Developer's Guide - Fundamentals.

Using Dynamic PL/SQL

Recall that Pro*COBOL treats an entire PL/SQL block like a single SQL statement. Therefore, you can store a PL/SQL block in a string host variable. Then, if the block contains no host variables, you can use dynamic SQL Method 1 to execute the PL/SQL string. Or, if the block contains a known number of host variables, you can use dynamic SQL Method 2 to prepare and execute the PL/SQL string. If the block contains an unknown number of host variables, you must use dynamic SQL Method 4. For more information, refer to Chapter 9, "Oracle Dynamic SQL", Chapter 10, "ANSI Dynamic SQL"and Chapter 11, "Oracle Dynamic SQL: Method 4"."

Subprograms Restriction

In dynamic SQL Method 4, a host table cannot be bound to a PL/SQL procedure with a parameter of type "table."

Cursor Variables

You can use cursor variables in your Pro*COBOL programs to process multi-row queries using static embedded SQL. A cursor variable identifies a cursor reference that is defined and opened on the database server, using PL/SQL. See PL/SQL User's Guide and Reference for complete information about cursor variables.

Like a cursor, a cursor variable points to the current row in the active set of a multi-row query. Cursors differ from cursor variables the way constants differ from variables. While a cursor is static, a cursor variable is dynamic, because it is not tied to a specific query. You can open a cursor variable for any type-compatible query.

You can assign new values to a cursor variable and pass it as a parameter to subprograms, including subprograms stored in a database. This gives you a convenient way to centralize data retrieval.

First, you declare the cursor variable. After declaring the variable, you use these statements to control a cursor variable:

After you declare the cursor variable and allocate memory for it, you must pass it as an input host variable (bind variable) to PL/SQL, OPEN it FOR a multi-row query on the server side, FETCH from it on the client side, then CLOSE it on either side.

The advantages of cursor variables are

Declaring a Cursor Variable

You declare a Pro*COBOL cursor variable using the SQL-CURSOR pseudotype. For example:

 WORKING-STORAGE SECTION.
     ...
     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
         ...
         01  CUR-VAR  SQL-CURSOR.
         ...
     EXEC SQL END DECLARE SECTION END-EXEC.

A SQL-CURSOR variable is implemented as a COBOL group item in the code that Pro*COBOL generates. A cursor variable is just like any other Pro*COBOL host variable.

Allocating a Cursor Variable

Before you can OPEN or FETCH from a cursor variable, you must initialize it using the Pro*COBOL ALLOCATE command. For example, to initialize the cursor variable CUR-VAR that was declared in the previous section, write the following statement:

     EXEC SQL ALLOCATE :CUR-VAR END-EXEC.

Allocating a cursor variable does not require a call to the server, either at precompile time or at runtime.

The AT clause cannot be used in an ALLLOCATE statement.

Warning: Allocating a cursor variable does cause heap memory to be used. For this reason, avoid allocating a cursor variable in a program loop.

Opening a Cursor Variable

You must use an embedded anonymous PL/SQL block to open a cursor variable on the database server. The anonymous PL/SQL block may open the cursor either indirectly by calling a PL/SQL stored procedure that opens the cursor (and defines it in the same statement) or directly from the Pro*COBOL program.

Opening Indirectly through a Stored PL/SQL Procedure

Consider the following PL/SQL package stored in the database:

CREATE PACKAGE demo_cur_pkg AS
    TYPE EmpName IS RECORD (name VARCHAR2(10));
    TYPE cur_type IS REF CURSOR RETURN EmpName;
    PROCEDURE open_emp_cur (
               curs     IN OUT curtype,
               dept_num IN     number);
END;

CREATE PACKAGE BODY demo_cur_pkg AS
    CREATE PROCEDURE open_emp_cur (
               curs     IN OUT curtype,
               dept_num IN     number) IS
    BEGIN
        OPEN curs FOR
            SELECT ename FROM emp
                WHERE deptno = dept_num
                ORDER BY ename ASC;
    END;
END;

After this package has been stored, you can open the cursor curs by calling the open_emp_cur stored procedure from your Pro*COBOL program, and FETCH from the cursor variable EMP-CURSOR in the program. For example:

 WORKING-STORAGE SECTION.
     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
 01  EMP-CURSOR     sql-cursor.
 01  DEPT-NUM      PIC S9(4).
 01  EMP-NAME      PIC X(10) VARYING.
     EXEC SQL END DECLARE SECTION END-EXEC.
     ...

 PROCEDURE DIVISION.
     ...
*    Allocate the cursor variable.
     EXEC SQL 
         ALLOCATE :emp-cursor 
     END-EXEC.
     ...
     MOVE 30 TO dept_num.
*    Open the cursor on the Oracle Server.
     EXEC SQL EXECUTE
         begin
             demo_cur_pkg.open_emp_cur(:emp-cursor, :dept-num);
         END;
     END-EXEC.
     EXEC SQL 
         WHENEVER NOT FOUND DO PERFORM SIGN-OFF
     END-EXEC.
 FETCH-LOOP.
     EXEC SQL 
         FETCH :EMP-CURSOR INTO :EMP-NAME 
     END-EXEC.
     DISPLAY "Employee Name: ",:EMP-NAME.
     GO TO FETCH-LOOP.
     ...
 SIGN-OFF.
     ...

Opening Directly from Your Pro*COBOL Application

To open a cursor using a PL/SQL anonymous block in a Pro*COBOL program, define the cursor in the anonymous block. Consider the following example:

 PROCEDURE DIVISION.
     ...
     EXEC SQL EXECUTE
         begin
             OPEN :EMP-CURSOR FOR SELECT ename FROM emp
                 WHERE deptno = :DEPT-NUM;
         end;
     END-EXEC.
     ...

Fetching from a Cursor Variable

After opening a cursor variable for a multi-row query, you use the FETCH statement to retrieve rows from the active set one at a time. The syntax follows:

     EXEC SQL FETCH cursor_variable_name 
         INTO {record_name | variable_name[, variable_name, ...]} 
     END-EXEC.

Each column value returned by the cursor variable is assigned to a corresponding field or variable in the INTO clause, providing their datatypes are compatible.

The FETCH statement must be executed on the client side. In the following example, you fetch rows into a host record named EMP-REC:

* -- exit loop when done fetching
     EXEC SQL
         WHENEVER NOT FOUND DO PERFORM NO-MORE
     END-EXEC.
     PERFORM
* -- fetch row into record
     EXEC SQL FETCH :EMP-CUR INTO :EMP-REC END-EXEC 
* -- test for transfer out of loop
     ...
* -- process the data
     ...
     END-PERFORM.
     ...
 NO-MORE.
     ...

Use the embedded SQL FETCH .... INTO command to retrieve the rows selected when you opened the cursor variable. For example:

 EXEC SQL
     FETCH :EMP-CURSOR INTO :EMP-INFO:EMP-INFO-IND
 END-EXEC.

Before you can FETCH from a cursor variable, the variable must be initialized and opened. You cannot FETCH from an unopened cursor variable.

Closing a Cursor Variable

Use the embedded SQL CLOSE statement to close a cursor variable, at which point its active set becomes undefined. The syntax follows:

     EXEC SQL CLOSE cursor_variable_name END-EXEC.

The CLOSE statement can be executed on the client side or the server side. In the following example, when the last row is processed, you close the cursor variable CUR-VAR:

 WORKING-STORAGE SECTION.
     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
*    Declare the cursor variable.
         01  CUR-VAR       SQL-CURSOR.
         ...
     EXEC SQL END DECLARE SECTION END-EXEC.

 PROCEDURE DIVISION.
*    Allocate and open the cursor variable, then
*    Fetch one or more rows.
     ...
*    Close the cursor variable.
     EXEC SQL
         CLOSE :CUR-VAR
     END-EXEC.

Freeing a Cursor Variable

To free memory allocated for the cursor variable, CUR-VAR,use the FREE statement after the CLOSE:

*    Free the cursor variable memory.
     EXEC SQL 
         FREE :CUR-VAr 
     END-EXEC.

Restrictions on Cursor Variables

The following restrictions apply to the use of cursor variables:

Error Conditions

Do not perform any of the following operations:

These operations on cursor variables result in errors.

Sample Program 11: Cursor Variables

The following sample programs -- a SQL script (SAMPLE11.sql) and a Pro*COBOL program (SAMPLE11.pco) -- demonstrate how you can use cursor variables in Pro*COBOL.

SAMPLE11.SQL

Following is the PL/SQL source code for a creating a package that declares and opens a cursor variable:

CONNECT SCOTT/TIGER
CREATE OR REPLACE PACKAGE emp_demo_pkg AS
    TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
    PROCEDURE open_cur (
        cursor   IN OUT emp_cur_type,
        dept_num IN     number);
END emp_demo_pkg;
/  
CREATE OR REPLACE PACKAGE BODY emp_demo_pkg AS

    PROCEDURE open_cur (
        cursor   IN OUT emp_cur_type, 
        dept_num IN     number) IS
    BEGIN 
        OPEN cursor FOR SELECT * FROM emp
        WHERE deptno = dept_num
        ORDER BY ename ASC;
    END;
END emp_demo_pkg;
/

SAMPLE11.PCO

Following is a Pro*COBOL sample program, SAMPLE11.PCO, that uses the cursor variable declared in the SAMPLE11.sql example to fetch employee names, salaries, and commissions from the EMP table:

      *****************************************************************
      * Sample Program 11:  Cursor Variable Operations                *
      *                                                               *
      * This program logs on to ORACLE, allocates and opens a cursor  *
      * variable fetches the names, salaries, and commissions of all  *
      * salespeople, displays the results, then closes the cursor.    *
      *****************************************************************
                                          
       IDENTIFICATION DIVISION.
       PROGRAM-ID. CURSOR-VARIABLES.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01  USERNAME          PIC X(15) VARYING.
       01  PASSWD            PIC X(15) VARYING.
       01  HOST              PIC X(15) VARYING.
       01  EMP-CUR           SQL-CURSOR.
       01  EMP-INFO.
           05  EMP-NUM       PIC S9(4) COMP.
           05  EMP-NAM       PIC X(10) VARYING.
           05  EMP-JOB       PIC X(10) VARYING.
           05  EMP-MGR       PIC S9(4) COMP.
           05  EMP-DAT       PIC X(10) VARYING.
           05  EMP-SAL       PIC S9(6)V99 
                               DISPLAY SIGN LEADING SEPARATE.
           05  EMP-COM       PIC S9(6)V99 
                               DISPLAY SIGN LEADING SEPARATE.
           05  EMP-DEP       PIC S9(4) COMP.
       01  EMP-INFO-IND.
           05  EMP-NUM-IND   PIC S9(4) COMP.
           05  EMP-NAM-IND   PIC S9(4) COMP.
           05  EMP-JOB-IND   PIC S9(4) COMP.
           05  EMP-MGR-IND   PIC S9(4) COMP.
           05  EMP-DAT-IND   PIC S9(4) COMP.
           05  EMP-SAL-IND   PIC S9(4) COMP.
           05  EMP-COM-IND   PIC S9(4) COMP.
           05  EMP-DEP-IND   PIC S9(4) COMP.
           EXEC SQL END DECLARE SECTION END-EXEC.
      
           EXEC SQL INCLUDE SQLCA END-EXEC.   

       01  DISPLAY-VARIABLES.
           05  D-DEP-NUM     PIC Z(3)9.
           05  D-EMP-NAM     PIC X(10).
           05  D-EMP-SAL     PIC Z(4)9.99.
           05  D-EMP-COM     PIC Z(4)9.99.
           05  D-EMP-DEP     PIC 9(2).


       PROCEDURE DIVISION.
      
       BEGIN-PGM.
           EXEC SQL
               WHENEVER SQLERROR DO PERFORM SQL-ERROR
           END-EXEC.
           PERFORM LOGON.
           EXEC SQL
               ALLOCATE :EMP-CUR
           END-EXEC.
           DISPLAY "Enter department number (0 to exit):  " 
               WITH NO ADVANCING.
           ACCEPT D-EMP-DEP.
           MOVE D-EMP-DEP TO EMP-DEP.
           IF EMP-DEP <= 0
               GO TO SIGN-OFF
           END-IF.
           MOVE EMP-DEP TO D-DEP-NUM.
           EXEC SQL EXECUTE
               BEGIN
                   emp_demo_pkg.open_cur(:EMP-CUR, :EMP-DEP);
               END;
           END-EXEC.
           DISPLAY " ".
           DISPLAY "For department ", D-DEP-NUM, ":".
           DISPLAY " ".
           DISPLAY "EMPLOYEE   SALARY     COMMISSION".
           DISPLAY "---------- ---------- ----------".
                 
       FETCH-LOOP.
           EXEC SQL
               WHENEVER NOT FOUND GOTO CLOSE-UP
           END-EXEC.
           MOVE SPACES TO EMP-NAM-ARR.
           EXEC SQL FETCH :EMP-CUR
               INTO :EMP-NUM:EMP-NUM-IND,
                    :EMP-NAM:EMP-NAM-IND,
                    :EMP-JOB:EMP-JOB-IND,
                    :EMP-MGR:EMP-MGR-IND,
                    :EMP-DAT:EMP-DAT-IND,
                    :EMP-SAL:EMP-SAL-IND,
                    :EMP-COM:EMP-COM-IND,
                    :EMP-DEP:EMP-DEP-IND
           END-EXEC.
           MOVE EMP-SAL TO D-EMP-SAL.
           IF EMP-COM-IND = 0
               MOVE EMP-COM TO D-EMP-COM
               DISPLAY EMP-NAM-ARR, "   ", D-EMP-SAL, 
                       "   ", D-EMP-COM
           ELSE
               DISPLAY EMP-NAM-ARR, "   ", D-EMP-SAL, 
                       "        N/A"
           END-IF.
           GO TO FETCH-LOOP. 
      
       LOGON.
           MOVE "SCOTT" TO USERNAME-ARR.
           MOVE 5 TO USERNAME-LEN.
           MOVE "TIGER" TO PASSWD-ARR.
           MOVE 5 TO PASSWD-LEN.
           MOVE "INST1_ALIAS" TO HOST-ARR.
           MOVE 11 TO HOST-LEN.
           EXEC SQL
               CONNECT :USERNAME IDENTIFIED BY :PASSWD
           END-EXEC.
           DISPLAY " ".
           DISPLAY "CONNECTED TO ORACLE AS USER: ", USERNAME-ARR.

       CLOSE-UP.
           EXEC SQL
               CLOSE :EMP-CUR
           END-EXEC.
           EXEC SQL
               FREE :EMP-CUR
           END-EXEC.
       SIGN-OFF.
           DISPLAY " ".
           DISPLAY "HAVE A GOOD DAY.".
           DISPLAY " ".
           EXEC SQL
               COMMIT WORK RELEASE
           END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
               ROLLBACK WORK RELEASE
           END-EXEC.
           STOP RUN.




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index