Pro*COBOL Supplement to the Oracle Precompilers | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
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.
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.
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.
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.
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.
<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.
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.
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.
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.
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.
![]() ![]() Prev Next |
![]() Copyright © 1997 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |