Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Location
    Hyderabad
    Posts
    4

    Unanswered: DB2 z/OS DECLARE GLOBAL TEMPORARY TABLE, error cursor was not declared

    Hello GUYZ,

    I trying to create a simple COBOL-DB2 program which uses GLOBAL TEMPORARY TABLE. What here i am trying to do is, DECLARE a GLOBAL TEMPORARY TABLE, insert a few rows & get those rows from the table and display. I am getting an error like the following

    Code:
    DSNHSMUD LINE 82 COL 19  CURSOR "DEPTCUR" WAS NOT DECLARED  
    DSNHSM3  LINE 108 COL 19  THE FETCH FOR CURSOR "DEPTCUR" IS ASSUMED TO APPLY TO A DYNAMIC ALLOCATE CURSOR STATEMENT
    Following is my COBOL-DB2 program.
    Code:
    000100 IDENTIFICATION DIVISION.
    000200 PROGRAM-ID.  TEMPTB.
    000300*AUTHOR.  SUSHANTH BOBBY.
    000400*DATE-WRITTEN. MARCH 2009.
    000500*REMARKS.  A SAMPLE DB2 TEMPORARY TABLE CURSOR PROGRAM       ***
    000600
    001300 ENVIRONMENT DIVISION.
    001400
    001500 DATA DIVISION.
    001600
    001700****************************************************************
    001800 WORKING-STORAGE SECTION.
    001900****************************************************************
    001910 01 WS-TEMP-VAR.
    001920       05 END-OF-LOOP          PIC X(1).
    001930       05 RECORD-COUNT         PIC 9(7).
    001940
    002000 01 WS-TEMP-DEPT.
    002100         10 WS0-DEPT              PIC S9(4) USAGE COMP.
    002200         10 WS1-DEPT              PIC 9(4).
    002300         10 WS-NAME               PIC X(5).
    002400
    002500*    ***********************************************************
    002600*    SQL CONTROL BLOCK
    002700*    ***********************************************************
    002800     EXEC SQL INCLUDE SQLCA    END-EXEC.
    002900
    002910     EXEC SQL DECLARE DEPTTMP TABLE
    002920     ( DEPT SMALLINT NOT NULL,
    002930       NAME CHAR(5)  NOT NULL
    002940     )
    002950     END-EXEC.
    002990
    003000*   ************************************************************
    003100*    CURSOR DECLARATION
    003200*   ************************************************************
    004400
    004500    EXEC SQL
    004600      DECLARE DEPTCUR CURSOR FOR
    004700      SELECT DEPT ,NAME FROM SESSION.DEPTTMP
    004800    END-EXEC.
    006700
    006800*   ************************************************************
    006900
    007000****************************************************************
    007100 PROCEDURE DIVISION.
    007106
    007110*   EXEC SQL
    007120*        DROP TABLE SESSION.DEPTTMP
    007130*   END-EXEC.
    007140
    007150    EXEC SQL
    007160         DECLARE GLOBAL TEMPORARY TABLE SESSION.DEPTTMP
    007170         (DEPT    SMALLINT NOT NULL
    007180         ,NAME    CHAR(5)  NOT NULL
    007190         )
    007192    END-EXEC.
    007193
    007194    EXEC SQL
    007195        INSERT INTO SESSION.DEPTTMP VALUES(10, 'N1');
    007196        INSERT INTO SESSION.DEPTTMP VALUES(10, 'N2');
    007197        INSERT INTO SESSION.DEPTTMP VALUES(10, 'N3');
    007198        INSERT INTO SESSION.DEPTTMP VALUES(10, 'N4');
    007199        INSERT INTO SESSION.DEPTTMP VALUES(10, 'N5');
    007200        INSERT INTO SESSION.DEPTTMP VALUES(10, 'N6');
    007201
    007202        INSERT INTO SESSION.DEPTTMP VALUES(20, 'N1');
    007203        INSERT INTO SESSION.DEPTTMP VALUES(20, 'N2');
    007204        INSERT INTO SESSION.DEPTTMP VALUES(20, 'N3');
    007205
    007206        INSERT INTO SESSION.DEPTTMP VALUES(30, 'N4');
    007207        INSERT INTO SESSION.DEPTTMP VALUES(30, 'N5');
    007208        INSERT INTO SESSION.DEPTTMP VALUES(30, 'N6');
    007209    END-EXEC.
    007210
    007220    INITIALIZE WS-TEMP-DEPT
    007300*    ***********************************************************
    007400*    OPENING ECURSOR
    007500*    ***********************************************************
    007600     EXEC SQL
    007700       OPEN DEPTCUR
    007800     END-EXEC.
    007900
    008000 0000-START-PROGRAM.
    008100     EXEC SQL
    008200       WHENEVER SQLERROR
    008300       GOTO 9000-DB-ERROR
    008400     END-EXEC.
    008500
    008600     EXEC SQL
    008700       WHENEVER SQLWARNING
    008800       CONTINUE
    008900     END-EXEC.
    009000
    009100     EXEC SQL
    009200       WHENEVER NOT FOUND
    009300       CONTINUE
    009400     END-EXEC.
    009500
    009600
    009700     PERFORM 0100-READ-PARA.
    009800     GO TO 1000-END-PROGRAM.
    009900
    010000 0100-READ-PARA.
    010100        PERFORM UNTIL END-OF-LOOP = 'Y'
    010200           EXEC SQL
    010300            FETCH   DEPTCUR
    010400            INTO    :WS0-DEPT , :WS-NAME
    010500           END-EXEC
    010600          EVALUATE SQLCODE
    010700          WHEN 0
    010800              MOVE WS0-DEPT TO WS1-DEPT
    010900              DISPLAY WS0-DEPT WS1-DEPT
    011000              ADD 1 TO RECORD-COUNT
    011100
    011200            WHEN +100
    011300              DISPLAY 'TOTAL NUMBER OF RECORDS = ' RECORD-COUNT
    011400              MOVE 'Y' TO END-OF-LOOP
    011500
    011600            WHEN OTHER
    011700              DISPLAY 'ERROR IN RETREVING DATA'
    011800              DISPLAY 'SQLCODE = ' SQLCODE
    011900
    012000          END-EVALUATE
    012100        END-PERFORM.
    012200
    012300 1000-END-PROGRAM.
    012400     EXEC SQL
    012500       CLOSE DEPTCUR
    012600     END-EXEC.
    012700     GOBACK.
    012800
    012900 9000-DB-ERROR.
    013000      GO TO 1000-END-PROGRAM.
    013100
    Where am i making the mistake.....

    Thank You in advance,
    Sushanth Bobby

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    Shift all the "EXEC SQL" at least 1 Byte to the right, so the texts start in column 12 through 72

  3. #3
    Join Date
    Nov 2008
    Location
    Hyderabad
    Posts
    4
    Thank You umayer,

    I am getting a error on this, is it not possible to give multiple insert statement inside EXEC SQL statements like this
    Code:
    EXEC SQL                                         
      INSERT INTO SESSION.DEPTTMP VALUES(10, 'N1');  
      INSERT INTO SESSION.DEPTTMP VALUES(10, 'N2');  
      INSERT INTO SESSION.DEPTTMP VALUES(10, 'N3');  
      INSERT INTO SESSION.DEPTTMP VALUES(10, 'N4');  
      INSERT INTO SESSION.DEPTTMP VALUES(10, 'N5');  
      INSERT INTO SESSION.DEPTTMP VALUES(10, 'N6');  
    END-EXEC
    iam getting an error like this
    Code:
    ILLEGAL SYMBOL ";". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <END-OF-STATEMENT>
    When i put a single INSERT statement its working fine.

    Thank You,
    Sushanth Bobby

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    each sql statement must have its own EXEC SQL and END-EXEC.

    You cannot "group" them.

  5. #5
    Join Date
    Nov 2008
    Location
    Hyderabad
    Posts
    4
    Thank You Umayer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •