Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,634
    Provided Answers: 1

    Unanswered: Stored procedure error: SQL0501N The cursor specified in a FETCH statement or CLOSE

    Hi,
    on DB2 v11.1 fixpack 2 on Linux I would like to write a stored procedure to validate all of the not valid stored procedures.
    Bellow is simple sample how to reproduce a problem.

    Regards

    Code:
    -- Sample table
    DROP TABLE ADMIN.TAB
    @
    
    -- Sample table
    CREATE TABLE ADMIN.TAB (A INT)
    @
    
    -- Sample stored procedure
    CREATE OR REPLACE PROCEDURE ADMIN.SP
    ()
    SPECIFIC ADMIN.SP
    LANGUAGE SQL
    BEGIN
        DELETE FROM ADMIN.TAB;
    END
    @
    
    -- Check if above procedure is valid, it should be
    SELECT VALID FROM SYSCAT.ROUTINES
    WHERE ROUTINESCHEMA = 'ADMIN' AND ROUTINENAME = 'SP'
    @
    
    -- Create procedure to get all of the procedures on database that are in not valid state and validate them with sysproc.admin_revalidate_db_objects system procedure.
    CREATE OR REPLACE PROCEDURE ADMIN.VALIDATE
    ()
    SPECIFIC ADMIN.VALIDATE
    LANGUAGE SQL
    BEGIN
        FOR
            ROW AS
                    SELECT
                        RTRIM(ROUTINESCHEMA) AS PROC_SCHEMA,
                        ROUTINENAME AS PROC_NAME
                    FROM
                        SYSCAT.ROUTINES
                    WHERE
                        VALID = 'N' AND
                        -- Below two conditions, just to simulate our stored procedure in case if tested on database with other non valid procedures
                        -- Below two conditions should be deleted after testing simple sample
                        ROUTINESCHEMA = 'ADMIN' AND
                        ROUTINENAME = 'SP'
            DO
                  CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('PROCEDURE', ROW.PROC_SCHEMA, ROW.PROC_NAME);
                  COMMIT;
        END FOR;
    END
    @
    
    -- Simulate: procedure admin.sp becomes not valid after below command
    DROP TABLE ADMIN.TAB
    @
    
    -- After above command procedure ADMIN.SP is not valid anymore
    SELECT VALID FROM SYSCAT.ROUTINES
    WHERE ROUTINESCHEMA = 'ADMIN' AND ROUTINENAME = 'SP'
    @
    
    -- Recreate table to admin.sp has all of the conditions to become valid
    CREATE TABLE ADMIN.TAB (A INT)
    @
    
    -- Call procedure to validate all of the not valid procedures on database
    CALL ADMIN.VALIDATE()
    @
    The output of above commands:
    Code:
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    
    
    VALID
    -----
    Y
    
      1 record(s) selected.
    
    
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    
    
    VALID
    -----
    N
    
      1 record(s) selected.
    
    
    DB20000I  The SQL command completed successfully.
    
    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.
    SQLSTATE=24501
    The last command returns error "SQL0501N The cursor specified in a FETCH statement or CLOSE statement is not open..."
    How to fix a admin.validate stored procedure?

    Regards
    Last edited by grofaty; 09-25-17 at 09:09.

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    282
    Provided Answers: 41
    Hi,

    Try this:
    Code:
    ...
        FOR
            c1 cursor with hold for
            ROW AS
    ...
    Regards,
    Mark.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,634
    Provided Answers: 1
    Hi,
    actually correct code is:
    Code:
    ...
        FOR
            ROW AS
             c1 cursor with hold for 
                SELECT
    ...
    but your help was extremely helpful.

    Thanks a lot

Posting Permissions

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