CODEWITHSUNDEEP
cursordb2
Sekhar
Sekhar

Reputation: 343

DB2 error in using the cursor in PL/SQL function

I am declaring a cursor in DB2 PL/SQL function as below:

create function query1(tbname VARCHAR(32), msisdn VARCHAR(32)) 
returns VARCHAR(40) 
LANGUAGE SQL 
READS SQL DATA 
NO EXTERNAL ACTION 
DETERMINISTIC 
begin atomic 
    DECLARE vsql varchar(2000); 
    DECLARE dt_UTC   date; 
    DECLARE C1 CURSOR FOR select productid from Subscription_000 where msisdn= 123456; 
    SET vsql = 'select productid from Subscription_000 where msisdn= 123456'; 
OPEN C1; 


    return '123'; 
end 
@

I saved the above contents to k2.sql and When I try to compile this function using the command: db2 -td@ -f k2.sql I am getting the below error:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "FOR" was found following "DECLARE C1 CURSOR". Expected tokens may include: "". LINE NUMBER=10. SQLSTATE=42601

Any suggestions on What is the problem here...?

Upvotes: 1

Views: 2311

Answers (1)

dan1111
dan1111

Reputation: 6566

This was a tricky one, but after some testing I figured out the problem: you can't declare a cursor within an atomic compound statement. Replace BEGIN ATOMIC with just BEGIN.

I wish I could point you to the list of allowed statements for ATOMIC, but it is buried in the manual somewhere and I can't find it at the moment.

(This is a classic case of DB2's terrible error reporting. Shouldn't it be able to recognize a disallowed statement and tell you that?)

Edit: this is one of the problems, but there is also some other problem, which I am not having, so I can't debug it.

Upvotes: 3

Related Questions