Return cursor in function in db2

Question

 CREATE OR REPLACE FUNCTION PPP ()
       RETURNS cursor 
  F1: BEGIN ATOMIC
          declare c1 cursor ;

          set c1 = CURSOR FOR select * from aaa ; {get error here}

          RETURN c1 ;
  END

My error is

A.PPP - Deploy started. Create user-defined function returns SQLCODE: -104, SQLSTATE: 42601. A.PPP: 6: An unexpected token "FOR" was found following " set c1 = CURSOR". Expected tokens may include: "<compound_SQL_stmts1>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.19.56 An unexpected token "FOR" was found following " set c1 = CURSOR". Expected tokens may include: "<compound_SQL_stmts1>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.19.56 A.PPP - Deploy failed. A.PPP - Roll back completed successfully.

If i wrire set c1 = ... in Stored Procedure, i don't get error

mao mao · Accepted Answer · 2021-06-14T06:36:13

You get this error because your syntax is not valid.

One of the many restrictions on inline SQL blocks (that is, any compound block that starts withBEGIN ATOMIC ), is that:

"Cursors and condition handlers are not supported in inline SQL PL and therefore neither is the RESIGNAL statement."

See docs.

To get your code to compile , the function should not be inlined, but instead be a compiled block, replace BEGIN ATOMIC by BEGIN.

Compilation is just the first step, you will also need to get the function to execute correctly - other changes may be needed.

Return cursor in function in db2

1 Answers