Unable to Print the Data Via Cursor in DB2

Question

Code :

BEGIN   
  DECLARE EMPID INT DEFAULT 0;
  CALL dbms_output.put_line ('EMPID-' || EMPID);
  DECLARE c1 CURSOR FOR  select EMPLOYEE_ID from EMP.EMPLOYEESDET;
  OPEN c1;
  LOOP
  FETCH c1  INTO EMPID;
  CALL dbms_output.put_line ('Display EMPID-' || EMPID);
  end loop;
  close c1;
END
@

Error:

[Code: -104, SQL State: 42601]  An unexpected token <cursor declaration> was found following.  

Expected tokens may include:  <SQL statement>.. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.28.11

Answer 1

When using an anonymous block with compound SQL (inlined), the statements that are valid are a subset of SQL statements. The documentation states which statements are valid.

Specifically you can only use DECLARE CURSOR in a compound SQL (compiled) block (i.e. inside a routine/stored-procedure).

Instead, you should use the FOR statement in your anonymous block like this:

BEGIN   
  DECLARE EMPID INT DEFAULT 0;
  CALL dbms_output.put_line ('EMPID-' || EMPID );
  FOR ROW AS select EMPLOYEE_ID from EMP.EMPLOYEESDET
  DO
      CALL dbms_output.put_line ('Display EMPID- ' || EMPID);
  end FOR;
END
@

Separately your code should handle the case for EMPID being NULL if that is possible (example, use coalesce(empid, ...).