CODEWITHSUNDEEP
db2handlersql-pl
ammianus
ammianus

Reputation: 488

In DB2 PL/SQL anonymous block declare local variables and declare continue handler results in error?

I am trying to make a simple script that handles errors from statements that are ok to fail gracefully. This is for DB2 v10.5 (Linux)

Example create a table and handle if table already exists

begin 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END; 
    EXECUTE IMMEDIATE 'CREATE TABLE T(c1 INT)'; 
end
/

This works ok

Now I had a slightly more complex block that originally used local variables and I was trying to added the DECLARE CONTINUE HANDLER and I could not get it to compile. Simplifying the problem I found that by adding a declare statement before the begin fails if I have the Handler line (note I am not using the localVariable here, but it fails just by adding that statement in. If I run same statement without DECLARE CONTINUE HANDLER, it also runs ok

declare 
  localVariable  INT;
begin 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END; 
    EXECUTE IMMEDIATE 'CREATE TABLE T(c1 INT)'; 
end
/

Resulting error code

Error report: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=end;end;BEGIN, DRIVER=3.68.61

Upvotes: 0

Views: 3602

Answers (1)

mustaccio
mustaccio

Reputation: 18955

You seem to be mixing up PL/SQL and DB2 SQL PL syntax. A PL/SQL block has the DECLARE section before BEGIN...END, as in your second example. If you use that, all your declaration must be in the DECLARE section.

When you use SQL PL compound SQL syntax, there is no separate DECLARE section, but instead all DECLARE statements must appear within the BEGIN...END block before any executable statements. Note that the order of DECLARE statements is also important. For example, condition handler declarations must appear after the variable declarations. You can find the exact compound SQL syntax in the manual.

Upvotes: 1

Related Questions