Pro*COBOL Supplement to the Oracle Precompilers Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Sample Programs


This chapter provides several embedded SQL programs to guide you in writing your own. These programs illustrate the key concepts and features of Pro*COBOL programming and demonstrate techniques that let you take full advantage of SQL's power and flexibility.

Each sample program in this chapter is available online. Table 3 - 1 shows the usual filenames of the sample programs. However, the exact filenames are system-dependent. For specific filenames, see your Oracle system-specific documentation.

Filename Demonstrates...
SAMPLE1.PCO a simple query
SAMPLE2.PCO cursor operations
SAMPLE3.PCO array fetches
SAMPLE4.PCO datatype equivalencing
SAMPLE5.PCO an Oracle Forms user exit
SAMPLE6.PCO dynamic SQL Method 1
SAMPLE7.PCO dynamic SQL Method 2
SAMPLE8.PCO dynamic SQL Method 3
SAMPLE9.PCO calling a stored procedure
Table 3 - 1. Pro*COBOL Sample Programs




Sample Program 1: Simple Query

This program logs on to Oracle, prompts the user for an employee number, queries the database for the employee's name, salary, and commission, then displays the result. The program ends when the user enters a zero employee number.

 IDENTIFICATION DIVISION.
 PROGRAM-ID. QUERY.
 ENVIRONMENT DIVISION.
 DATA DIVISION.
 WORKING-STORAGE SECTION.
     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
         01  USERNAME           PIC X(10) VARYING.
         01  PASSWD             PIC X(10) VARYING.
         01  EMP-REC-VARS.
             05  EMP-NAME       PIC X(10) VARYING.
             05  EMP-NUMBER     PIC S9(4) COMP VALUE ZERO.
             05  SALARY         PIC S9(5)V99 COMP-3 VALUE ZERO.
             05  COMMISSION     PIC S9(5)V99 COMP-3 VALUE ZERO.
             05  COMM-IND       PIC S9(4) COMP VALUE ZERO.
     EXEC SQL END DECLARE SECTION END-EXEC.
     EXEC SQL INCLUDE SQLCA END-EXEC.
         01  DISPLAY-VARIABLES.
             05  D-EMP-NAME     PIC X(10).
             05  D-SALARY       PIC Z(4)9.99.
             05  D-COMMISSION   PIC Z(4)9.99.
         01  D-TOTAL-QUERIED    PIC 9(4) VALUE ZERO.
 PROCEDURE DIVISION.
 BEGIN-PGM.
     EXEC SQL 
         WHENEVER SQLERROR DO PERFORM SQL-ERROR 
     END-EXEC.
     PERFORM LOGON.
 QUERY-LOOP.
     DISPLAY " ".
     DISPLAY "ENTER EMP NUMBER (0 TO QUIT): " WITH NO ADVANCING.
     ACCEPT EMP-NUMBER
     IF (EMP-NUMBER = 0) PERFORM SIGN-OFF.
     MOVE SPACES TO EMP-NAME-ARR.
     EXEC SQL 
         WHENEVER NOT FOUND GOTO NO-EMP
     END-EXEC.
     EXEC SQL
         SELECT ENAME, SAL, COMM 
         INTO :EMP-NAME, :SALARY, :COMMISSION:COMM-IND 
         FROM EMP
         WHERE EMPNO = :EMP-NUMBER
     END-EXEC.
     PERFORM DISPLAY-INFO.
     ADD 1 TO D-TOTAL-QUERIED.
     GO TO QUERY-LOOP. 
 NO-EMP.             
     DISPLAY "NOT A VALID EMPLOYEE NUMBER - TRY AGAIN.".
     GO TO QUERY-LOOP.                  
 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.
     DISPLAY-INFO.             
     DISPLAY " ".             
     DISPLAY "EMPLOYEE    SALARY    COMMISSION".             
     DISPLAY "--------    ------    ----------".              
     MOVE EMP-NAME-ARR TO D-EMP-NAME.             
     MOVE SALARY TO D-SALARY. 	    
     IF COMM-IND = -1                 
         DISPLAY D-EMP-NAME, D-SALARY, "          NULL" 	    
     ELSE                 
         MOVE COMMISSION TO D-COMMISSION                 
         DISPLAY D-EMP-NAME, D-SALARY, "      ", D-COMMISSION
     END-IF.
        
 SIGN-OFF.             
     DISPLAY " ".             
     DISPLAY "TOTAL NUMBER QUERIED WAS ", D-TOTAL-QUERIED, ".".
     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.


Sample Program 2: Cursor Operations

This program logs on to Oracle, declares and opens a cursor, fetches the names, salaries, and commissions of all salespeople, displays the results, then closes the cursor.

 IDENTIFICATION DIVISION.
 PROGRAM-ID. CURSOR-OPS.
 ENVIRONMENT DIVISION.
 DATA DIVISION.
 WORKING-STORAGE SECTION.
     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
         01  USERNAME          PIC X(10) VARYING.
         01  PASSWD            PIC X(10) VARYING.
         01  EMP-REC-VARS.
             05  EMP-NAME      PIC X(10) VARYING.
             05  SALARY        PIC S9(6)V99 
                               DISPLAY SIGN LEADING SEPARATE.
             05  COMMISSION    PIC S9(6)V99 
                               DISPLAY SIGN LEADING SEPARATE.
         EXEC SQL VAR SALARY IS DISPLAY(8,2) END-EXEC.
         EXEC SQL VAR COMMISSION IS DISPLAY(8,2) END-EXEC.
     EXEC SQL END DECLARE SECTION END-EXEC.
     EXEC SQL INCLUDE SQLCA END-EXEC.
         01  DISPLAY-VARIABLES.
             05  D-EMP-NAME    PIC X(10).
             05  D-SALARY      PIC Z(4)9.99.
             05  D-COMMISSION  PIC Z(4)9.99.
 PROCEDURE DIVISION.
 BEGIN-PGM.
     EXEC SQL 
         WHENEVER SQLERROR DO PERFORM SQL-ERROR 
     END-EXEC.
     PERFORM LOGON.
     EXEC SQL 
         DECLARE SALESPEOPLE CURSOR FOR
         SELECT ENAME, SAL, COMM FROM EMP
         WHERE JOB LIKE 'SALES%'
     END-EXEC.
     EXEC SQL 
         OPEN SALESPEOPLE 
     END-EXEC.
     DISPLAY "SALESPERSON   SALARY   COMMISSION".
     DISPLAY "-----------   ------   ----------".
        
 FETCH-LOOP.
     EXEC SQL 
         WHENEVER NOT FOUND DO PERFORM SIGN-OFF 
     END-EXEC.
     EXEC SQL 
         FETCH SALESPEOPLE
         INTO :EMP-NAME, :SALARY, :COMMISSION
     END-EXEC.
     MOVE EMP-NAME-ARR TO D-EMP-NAME.
     MOVE SALARY TO D-SALARY.
     MOVE COMMISSION TO D-COMMISSION.
     DISPLAY D-EMP-NAME, "  ", D-SALARY, "     ", D-COMMISSION.
     MOVE SPACES TO EMP-NAME-ARR.
     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.
     EXEC SQL
         CONNECT :USERNAME IDENTIFIED BY :PASSWD
     END-EXEC.
     DISPLAY " ".
     DISPLAY "CONNECTED TO ORACLE AS USER: ", USERNAME-ARR.
     DISPLAY " ".
 SIGN-OFF.
     EXEC SQL 
         CLOSE SALESPEOPLE 
     END-EXEC.
     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.


