Ora-2122 On Exec Sql (Not-At) Open Or Fetch (Windows-specific) (Doc ID 1395413.1)

Last updated on JULY 05, 2017

Applies to:

Precompilers - Version 11.2.0.1 and later
Microsoft Windows x64 (64-bit)

Symptoms

The documentation for EXEC SQL OPEN, FETCH and CLOSE states that they are exceptions for EXEC SQL AT.  However, this is not the case on Windows - AT is needed, otherwise they an ORA-2122 is thrown.

The following code demonstrates the problem:


       IDENTIFICATION DIVISION.
       PROGRAM-ID. CASEW.
      *
       ENVIRONMENT DIVISION.
      *
       DATA DIVISION.
       WORKING-STORAGE SECTION.
      *
       01 SQLCA.
           05  SQLCAID         PIC X(8)         VALUE "SQLCA   ".
           05  SQLCABC         PIC S9(9) COMP-5 VALUE 136.
           05  SQLCODE         PIC S9(9) COMP-5 VALUE 0.
           05  SQLERRM.
               49  SQLERRML    PIC S9(4) COMP-5.
               49  SQLERRMC    PIC X(70).
           05  SQLERRP         PIC X(8).
           05  SQLERRD         PIC S9(9) COMP-5 OCCURS 6 VALUE 0.
           05  SQLWARN.
               10  SQLWARN0    PIC X.
               10  SQLWARN1    PIC X.
               10  SQLWARN2    PIC X.
               10  SQLWARN3    PIC X.
               10  SQLWARN4    PIC X.
               10  SQLWARN5    PIC X.
               10  SQLWARN6    PIC X.
               10  SQLWARN7    PIC X.
               10  SQLWARN8    PIC X.
               10  SQLWARN9    PIC X.
               10  SQLWARN10   PIC X.
               10  SQLWARNA    REDEFINES SQLWARN10 PIC X.
           05  SQLSTATE    PIC X(5).

            EXEC SQL BEGIN DECLARE SECTION END-EXEC.
            

       01  SQLSTATE                    PIC X(5).
       01  DB-CONNECT-STRING           PIC X(256).
      *
       01  DB--DATA-CONNECTION PIC X(10)
           IS EXTERNAL.
           
      *
       01  ORA--USING-STMT PIC X(32000) VARYING.
       01  SQL--USING-STMT REDEFINES ORA--USING-STMT.
           49 L PIC S9(4) COMP-5.
           49 D PIC X(32000).
       01  SQL--USING-LEN PIC S9(4) COMP-5.          
      *
       01 MY-EMPNO  PIC 9(4).
       01 MY-ENAME  PIC X(20).
       
           EXEC SQL END DECLARE SECTION END-EXEC.
      *
       PROCEDURE DIVISION.
      *
       MAIN-PAR.
           MOVE "scott/tiger@entm" TO DB-CONNECT-STRING

           MOVE "DATA" TO DB--DATA-CONNECTION
           EXEC SQL
            AT :DB--DATA-CONNECTION
            CONNECT :DB-CONNECT-STRING      
           END-EXEC
           DISPLAY "After database connection - sqlcode = "
                   SQLCODE


           MOVE SPACE TO D OF SQL--USING-STMT(1:SQL--USING-LEN)
           MOVE 1     TO L OF SQL--USING-STMT
           STRING
             "SELECT "
                 "EMPNO,"
                 "ENAME"
              " FROM "
                 "EMP "
                 DELIMITED BY SIZE
             INTO    D OF SQL--USING-STMT
             POINTER L OF SQL--USING-STMT
           END-STRING
           DISPLAY "Statement text build."
      * Working part of the case    
           EXEC SQL AT :DB--DATA-CONNECTION
              PREPARE  
              SQLSTMT FROM :ORA--USING-STMT
           END-EXEC
           IF SQLCODE OF SQLCA NOT = 0 THEN
             DISPLAY "ERROR - Unable to prepare cursor "
                SQLCODE OF SQLCA
           END-IF
           DISPLAY "Statement prepared."
           EXEC SQL AT :DB--DATA-CONNECTION
             DECLARE USING_CSR CURSOR FOR SQLSTMT
           END-EXEC
           IF SQLCODE OF SQLCA NOT = 0 THEN
             DISPLAY "ERROR - Unable to declare cursor "
                SQLCODE OF SQLCA
           END-IF
           DISPLAY "Statement declared."
           EXEC SQL  AT :DB--DATA-CONNECTION
                OPEN USING_CSR
           END-EXEC
           IF SQLCODE OF SQLCA NOT = 0 THEN
             DISPLAY "ERROR - Unable to open cursor "
                SQLCODE OF SQLCA
           END-IF
           DISPLAY "Cursor opened."
           EXEC SQL  AT :DB--DATA-CONNECTION
                FETCH USING_CSR
                 INTO :MY-EMPNO,
                      :MY-ENAME
           END-EXEC
           IF SQLCODE OF SQLCA NOT = 0 THEN
             DISPLAY "ERROR - Unable to fetch from cursor "
                SQLCODE OF SQLCA
           END-IF
           DISPLAY "Fetch completed."
           DISPLAY "EMPNO[" MY-EMPNO "]".
           DISPLAY "ENAME[" MY-ENAME "]".

           EXEC SQL  AT :DB--DATA-CONNECTION
                CLOSE USING_CSR
           END-EXEC
           IF SQLCODE OF SQLCA NOT = 0 THEN
             DISPLAY "ERROR - Unable to close cursor "
                SQLCODE OF SQLCA
           END-IF
           DISPLAY "Close cursor completed."
           
      * Failing part of the case    
           EXEC SQL AT :DB--DATA-CONNECTION
              PREPARE  
              SQLSTMT2 FROM :ORA--USING-STMT
           END-EXEC
           IF SQLCODE OF SQLCA NOT = 0 THEN
             DISPLAY "ERROR - Unable to prepare cursor2 "
                SQLCODE OF SQLCA
           END-IF
           DISPLAY "Statement prepared."
           EXEC SQL AT :DB--DATA-CONNECTION
             DECLARE USING_CSR2 CURSOR FOR SQLSTMT2
           END-EXEC
           IF SQLCODE OF SQLCA NOT = 0 THEN
             DISPLAY "ERROR - Unable to declare cursor2 "
                SQLCODE OF SQLCA
           END-IF
           DISPLAY "Statement declared."
      * No connection specified [ cursor control section in chapter database concepts 3-5 of oracle precompiler manual ]   
      * No connection specified [ syntax diagram page 482 ]   
           EXEC SQL
                OPEN USING_CSR2
           END-EXEC
           IF SQLCODE OF SQLCA NOT = 0 THEN
             DISPLAY "ERROR - Unable to open cursor2 "
                SQLCODE OF SQLCA
           END-IF
           DISPLAY "Cursor opened."
      * No connection specified [ syntax diagram page 464 ]   
           EXEC SQL
                FETCH USING_CSR2
                 INTO :MY-EMPNO,
                      :MY-ENAME
           END-EXEC
           IF SQLCODE OF SQLCA NOT = 0 THEN
             DISPLAY "ERROR - Unable to fetch from cursor2 "
                SQLCODE OF SQLCA
           END-IF
           DISPLAY "Fetch completed."
           DISPLAY "EMPNO[" MY-EMPNO "]".
           DISPLAY "ENAME[" MY-ENAME "]".
      * No connection specified [ syntax diagram page 440 ]   
           EXEC SQL
                CLOSE USING_CSR2
           END-EXEC
           IF SQLCODE OF SQLCA NOT = 0 THEN
             DISPLAY "ERROR - Unable to close cursor2 "
                SQLCODE OF SQLCA
           END-IF
           DISPLAY "Close cursor completed."
           
           
           DISPLAY "Case completed".           
           EXIT PROGRAM.


Output:


After database connection - sqlcode = +0000000000
Statement text build.
Statement prepared.
Statement declared.
Cursor opened.
Fetch completed.
EMPNO[7369]
ENAME[SMITH               ]
Close cursor completed.
Statement prepared.
Statement declared.
ERROR - Unable to open cursor2 -0000002122
Cursor opened.
ERROR - Unable to fetch from cursor2 -0000002122
Fetch completed.
EMPNO[7369]
ENAME[SMITH               ]
ERROR - Unable to close cursor2 -0000002122
Close cursor completed.
Case completed

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms