Topic
No replies
harbha
harbha
1 Post
ACCEPTED ANSWER

Pinned topic Cursor exception (SQLSTATE=24501) during nested error handling

‏2013-07-17T18:46:42Z |

I am getting error when running following procedure.

Logs indicate that cursor cur_loop_test (cursor is declared using WITH HOLD option) is thorwing error on 2 record's fetch. Error message is:

SQLCODE=-501/SQLSTATE=24501/MSG=SQL0501N The cursor specified in a FETCH statement or CLOSE statement is not open or a cursor variable in a cursor scalar function reference is not open.

Any help is appreciated. Thanks in advance !!!

-----

CREATE PROCEDURE SP_LOOP_TEST2()
P1: BEGIN
   DECLARE vTxnKey       INT;
   DECLARE tmp_msg         VARCHAR(1000)      DEFAULT '';
   DECLARE eop             SMALLINT           DEFAULT 0;
   DECLARE vErrorMsg     VARCHAR(32672);
   DECLARE vSession       VARCHAR(50);
   DECLARE SQLCODE         INTEGER           DEFAULT 0;
   DECLARE SQLSTATE        CHAR(5)           DEFAULT '00000';
   DECLARE vErrorCount   INTEGER           DEFAULT 0;
  
   --
   DECLARE cur_loop_test CURSOR WITH HOLD FOR
      SELECT TXN_KEY FROM TXNS WHERE TXN_KEY IN (100,101,102,103) FOR FETCH ONLY;
   --
   DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
   BEGIN
      --
      GET DIAGNOSTICS EXCEPTION 1 vErrorMsg = MESSAGE_TEXT;
      --
      SET vErrorMsg = 'SQLCODE=' || NVL(TRIM(CHAR(SQLCODE)), '?') || '/SQLSTATE=' || NVL(SQLSTATE, '?') || '/MSG=' || NVL(vErrorMsg, '?');
      --
      ROLLBACK;
      --
      INSERT INTO ATT_LOGS (CREATED_TIMESTAMP, CREATED_BY, SESSION_ID, TXN_NO, QUERY_NAME, LOG_TYPE, COMMENTS)
      VALUES  
      (
         CURRENT TIMESTAMP,
         CURRENT USER,
         vSession,
         NULL,
         'SP_LOOP_TEST2',
         'E',
         'ERROR: ' || vErrorMsg
      );
      --
      COMMIT;
      --
      INSERT INTO ATT_LOGS (CREATED_TIMESTAMP, CREATED_BY, SESSION_ID, TXN_NO, QUERY_NAME, LOG_TYPE, COMMENTS)
      VALUES  
      (
         CURRENT TIMESTAMP,
         CURRENT USER,
         vSession,
         NULL,
         'SP_LOOP_TEST2',
         'I',
         'END SP_LOOP_TEST2'
      );
      --
      COMMIT;
      --
   END;
   --
   SET vSession = TRIM(CURRENT USER) || TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS');
   --
   INSERT INTO ATT_LOGS (CREATED_TIMESTAMP, CREATED_BY, SESSION_ID, TXN_NO, QUERY_NAME, LOG_TYPE, COMMENTS)
   VALUES  
   (
      CURRENT TIMESTAMP,
      CURRENT USER,
      vSession,
      NULL,
      'SP_LOOP_TEST2',
      'I',
      'START SP_LOOP_TEST2'
   );
   --
   COMMIT;
   --
   OPEN cur_loop_test;
   WHILE eop = 0
   DO
      -- Starting block P2 with intension to skip a record if any error occurs
      P2: BEGIN
         --
         DECLARE EXIT HANDLER FOR SQLSTATE '02000' SET eop = 1;
         --
         DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
         BEGIN
            --
            SET vErrorCount = vErrorCount + 1;
            --
            -- If received mroe than 3 errors, exit while loop
            IF (vErrorCount >= 3) THEN
               SET eop = 1;
            END IF;
            --
            GET DIAGNOSTICS EXCEPTION 1 vErrorMsg = MESSAGE_TEXT;
            --
            SET vErrorMsg = 'SQLCODE=' || NVL(TRIM(CHAR(SQLCODE)), '?') || '/SQLSTATE=' || NVL(SQLSTATE, '?') || '/MSG=' || NVL(vErrorMsg, '?');
            --
            ROLLBACK;
            --
            INSERT INTO ATT_LOGS (CREATED_TIMESTAMP, CREATED_BY, SESSION_ID, TXN_NO, QUERY_NAME, LOG_TYPE, COMMENTS)
            VALUES  
            (
               CURRENT TIMESTAMP,
               CURRENT USER,
               vSession,
               NULL,
               'SP_LOOP_TEST2',
               'E',
               'ERROR: ' || vErrorMsg
            );
            --
            COMMIT;
            --
         END;
         --
         FETCH cur_loop_test INTO vTxnKey;
         --
         SET tmp_msg = tmp_msg || TO_CHAR(vTxnKey) || ': ';
         --
         -- Following code to test error handling, proc should skip current records processing up on error.
         SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT = 'TEST ERROR ...';
         --        
         FOR CUR_CAS_TXNS AS
            SELECT CAS_KEY FROM CAS_TXN WHERE TXN_KEY = vTxnKey
         DO
            --
            SET tmp_msg = tmp_msg || TO_CHAR(CUR_CAS_TXNS.CAS_KEY) || ', ';
            --
         END FOR;
         --
         INSERT INTO ATT_LOGS  (CREATED_TIMESTAMP, CREATED_BY, SESSION_ID, TXN_NO, QUERY_NAME, LOG_TYPE, COMMENTS)
         VALUES (CURRENT TIMESTAMP, CURRENT USER, vSession, NULL,'SP_LOOP_TEST2','I',tmp_msg);
         --
         COMMIT;
         --
         SET tmp_msg = '';
         --
      END P2;
      --
   END WHILE;
   --
   CLOSE cur_loop_test;
   --
   INSERT INTO ATT_LOGS (CREATED_TIMESTAMP, CREATED_BY, SESSION_ID, TXN_NO, QUERY_NAME, LOG_TYPE, COMMENTS)
   VALUES  
   (
      CURRENT TIMESTAMP,
      CURRENT USER,
      vSession,
      NULL,
      'SP_LOOP_TEST2',
      'I',
      'END SP_LOOP_TEST2'
   );
   --
   COMMIT;
   --
END P1

Updated on 2013-07-17T18:47:55Z at 2013-07-17T18:47:55Z by harbha