0

I'm trying to ensure when i run a stored procedure it checks if a table exists and if it does it drops it then re-creates the table. Using the following stored proc

CREATE PROCEDURE PROCEDURE1 ()
DYNAMIC RESULT SETS 1
P1: BEGIN
if( exists(
select 1 from syscat.tables where tabschema = 'AELUM' and tabname = 'ROOTNODES'
)) then

drop table AELUM.ROOTNODES ;

CREATE TABLE "AELUM"."ROOTNODES" (
"UID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 20 NO ORDER ),
"NODENAME" VARCHAR(255),
"HASCHILD" INTEGER,
"CHILDTABLE" VARCHAR(255)
)
DATA CAPTURE NONE;

**-->>ALTER TABLE "AELUM"."ROOTNODES" ADD CONSTRAINT "ROOTNODES_PK" PRIMARY KEY ("UID");

end if;

END P1

Now the point labeled above **-->> isn't liked by data studio. I am not sure what is wrong with this statement. please help

Vadim K.
  • 2,370
  • 18
  • 26
ojuang
  • 1
  • 1
  • 2
  • Why not create the PK as part of the CREATE TABLE? – Jonathan Leffler Jan 09 '11 at 17:21
  • Have you set the statement separator to something other than the semicolon? Semicolons have a different meaning in stored procedures and the default of also having them as a statement separator is ambiguous. It's defined by Right-click -> Set Statement Separator. I usually use @ when dealing with stored procs. – Leo Jan 10 '11 at 05:36
  • Thank you. That works, I it the procedure as follows CREATE PROCEDURE PROCEDURE1 () DYNAMIC RESULT SETS 1 P1: BEGIN if( exists( select * from syscat.tables where tabschema = 'AELUM' and tabname = 'ROOTNODES' )) then drop table AELUM.ROOTNODES ; CREATE TABLE "AELUM"."ROOTNODES" ( "UID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 20 NO ORDER ) PRIMARY KEY, "NODENAME" VARCHAR(255), "HASCHILD" INTEGER, "CHILDTABLE" VARCHAR(255) ) DATA CAPTURE NONE; end if; END P1 I shall try changing the statement separator – ojuang Jan 10 '11 at 19:23
  • Can anyone tell me how I can use the ALTER statement in a db2 stored procedure. – ojuang Jan 10 '11 at 20:34

1 Answers1

0

Not sure about DB2, but in Oracle you cannot perform DDL in standard PL/SQL. Instead, you have to put the DDL as a VARCHAR2 and perform an EXECUTE IMMEDIATE statement.

EXECUTE IMMEDIATE 'drop table AELUM.ROOTNODES';
Adam Hawkes
  • 7,218
  • 30
  • 57