Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Posts
    4

    Unanswered: Cursor exception (SQLSTATE=24501) during nested error handling

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you don't take time to properly format your code, why should anyone spend time trying to decipher it?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi harjeet_21,

    rollback closes all cursors even 'with hold' option.
    In the following code below you will get -501 on the 2-nd fetch.
    But if you comment 'rollback' and uncomment 'commit', the code will work.

    Code:
    begin
      declare i int;
      declare c1 cursor with hold for
        select c from table(values 1, 2) t(c);
      open c1;
      fetch c1 into i;
      rollback;
    --commit;
      fetch c1 into i;
      close c1;
    end@
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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