Sample Program 3: Fetching in Batches

This program logs on to Oracle, declares and opens a cursor, fetches in batches using host tables, and prints the results using the PRINT-IT paragraph.

 IDENTIFICATION DIVISION.
 PROGRAM-ID. HOST-TABLES.
 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  EMP-REC-TABLES.
             05  EMP-NUMBER    OCCURS 5 TIMES PIC S9(4) COMP.
             05  EMP-NAME      OCCURS 5 TIMES PIC X(10) VARYING.
             05  SALARY        OCCURS 5 TIMES PIC S9(6)V99
                               DISPLAY SIGN LEADING SEPARATE.
     EXEC SQL VAR SALARY IS DISPLAY(8,2) END-EXEC.
     EXEC SQL END DECLARE SECTION END-EXEC.
     EXEC SQL INCLUDE SQLCA END-EXEC.
         01  NUM-RET           PIC S9(9) COMP VALUE ZERO.
         01  PRINT-NUM         PIC S9(9) COMP VALUE ZERO.
         01  COUNTER           PIC S9(9) COMP.
         01  DISPLAY-VARIABLES.
             05  D-EMP-NAME    PIC X(10).
             05  D-EMP-NUMBER  PIC 9(4).
             05  D-SALARY      PIC Z(4)9.99.
 PROCEDURE DIVISION.
 BEGIN-PGM.
     EXEC SQL 
         WHENEVER SQLERROR DO PERFORM SQL-ERROR
     END-EXEC.
     PERFORM LOGON.
     EXEC SQL 
         DECLARE C1 CURSOR FOR
         SELECT EMPNO, SAL, ENAME FROM EMP
     END-EXEC.
     EXEC SQL 
         OPEN C1 
     END-EXEC.
 FETCH-LOOP.
     EXEC SQL 
         WHENEVER NOT FOUND DO PERFORM SIGN-OFF 
     END-EXEC.
     EXEC SQL 
         FETCH C1 INTO :EMP-NUMBER, :SALARY, :EMP-NAME
     END-EXEC.
     SUBTRACT NUM-RET FROM SQLERRD(3) GIVING PRINT-NUM.
     PERFORM PRINT-IT.
     MOVE SQLERRD(3) TO NUM-RET.
     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.
     EXEC SQL
         CONNECT :USERNAME IDENTIFIED BY :PASSWD
     END-EXEC.
     DISPLAY " ".
     DISPLAY "CONNECTED TO ORACLE AS USER: ", USERNAME-ARR.
        
 PRINT-IT.
     DISPLAY " ".
     DISPLAY "EMPLOYEE NUMBER   SALARY   EMPLOYEE NAME".
     DISPLAY "---------------   ------   -------------".
     PERFORM PRINT-ROWS
         VARYING COUNTER FROM 1 BY 1 UNTIL COUNTER > PRINT-NUM.
 PRINT-ROWS.
     MOVE EMP-NUMBER(COUNTER) TO D-EMP-NUMBER.
     MOVE SALARY(COUNTER) TO D-SALARY.
     DISPLAY D-EMP-NUMBER, "            ", D-SALARY, "   ",
         EMP-NAME-ARR IN EMP-NAME(COUNTER).
     MOVE SPACES TO EMP-NAME-ARR IN EMP-NAME(COUNTER).
 SIGN-OFF.
     SUBTRACT NUM-RET FROM SQLERRD(3) GIVING PRINT-NUM.
     IF (PRINT-NUM > 0) PERFORM PRINT-IT.
     EXEC SQL CLOSE C1 END-EXEC.
     EXEC SQL COMMIT WORK RELEASE END-EXEC.
     DISPLAY " ".
     DISPLAY "HAVE A GOOD DAY.".
     DISPLAY " ".
     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.


Sample Program 4: Datatype Equivalencing

