stored-procedurespredicatedb2-luwdatabase-cursor

DB2 LUW Why does use of a cursor predicate generate an error?


I confirmed the following syntax in the DB2 LUW v11.5 manual (we are running DB2 LUW v11.5.9). And apparently this syntax has been supported at least since v9.7! This is in a stored procedure and, yes, C1 is declared as a cursor.

OPEN C1;
If ( C1 IS OPEN ) Then

So, why does the IF statement generate the following error message?

Error: "C1" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.33.31

Color me confused!


Solution

  • There are 2 different techniques of cursor processing depending on what you declared: cursor or cursor variable.

    IS [NOT] OPEN is applicable to cursor variables only.
    Look at the examples below.

    (1) Processing with a cursor variable

    BEGIN
    
    DECLARE v_int INT;
    DECLARE c1 CURSOR;  -- cursor variable declaration
    
    SET c1 = CURSOR FOR VALUES (1), (2);
    OPEN c1;
    IF (c1 IS OPEN) THEN  -- Not really needed here, just for demo
      L1: LOOP
        FETCH c1 INTO v_int;
        IF (c1 IS NOT FOUND) THEN LEAVE L1; END IF;
        -- do something useful here
        CALL DBMS_OUTPUT.PUT_LINE('v_int: ' || v_int);
      END LOOP L1;
      CLOSE c1;
    END IF;
    
    END
    @
    

    (2) Processing with a cursor (the FOR clause after the CURSOR one in DECLARE)

    BEGIN
    
    DECLARE v_int INT;
    DECLARE SQLSTATE CHAR(5);
    DECLARE c1 CURSOR FOR VALUES (1), (2);  -- cursor declaration
    
    OPEN c1;
    L1: LOOP
        FETCH c1 INTO v_int;
        IF SQLSTATE = '02000' THEN LEAVE L1; END IF;
        -- do something useful here
        CALL DBMS_OUTPUT.PUT_LINE('v_int: ' || v_int);
    END LOOP L1;
    CLOSE c1;
    
    END
    @