I wish to write a SQL script that will check whether the table/sequence exists or not before create the table/sequence.
I tried Google and get some solution that work for other people but not work for me:
Method 1:
SELECT *
FROM tableA
WHERE EXISTS
(SELECT * from tableB);
This is work with select
statement. Then I try with create table statement:
CREATE TABLE "SMEADM"."JXTEST" (
"ACCOUNTSENTRYID" BIGINT NOT NULL )
IN "DATATBSP"
WHERE EXISTS
(SELECT * from tableB);
This will hit error 42601
.
Method 2:
CREATE TABLE IF NOT EXISTS "SMEADM"."JXTEST" (
"ACCOUNTSENTRYID" BIGINT NOT NULL )
IN "DATATBSP" ;
This also bring me to error 42601
.
Method 3:
begin
declare continue handler for sqlstate '42710' begin end;
execute immediate 'CREATE TABLE "SMEADM"."JXTEST" (
"ACCOUNTSENTRYID" BIGINT NOT NULL )
IN "DATATBSP"';
end
And this also bring me to error 42601
.
Kindly advise.