After connecting to Oracle, this program creates a database table named IMAGE in the SCOTT account, then simulates the insertion of bitmap images of employee numbers into the table. Datatype equivalencing lets the program use the Oracle external datatype LONG RAW to represent the images. Later, when the user enters an employee number, the number's "bitmap" is selected from the IMAGE table and pseudo-displayed on the terminal screen.

 IDENTIFICATION DIVISION. 
 PROGRAM-ID. DTY-EQUIV. 
 ENVIRONMENT DIVISION. 
 DATA DIVISION. 
 WORKING-STORAGE SECTION. 
     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         01  USERNAME          PIC X(10) VARYING. 
         01  PASSWD            PIC X(10) VARYING. 
         01  EMP-REC-VARS. 
             05  EMP-NUMBER    PIC S9(4) COMP. 
             05  EMP-NAME      PIC X(10) VARYING. 
             05  SALARY        PIC S9(6)V99 
                               DISPLAY SIGN LEADING SEPARATE. 
             05  COMMISSION    PIC S9(6)V99 
                               DISPLAY SIGN LEADING SEPARATE. 
             05  COMM-IND      PIC S9(4) COMP. 
         
     EXEC SQL VAR SALARY IS DISPLAY(8,2) END-EXEC. 
     EXEC SQL VAR COMMISSION IS DISPLAY(8,2) END-EXEC. 
         01  BUFFER-VAR. 
             05  BUFFER        PIC X(8192). 
     EXEC SQL VAR BUFFER IS LONG RAW END-EXEC. 
         01  SELECTION         PIC S9(4) COMP. 
     EXEC SQL END DECLARE SECTION END-EXEC. 
     EXEC SQL INCLUDE SQLCA END-EXEC. 
     01  DISPLAY-VARIABLES. 
         05  D-EMP-NAME    PIC X(10). 
         05  D-SALARY      PIC Z(4)9.99. 
         05  D-COMMISSION  PIC Z(4)9.99. 
     01  REPLY             PIC X(10). 
     01  INDX              PIC S9(9) COMP. 
     01  PRT-QUOT          PIC S9(9) COMP. 
     01  PRT-MOD           PIC S9(9) COMP. 
         
 PROCEDURE DIVISION. 
         
 BEGIN-PGM. 
     EXEC SQL 
         WHENEVER SQLERROR DO PERFORM SQL-ERROR 
     END-EXEC. 
     PERFORM LOGON. 
     DISPLAY "OK TO DROP THE IMAGE TABLE? (Y/N) " 
         WITH NO ADVANCING. 
     ACCEPT REPLY. 
     IF (REPLY NOT = "Y") AND (REPLY NOT = "Y") 
         PERFORM SIGN-OFF. 
     EXEC SQL 
         WHENEVER SQLERROR CONTINUE 
     END-EXEC. 
     EXEC SQL 
         DROP TABLE IMAGE 
     END-EXEC. 
     DISPLAY " ". 
     IF (SQLCODE = 0) DISPLAY 
         "TABLE IMAGE DROPPED - CREATING NEW TABLE." 
     ELSE IF (SQLCODE = -942) DISPLAY
         "TABLE IMAGE DOES NOT EXIST - CREATING NEW TABLE." 
     ELSE PERFORM SQL-ERROR. 
     EXEC SQL 
         WHENEVER SQLERROR DO PERFORM SQL-ERROR 
     END-EXEC. 
     EXEC SQL 
         CREATE TABLE IMAGE 
         (EMPNO NUMBER(4) NOT NULL, BITMAP LONG RAW) 
     END-EXEC. 
     EXEC SQL 
         DECLARE EMPCUR CURSOR FOR 
         SELECT EMPNO, ENAME FROM EMP 
     END-EXEC. 
     EXEC SQL 
         OPEN EMPCUR 
     END-EXEC. 
     DISPLAY " ". 
     DISPLAY "INSERTING BITMAPS INTO IMAGE FOR ALL EMPLOYEES...". 
     DISPLAY " ". 
         
 INSERT-LOOP. 
     EXEC SQL 
         WHENEVER NOT FOUND GOTO NOT-FOUND 
     END-EXEC. 
     EXEC SQL 
         FETCH EMPCUR INTO :EMP-NUMBER, :EMP-NAME 
     END-EXEC. 
     MOVE EMP-NAME-ARR TO D-EMP-NAME. 
     DISPLAY "EMPLOYEE ", D-EMP-NAME WITH NO ADVANCING. 
     PERFORM GET-IMAGE. 
     EXEC SQL 
        INSERT INTO IMAGE VALUES (:EMP-NUMBER, :BUFFER) 
     END-EXEC. 
     DISPLAY " IS DONE!". 
     MOVE SPACES TO EMP-NAME-ARR. 
     GO TO INSERT-LOOP. 
         
 NOT-FOUND. 
     EXEC SQL 
         CLOSE EMPCUR 
     END-EXEC. 
     EXEC SQL 
         COMMIT WORK 
     END-EXEC. 
     DISPLAY " ". 
     DISPLAY "DONE INSERTING BITMAPS.  NEXT, DISPLAY SOME.". 
         
 DISP-LOOP. 
     MOVE 0 TO SELECTION. 
     DISPLAY " ". 
     DISPLAY "ENTER EMP NUMBER (0 TO QUIT): " WITH NO ADVANCING. 
     ACCEPT SELECTION. 
     IF (SELECTION = 0) PERFORM SIGN-OFF. 
     EXEC SQL 
         WHENEVER NOT FOUND GOTO NO-EMP 
     END-EXEC. 
     EXEC SQL 
         SELECT EMP.EMPNO, ENAME, SAL, COMM, BITMAP 
         INTO :EMP-NUMBER, :EMP-NAME, :SALARY, 
              :COMMISSION:COMM-IND, :BUFFER 
         FROM EMP, IMAGE 
         WHERE EMP.EMPNO = :SELECTION AND EMP.EMPNO = IMAGE.EMPNO 
     END-EXEC. 
     DISPLAY " ". 
     PERFORM SHOW-IMAGE. 
     MOVE EMP-NAME-ARR TO D-EMP-NAME. 
     MOVE SALARY TO D-SALARY. 
     MOVE COMMISSION TO D-COMMISSION. 
     DISPLAY "EMPLOYEE ", D-EMP-NAME, " HAS SALARY ", D-SALARY 
         WITH NO ADVANCING. 
     IF COMM-IND = -1 
         DISPLAY " AND NO COMMISSION." 
     ELSE 
         DISPLAY " AND COMMISSION ", D-COMMISSION 
     END-IF. 
     MOVE SPACES TO EMP-NAME-ARR. 
     GO TO DISP-LOOP. 
         
 NO-EMP. 
     DISPLAY "NOT A VALID EMPLOYEE NUMBER - TRY AGAIN.". 
     GO TO DISP-LOOP. 
         
 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. 
     DISPLAY " ". 
         
 GET-IMAGE. 
     PERFORM MOVE-IMAGE 
         VARYING INDX FROM 1 BY 1 UNTIL INDX > 8192. 
         
 MOVE-IMAGE. 
     STRING '*' DELIMITED BY SIZE INTO BUFFER WITH POINTER INDX. 
     DIVIDE 256 INTO INDX GIVING PRT-QUOT REMAINDER PRT-MOD. 
     IF (PRT-MOD = 0) DISPLAY "." WITH NO ADVANCING. 
         
 SHOW-IMAGE. 
     PERFORM VARYING INDX FROM 1 BY 1 UNTIL INDX > 10 
     DISPLAY "               *************************" 
     END-PERFORM. 
     DISPLAY " ". 
         
 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.


Sample Program 5: Oracle Forms User Exit

This user exit concatenates form fields. To call the user exit from a Oracle Forms trigger, use the syntax

<user_exit>('CONCAT <field1>, <field2>, ..., <result_field>'); 

where user_exit is a packaged procedure supplied with Oracle Forms and CONCAT is the name of the user exit. A sample CONCAT form invokes the user exit. For more information about Oracle Forms user exits, see Chapter 11 of the Programmer's Guide to the Oracle Precompilers.

 IDENTIFICATION DIVISION. 
 PROGRAM-ID.  CONCAT. 
 ENVIRONMENT DIVISION. 
 DATA DIVISION. 
 WORKING-STORAGE SECTION. 
     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         01  FIELD-NAME             PIC X(80)  VARYING. 
         01  FIELD-VALUE            PIC X(80)  VARYING. 
         01  RESULT                 PIC X(800) VARYING. 
     EXEC SQL END DECLARE SECTION END-EXEC. 
     EXEC SQL INCLUDE SQLCA END-EXEC. 
         01  EXIT-MESSAGE           PIC X(80). 
         01  EXIT-MESSAGE-LEN       PIC S9(9) COMP. 
         01  RTN-CODE               PIC S9(9) COMP. 
             77  INDX               PIC S9(4) COMP. 
         01  DONE-FLAG              PIC X. 
             88  DONE               VALUE 'Y'. 
         01  PTR                    PIC S9(4) COMP. 
         01  WS-CMD-LINE. 
             05  WS-CMD-LINE-Y      PIC X(80). 
             05  WS-CMD-LINE-X      REDEFINES WS-CMD-LINE-Y 
         01  WS-FIELD-NAME-AREA. 
             05  WS-FIELD-NAME      PIC X(80). 
             05  WS-FIELD-NAME-X    REDEFINES WS-FIELD-NAME  
                                    PIC X OCCURS 80. 
             05  WS-FIELD-NAME-LEN  PIC S9(4) COMP. 
  
 LINKAGE SECTION. 
         01  CMD-LINE               PIC X(80). 
         01  CMD-LINE-LEN           PIC S9(9) COMP. 
         01  ERR-MSG                PIC X(80). 
         01  ERR-MSG-LEN            PIC S9(9) COMP. 
         01  IN-QUERY               PIC S9(9) COMP. 
         01  RETURN-VALUE           PIC S9(9) COMP.  
 
 
 PROCEDURE DIVISION USING CMD-LINE, CMD-LINE-LEN, 
                          ERR-MSG, ERR-MSG-LEN, 
                          IN-QUERY, RETURN-VALUE. 
  
 MAIN. 
     MOVE 1 TO PTR. 
     MOVE SPACE TO RESULT-ARR. 
     MOVE ZERO TO RESULT-LEN. 
     MOVE SPACE TO DONE-FLAG. 
     MOVE 7 TO INDX. 
     MOVE CMD-LINE TO WS-CMD-LINE-Y. 
     PERFORM CMD-LINE-PARSE UNTIL DONE. 
     EXEC SQL 
         WHENEVER SQLERROR GOTO SQL-ERROR 
     END-EXEC. 
     MOVE WS-FIELD-NAME TO FIELD-NAME-ARR. 
     MOVE WS-FIELD-NAME-LEN TO FIELD-NAME-LEN. 
     EXEC IAF 
         PUT :FIELD-NAME VALUES(:RESULT) 
     END-EXEC. 
     MOVE SQL-IAPXIT-SUCCESS TO RTN-CODE.  
     EXIT PROGRAM GIVING RTN-CODE. 
 
  
 CMD-LINE-PARSE. 
     MOVE ZERO TO WS-FIELD-NAME-LEN. 
     MOVE SPACES TO WS-FIELD-NAME. 
     MOVE SPACES TO FIELD-NAME-ARR. 
     MOVE ZERO TO FIELD-NAME-LEN. 
     PERFORM GET-FIELD-NAME 
         UNTIL WS-CMD-LINE-X(INDX) = ',' OR DONE. 
     IF WS-CMD-LINE-X(INDX) = ',' 
     MOVE SPACES TO FIELD-NAME-ARR 
     MOVE WS-FIELD-NAME TO FIELD-NAME-ARR 
     MOVE WS-FIELD-NAME-LEN TO FIELD-NAME-LEN  
     MOVE SPACES TO FIELD-VALUE-ARR 
     EXEC IAF 
         GET :FIELD-NAME INTO :FIELD-VALUE 
     END-EXEC 
         STRING FIELD-VALUE-ARR  
             DELIMITED BY SPACE 
             INTO RESULT-ARR 
             WITH POINTER PTR 
         ADD FIELD-VALUE-LEN TO RESULT-LEN 
         ADD 1 TO INDX. 
 GET-FIELD-NAME. 
     IF WS-CMD-LINE-X(INDX) NOT EQUAL SPACE 
         ADD 1 TO WS-FIELD-NAME-LEN 
         MOVE WS-CMD-LINE-X(INDX) TO  
             WS-FIELD-NAME-X(WS-FIELD-NAME-LEN). 
     ADD 1 TO INDX. 
     IF INDX > CMD-LINE-LEN MOVE 'Y' TO DONE-FLAG. 
  
 SQL-ERROR. 
     EXEC SQL 
         WHENEVER SQLERROR CONTINUE 
     END-EXEC. 
     MOVE SQLERRMC TO EXIT-MESSAGE. 
     MOVE SQLERRML TO EXIT-MESSAGE-LEN. 
     CALL "SQLIEM" USING EXIT-MESSAGE EXIT-MESSAGE-LEN. 
     MOVE SQL-IAPXIT-FAILURE TO RTN-CODE. 
     EXIT PROGRAM.


Sample Program 6: Dynamic SQL Method 1

This program uses dynamic SQL Method 1 to create a table, insert a row, commit the insert, then drop the table.

 IDENTIFICATION DIVISION. 
 PROGRAM-ID.  DYNSQL1. 
 ENVIRONMENT DIVISION. 
 DATA DIVISION. 
 WORKING-STORAGE SECTION. 
  
*    INCLUDE THE ORACLE COMMUNICATIONS AREA, A STRUCTURE 
*    THROUGH WHICH ORACLE MAKES ADDITIONAL RUNTIME STATUS 
*    INFORMATION AVAILABLE TO THE PROGRAM. 
     EXEC SQL INCLUDE SQLCA END-EXEC. 
      
*    INCLUDE THE ORACLE COMMUNICATIONS AREA, A STRUCTURE 
*    THROUGH WHICH ORACLE MAKES ADDITIONAL RUNTIME STATUS 
*    INFORMATION AVAILABLE TO THE PROGRAM. 
     EXEC SQL INCLUDE ORACA END-EXEC. 
      
*    THE OPTION ORACA=YES MUST BE SPECIFIED TO ENABLE USE OF 
*    THE ORACA. 
     EXEC ORACLE OPTION (ORACA=YES) END-EXEC. 
  
*    THE RELEASE_CURSOR=YES OPTION INSTRUCTS PRO*COBOL TO 
*    RELEASE IMPLICIT CURSORS ASSOCIATED WITH EMBEDDED SQL 
*    STATEMENTS.  THIS ENSURES THAT ORACLE DOES NOT KEEP  
*    PARSE LOCKS ON TABLES, SO THAT SUBSEQUENT DATA 
*    MANIPULATION OPERATIONS ON THOSE TABLES DO NOT RESULT   
*    IN PARSE-LOCK ERRORS. 
     EXEC ORACLE OPTION (RELEASE_CURSOR=YES) END-EXEC. 
  
*    ALL HOST VARIABLES USED IN EMBEDDED SQL MUST APPEAR IN 
*    THE DECLARE SECTION. 
     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         01  USERNAME  PIC X(10) VALUE "SCOTT". 
         01  PASSWD    PIC X(10) VALUE "TIGER". 
         01  DYNSTMT   PIC X(80) VARYING. 
     EXEC SQL END DECLARE SECTION END-EXEC. 
 
*    DECLARE VARIABLES NEEDED TO DISPLAY COMPUTATIONALS. 
         01  ORASLNRD  PIC 9(9). 
  
 PROCEDURE DIVISION. 
  
 MAIN. 
*    BRANCH TO PARAGRAPH SQLERROR IF AN ORACLE ERROR OCCURS. 
     EXEC SQL 
         WHENEVER SQLERROR GOTO SQLERROR 
     END-EXEC. 
  
*    SAVE TEXT OF CURRENT SQL STATEMENT IN THE ORACA IF AN 
*    ERROR OCCURS. 
     MOVE 1 TO ORASTXTF. 
  
*    CONNECT TO ORACLE. 
     EXEC SQL 
         CONNECT :USERNAME IDENTIFIED BY :PASSWD 
     END-EXEC. 
     DISPLAY " ". 
     DISPLAY "CONNECTED TO ORACLE.". 
     DISPLAY " ". 
  
*    EXECUTE A STRING LITERAL TO CREATE THE TABLE.  HERE, 
*    YOU GENERALLY USE A STRING VARIABLE INSTEAD OF A  
*    LITERAL, AS IS DONE LATER IN THIS PROGRAM.  BUT, YOU 
*    CAN USE A LITERAL IF YOU WISH. 
     DISPLAY "CREATE TABLE DYN1 (COL1 CHAR(4))". 
     EXEC SQL 
         EXECUTE IMMEDIATE "CREATE TABLE DYN1 (COL1 CHAR(4))" 
     END-EXEC. 
  
*    ASSIGN A SQL STATEMENT TO THE VARYING STRING DYNSTMT. 
*    SET THE -LEN PART TO THE LENGTH OF THE -ARR PART. 
     MOVE "INSERT INTO DYN1 VALUES ('TEST')"  
       TO DYNSTMT-ARR.  
     MOVE 36 TO DYNSTMT-LEN. 
     DISPLAY DYNSTMT-ARR. 
  
*    EXECUTE DYNSTMT TO INSERT A ROW.  THE SQL STATEMENT IS 
*    A STRING VARIABLE WHOSE CONTENTS THE PROGRAM MAY 
*    DETERMINE AT RUN TIME. 
     EXEC SQL 
         EXECUTE IMMEDIATE :DYNSTMT 
     END-EXEC. 
  
*    COMMIT THE INSERT. 
     EXEC SQL 
         COMMIT WORK 
     END-EXEC. 
  
*    CHANGE DYNSTMT AND EXECUTE IT TO DROP THE TABLE. 
     MOVE "DROP TABLE DYN1" TO DYNSTMT-ARR. 
     MOVE 19 TO DYNSTMT-LEN. 
     DISPLAY DYNSTMT-ARR. 
     EXEC SQL 
         EXECUTE IMMEDIATE :DYNSTMT 
     END-EXEC. 
*    COMMIT ANY PENDING CHANGES AND DISCONNECT FROM ORACLE. 
     EXEC SQL 
         COMMIT RELEASE 
     END-EXEC. 
     DISPLAY " ". 
     DISPLAY "HAVE A GOOD DAY!". 
     DISPLAY " ". 
     STOP RUN. 
  
 SQLERROR. 
*    ORACLE ERROR HANDLER.  PRINT DIAGNOSTIC TEXT CONTAINING 
*    ERROR MESSAGE, CURRENT SQL STATEMENT, AND LOCATION OF 
*    ERROR. 
     DISPLAY SQLERRMC. 
     DISPLAY "IN ", ORASTXTC. 
     MOVE ORASLNR TO ORASLNRD. 
     DISPLAY "ON LINE ", ORASLNRD, " OF ", ORASFNMC. 
  
*    DISABLE ORACLE ERROR CHECKING TO AVOID AN INFINITE LOOP 
*    SHOULD ANOTHER ERROR OCCUR WITHIN THIS PARAGRAPH. 
     EXEC SQL 
         WHENEVER SQLERROR CONTINUE 
     END-EXEC. 
  
*    ROLL BACK ANY PENDING CHANGES AND DISCONNECT FROM 
*    ORACLE. 
     EXEC SQL 
         ROLLBACK RELEASE 
     END-EXEC. 
     STOP RUN.


Sample Program 7: Dynamic SQL Method 2

This program uses dynamic SQL Method 2 to insert two rows into the EMP table, then delete them.

 IDENTIFICATION DIVISION.
 PROGRAM-ID.  DYNSQL2.
 ENVIRONMENT DIVISION.
 DATA DIVISION.
 WORKING-STORAGE SECTION.
*    INCLUDE THE SQL COMMUNICATIONS AREA, A STRUCTURE THROUGH 
*    WHICH ORACLE MAKES RUNTIME STATUS INFORMATION (SUCH AS ERROR 
*    CODES, WARNING FLAGS, AND DIAGNOSTIC TEXT) AVAILABLE TO THE 
*    PROGRAM. 
     EXEC SQL INCLUDE SQLCA END-EXEC.
*    INCLUDE THE ORACLE COMMUNICATIONS AREA, A STRUCTURE THROUGH 
*    WHICH ORACLE MAKES ADDITIONAL RUNTIME STATUS INFORMATION 
*    AVAILABLE TO THE PROGRAM.
     EXEC SQL INCLUDE ORACA END-EXEC.
*    THE OPTION ORACA=YES MUST BE SPECIFIED TO ENABLE USE OF
*    THE ORACA.
     EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
*    ALL HOST VARIABLES USED IN EMBEDDED SQL MUST APPEAR IN THE
*    DECLARE SECTION.
     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
         01  USERNAME  PIC X(10) VALUE "SCOTT".
         01  PASSWD    PIC X(10) VALUE "TIGER".
         01  DYNSTMT   PIC X(80) VARYING.
         01  EMPNO     PIC S9(4) COMPUTATIONAL VALUE 1234.
         01  DEPTNO1   PIC S9(4) COMPUTATIONAL VALUE 97.
         01  DEPTNO2   PIC S9(4) COMPUTATIONAL VALUE 99.
     EXEC SQL END DECLARE SECTION END-EXEC.
*    DECLARE VARIABLES NEEDED TO DISPLAY COMPUTATIONALS.
         01  EMPNOD    PIC 9(4).        
         01  DEPTNO1D  PIC 9(2).
         01  DEPTNO2D  PIC 9(2).
         01  ORASLNRD  PIC 9(9).
 PROCEDURE DIVISION.  
 MAIN.  
*    BRANCH TO PARAGRAPH SQLERROR IF AN ORACLE ERROR OCCURS.
     EXEC SQL 
         WHENEVER SQLERROR GOTO SQLERROR 
     END-EXEC.
*    SAVE TEXT OF CURRENT SQL STATEMENT IN THE ORACA IF AN ERROR
*    OCCURS.             
     MOVE 1 TO ORASTXTF.
*    CONNECT TO ORACLE.
     EXEC SQL
         CONNECT :USERNAME IDENTIFIED BY :PASSWD
     END-EXEC.
     DISPLAY " ".
     DISPLAY "CONNECTED TO ORACLE.".
     DISPLAY " ".
*    ASSIGN A SQL STATEMENT TO THE VARYING STRING DYNSTMT.  BOTH 
*    THE ARRAY AND THE LENGTH PARTS MUST BE SET PROPERLY.  NOTE 
*    THAT THE STATEMENT CONTAINS TWO HOST VARIABLE PLACEHOLDERS, 
*    V1 AND V2, FOR WHICH ACTUAL INPUT HOST VARIABLES MUST BE 
*    SUPPLIED AT EXECUTE TIME.
     MOVE "INSERT INTO EMP (EMPNO, DEPTNO) VALUES (:V1, :V2)"
         TO DYNSTMT-ARR.
     MOVE 49 TO DYNSTMT-LEN.
*    DISPLAY THE SQL STATEMENT AND ITS CURRENT INPUT HOST 
*    VARIABLES.
     DISPLAY DYNSTMT-ARR.
     MOVE EMPNO TO EMPNOD.
     MOVE DEPTNO1 TO DEPTNO1D.
     DISPLAY "   V1 = ", EMPNOD, "    V2 = ", DEPTNO1D.
*    THE PREPARE STATEMENT ASSOCIATES A STATEMENT NAME WITH A
*    STRING CONTAINING A SQL STATEMENT.  THE STATEMENT NAME IS A 
*    SQL IDENTIFIER, NOT A HOST VARIABLE, AND THEREFORE DOES NOT 
*    APPEAR IN THE DECLARE SECTION.
*    A SINGLE STATEMENT NAME MAY BE PREPARED MORE THAN ONCE,
*    OPTIONALLY FROM A DIFFERENT STRING VARIABLE.
     EXEC SQL 
         PREPARE S FROM :DYNSTMT 
     END-EXEC.
*    THE EXECUTE STATEMENT EXECUTES A PREPARED SQL STATEMENT
*    USING THE SPECIFIED INPUT HOST VARIABLES, WHICH ARE
*    SUBSTITUTED POSITIONALLY FOR PLACEHOLDERS IN THE PREPARED
*    STATEMENT.  FOR EACH OCCURRENCE OF A PLACEHOLDER IN THE
*    STATEMENT THERE MUST BE A VARIABLE IN THE USING CLAUSE.
*    THAT IS, IF A PLACEHOLDER OCCURS MULTIPLE TIMES IN THE
*    STATEMENT, THE CORRESPONDING VARIABLE MUST APPEAR
*    MULTIPLE TIMES IN THE USING CLAUSE.  THE USING CLAUSE MAY
*    BE OMITTED ONLY IF THE STATEMENT CONTAINS NO PLACEHOLDERS.
*    A SINGLE PREPARED STATEMENT MAY BE EXECUTED MORE THAN ONCE,
*    OPTIONALLY USING DIFFERENT INPUT HOST VARIABLES.
     EXEC SQL 
         EXECUTE S USING :EMPNO, :DEPTNO1 
     END-EXEC.
*    INCREMENT EMPNO AND DISPLAY NEW INPUT HOST VARIABLES.
     ADD 1 TO EMPNO.
     MOVE EMPNO TO EMPNOD.
     MOVE DEPTNO2 TO DEPTNO2D.
     DISPLAY "    V1 = ", EMPNOD, "    V2 = ", DEPTNO2D.
*    REEXECUTE S TO INSERT THE NEW VALUE OF EMPNO AND A
*    DIFFERENT INPUT HOST VARIABLE, DEPTNO2.  A REPREPARE IS NOT
*    NECESSARY.
     EXEC SQL 
         EXECUTE S USING :EMPNO, :DEPTNO2 
     END-EXEC.
*    ASSIGN A NEW VALUE TO DYNSTMT.
     MOVE 
         "DELETE FROM EMP WHERE DEPTNO = :V1 OR DEPTNO = :V2"
         TO DYNSTMT-ARR.
     MOVE 50 TO DYNSTMT-LEN.
*    DISPLAY THE NEW SQL STATEMENT AND ITS CURRENT INPUT HOST
*    VARIABLES.
     DISPLAY DYNSTMT-ARR.
     DISPLAY "    V1 = ", DEPTNO1D, "      V2 = ", DEPTNO2D.
     REPREPARE S FROM THE NEW DYNSTMT.
     EXEC SQL 
         PREPARE S FROM :DYNSTMT 
     END-EXEC.
*    EXECUTE THE NEW S TO DELETE THE TWO ROWS PREVIOUSLY INSERTED.
     EXEC SQL 
         EXECUTE S USING :DEPTNO1, :DEPTNO2 
     END-EXEC.
*    COMMIT ANY PENDING CHANGES AND DISCONNECT FROM ORACLE.
     EXEC SQL 
         COMMIT RELEASE 
     END-EXEC.
     DISPLAY " ".
     DISPLAY "HAVE A GOOD DAY!".
     DISPLAY " ".
     STOP RUN.
 SQLERROR.
*    ORACLE ERROR HANDLER.  PRINT DIAGNOSTIC TEXT CONTAINING ERROR
*    MESSAGE, CURRENT SQL STATEMENT, AND LOCATION OF ERROR.
     DISPLAY SQLERRMC.
     DISPLAY "IN ", ORASTXTC.
     MOVE ORASLNR TO ORASLNRD.
     DISPLAY "ON LINE ", ORASLNRD, " OF ", ORASFNMC.
*    DISABLE ORACLE ERROR CHECKING TO AVOID AN INFINITE LOOP
*    SHOULD ANOTHER ERROR OCCUR WITHIN THIS PARAGRAPH.
     EXEC SQL 
         WHENEVER SQLERROR CONTINUE 
     END-EXEC.
*    ROLL BACK ANY PENDING CHANGES AND DISCONNECT FROM ORACLE.
     EXEC SQL 
         ROLLBACK RELEASE 
     END-EXEC.
     STOP RUN.


Sample Program 8: Dynamic SQL Method 3

This program uses dynamic SQL Method 3 to retrieve the names of all employees in a given department from the EMP table.

 IDENTIFICATION DIVISION. 
 PROGRAM-ID.  DYNSQL3. 
 ENVIRONMENT DIVISION. 
 DATA DIVISION. 
 WORKING-STORAGE SECTION. 
  
*    INCLUDE THE SQL COMMUNICATIONS AREA, A STRUCTURE 
*    THROUGH WHICH ORACLE MAKES RUNTIME STATUS INFORMATION 
*    (SUCH AS ERROR CODES, WARNING FLAGS, AND DIAGNOSTIC 
*    TEXT) AVAILABLE TO THE PROGRAM. 
     EXEC SQL INCLUDE SQLCA END-EXEC. 
  
*    INCLUDE THE ORACLE COMMUNICATIONS AREA, A STRUCTURE 
*    THROUGH WHICH ORACLE MAKES ADDITIONAL RUNTIME STATUS 
*    INFORMATION AVAILABLE TO THE PROGRAM. 
     EXEC SQL INCLUDE ORACA END-EXEC. 
  
*    THE ORACA=YES OPTION MUST BE SPECIFIED TO ENABLE USE OF 
*    THE ORACA. 
     EXEC ORACLE OPTION (ORACA=YES) END-EXEC. 
  
*    ALL HOST VARIABLES USED IN EMBEDDED SQL MUST APPEAR IN 
*    THE DECLARE SECTION. 
     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         01  USERNAME  PIC X(10) VALUE "SCOTT". 
         01  PASSWD    PIC X(10) VALUE "TIGER". 
         01  DYNSTMT   PIC X(80) VARYING. 
         01  ENAME     PIC X(10). 
         01  DEPTNO    PIC S99 COMPUTATIONAL VALUE 10. 
     EXEC SQL END DECLARE SECTION END-EXEC. 
  
*    DECLARE VARIABLES NEEDED TO DISPLAY COMPUTATIONALS. 
         01  DEPTNOD   PIC 9(2). 
         01  ENAMED    PIC X(10). 
         01  SQLERRD3  PIC 9(2). 
         01  ORASLNRD  PIC 9(4). 
  
 PROCEDURE DIVISION. 
 MAIN. 
*    BRANCH TO PARAGRAPH SQLERROR IF AN ORACLE ERROR OCCURS. 
     EXEC SQL 
         WHENEVER SQLERROR GO TO SQLERROR 
     END-EXEC. 
*    SAVE TEXT OF CURRENT SQL STATEMENT IN THE ORACA IF AN 
*    ERROR OCCURS. 
     MOVE 1 TO ORASTXTF. 
  
*    CONNECT TO ORACLE. 
     EXEC SQL 
         CONNECT :USERNAME IDENTIFIED BY :PASSWD 
     END-EXEC. 
     DISPLAY " ". 
     DISPLAY "CONNECTED TO ORACLE.". 
     DISPLAY " ". 
  
*    ASSIGN A SQL QUERY TO THE VARYING STRING DYNSTMT.  BOTH 
*    THE ARRAY AND THE LENGTH PARTS MUST BE SET PROPERLY.  
*    NOTE THAT THE STATEMENT CONTAINS ONE HOST VARIABLE 
*    PLACEHOLDER, V1, FOR WHICH AN ACTUAL INPUT HOST 
*    VARIABLE MUST BE SUPPLIED AT OPEN TIME. 
     MOVE "SELECT ENAME FROM EMP WHERE DEPTNO = :V1" 
         TO DYNSTMT-ARR. 
     MOVE 40 TO DYNSTMT-LEN. 
  
*    DISPLAY THE SQL STATEMENT AND ITS CURRENT INPUT HOST 
*    VARIABLE. 
     DISPLAY DYNSTMT-ARR. 
     MOVE DEPTNO TO DEPTNOD. 
     DISPLAY "    V1 = ", DEPTNOD. 
     DISPLAY " ". 
     DISPLAY "EMPLOYEE". 
     DISPLAY "--------". 
  
*    THE PREPARE STATEMENT ASSOCIATES A STATEMENT NAME WITH 
*    A STRING CONTAINING A SELECT STATEMENT.  THE STATEMENT 
*    NAME, WHICH MUST BE UNIQUE, IS A SQL IDENTIFIER, NOT A 
*    HOST VARIABLE, AND SO DOES NOT APPEAR IN THE DECLARE 
*    SECTION. 
     EXEC SQL 
         PREPARE S FROM :DYNSTMT 
     END-EXEC. 
  
*    THE DECLARE STATEMENT ASSOCIATES A CURSOR WITHAPREPARED 
*    STATEMENT.  THE CURSOR NAME, LIKE THE STATEMENT NAME,  
*    DOES NOT APPEAR IN THE DECLARE SECTION. 
     EXEC SQL 
         DECLARE C CURSOR FOR S 
     END-EXEC. 
  
*    THE OPEN STATEMENT EVALUATES THE ACTIVE SET OF THE  
*    PREPARED QUERY USING THE SPECIFIED INPUT HOST 
*    VARIABLES, WHICH ARE SUBSTITUTED POSITIONALLY FOR 
*    PLACEHOLDERS IN THE PREPARED QUERY.  FOR EACH 
*    OCCURRENCE OF A PLACEHOLDER IN THE STATEMENT THERE MUST 
*    BE A VARIABLE IN THE USING CLAUSE. 
*    THAT IS, IF A PLACEHOLDER OCCURS MULTIPLE TIMES IN THE 
*    STATEMENT, THE CORRESPONDING VARIABLE MUST APPEAR 
*    MULTIPLE TIMES IN THE USING CLAUSE.  THE USING CLAUSE 
*    MAY BE OMITTED ONLY IF THE STATEMENT CONTAINS NO 
*    PLACEHOLDERS. OPEN PLACES THE CURSOR AT THE FIRST ROW 
*    OF THE ACTIVE SET IN PREPARATION FOR A FETCH. 
  
*    A SINGLE DECLARED CURSOR MAY BE OPENED MORE THAN ONCE, 
*    OPTIONALLY USING DIFFERENT INPUT HOST VARIABLES. 
     EXEC SQL 
         OPEN C USING :DEPTNO 
     END-EXEC. 
  
*    BRANCH TO PARAGRAPH NOTFOUND WHEN ALL ROWS HAVE BEEN 
*    RETRIEVED. 
     EXEC SQL 
         WHENEVER NOT FOUND GO TO NOTFOUND 
     END-EXEC. 
  
 GETROWS. 
*    THE FETCH STATEMENT PLACES THE SELECT LIST OF THE 
*    CURRENT ROW INTO THE VARIABLES SPECIFIED BY THE INTO 
*    CLAUSE, THEN ADVANCES THE CURSOR TO THE NEXT ROW.  IF 
*    THERE ARE MORE SELECT-LIST FIELDS THAN OUTPUT HOST 
*    VARIABLES, THE EXTRA FIELDS ARE NOT RETURNED.  
*    SPECIFYING MORE OUTPUT HOST VARIABLES THAN SELECT-LIST 
*    FIELDS RESULTS IN AN ORACLE ERROR. 
     EXEC SQL 
         FETCH C INTO :ENAME 
     END-EXEC. 
     MOVE ENAME TO ENAMED. 
     DISPLAY ENAMED. 
  
*    LOOP UNTIL NOT FOUND CONDITION IS DETECTED. 
     GO TO GETROWS. 
  
 NOTFOUND. 
     MOVE SQLERRD(3) TO SQLERRD3. 
     DISPLAY " ". 
     DISPLAY "QUERY RETURNED ", SQLERRD3, " ROW(S).". 
  
*    THE CLOSE STATEMENT RELEASES RESOURCES ASSOCIATED WITH 
*    THE CURSOR. 
     EXEC SQL 
         CLOSE C 
     END-EXEC. 
  
*    COMMIT ANY PENDING CHANGES AND DISCONNECT FROM ORACLE. 
     EXEC SQL 
         COMMIT RELEASE 
     END-EXEC. 
     DISPLAY " ". 
     DISPLAY "HAVE A GOOD DAY!". 
     DISPLAY " ". 
     STOP RUN. 
 
 SQLERROR. 
*    ORACLE ERROR HANDLER.  PRINT DIAGNOSTIC TEXT CONTAINING 
*    ERROR MESSAGE, CURRENT SQL STATEMENT, AND LOCATION OF 
*    ERROR. 
     DISPLAY SQLERRMC. 
     DISPLAY "IN ", ORASTXTC. 
     MOVE ORASLNR TO ORASLNRD. 
     DISPLAY "ON LINE ", ORASLNRD, " OF ", ORASFNMC. 
  
*    DISABLE ORACLE ERROR CHECKING TO AVOID AN INFINITE LOOP 
*    SHOULD ANOTHER ERROR OCCUR WITHIN THIS PARAGRAPH. 
     EXEC SQL 
         WHENEVER SQLERROR CONTINUE 
     END-EXEC. 
*    RELEASE RESOURCES ASSOCIATED WITH THE CURSOR. 
     EXEC SQL 
         CLOSE C 
     END-EXEC. 
  
*    ROLL BACK ANY PENDING CHANGES AND DISCONNECT FROM 
*    ORACLE. 
     EXEC SQL 
         ROLLBACK RELEASE 
     END-EXEC. 
     STOP RUN.


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 Oracle, 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.

 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. 
 
     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 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.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index