How to create 2 tables using EXECUTE IMMEDIATE only once?

Question

I have a requirement where I need to create 2 tables inside a PLSQL block. Currently we're using EXECUTE IMMEDIATE. Now the issue is I need to create 2 tables but I can use EXECUTE IMMEDIATE only once.

Are there ways to create tables within PLSQL either by 1) without using EXECUTE IMMEDIATE or 2) create 2 tables inside a single usage of EXECUTE IMMEDIATE

I tried like below. and some trail and error combinations. but didnt help. Expecting like the below.

EXECUTE IMMEDIATE 
'BEGIN
  CREATE TABLE NEW_TABLE1 AS
  SELECT T1.*,
    ''Y'' PROCESS_FLAG
  FROM TABLE1 T1
  WHERE T1.PRIMAR_ID IN ( 300000005137174 ,300000005142005);
  CREATE TABLE NEW_TABLE2 AS
  SELECT T2.*,
    ''Y'' PROCESS_FLAG
  FROM TABLE2 T2
  WHERE T2.CODE       IS NULL
  AND T2.SECONDARY_ID IN
    (SELECT DISTINCT(T_TEMP.PRIMAR_ID)
    FROM NEW_TABLE1 T_TEMP
    WHERE T_TEMP.PROCESS_FLAG = ''Y''
    );
END' ;

I tried by having both create stmts in a single string and execute with one EXECUTE IMMEDIATE. But erroring. Not sure if syntax problem or the try itself is wrong. — Gokulnath, May 15 '19 at 06:56

Gurwinder Singh · Accepted Answer · 2019-05-15T07:40:57.913

I admit it's not very nice but you can run multiple execute immediate inside a plsql block in an execute immediate:

begin
    execute immediate '
        begin
            execute immediate ''create table a(c number)'';
            execute immediate ''create table b(c number)'';
        end;
    ';
end;

EDIT:

As per your comment, you can run the following.

begin
    execute immediate '
        BEGIN 
            execute immediate ''
                CREATE TABLE NEW_TABLE1 AS 
                SELECT T1.*, ''''Y'''' PROCESS_FLAG
                FROM TABLE1 T1 
                WHERE T1.PRIMAR_ID IN ( 300000005137174 ,300000005142005)
            '';

            execute immediate ''
                CREATE TABLE NEW_TABLE2 AS 
                SELECT T2.*, ''''Y'''' PROCESS_FLAG
                FROM TABLE2 T2
                WHERE T2.CODE IS NULL 
                AND T2.SECONDARY_ID IN (
                    SELECT distinct(T_TEMP.PRIMAR_ID)
                    FROM NEW_TABLE1 T_TEMP
                    WHERE T_TEMP.PROCESS_FLAG = ''''Y''''
                )
            ''; 
        END;
    ';
end;

Also, you may need to rethink your problem. Do you really need to create table? Can you not create the tables beforehand and just do simple insertion instead. You can circumvent dynamic SQL.

Hi.. I am looking for something like this.. EXECUTE IMMEDIATE 'BEGIN CREATE TABLE NEW_TABLE1 AS SELECT T1.*, ''Y'' PROCESS_FLAG FROM TABLE1 T1 WHERE T1.PRIMAR_ID IN ( 300000005137174 ,300000005142005); CREATE TABLE NEW_TABLE2 AS SELECT T2.*, ''Y'' PROCESS_FLAG FROM TABLE2 T2 WHERE T2.CODE IS NULL AND T2.SECONDARY_ID IN (SELECT distinct(T_TEMP.PRIMAR_ID) FROM NEW_TABLE1 T_TEMP WHERE T_TEMP.PROCESS_FLAG = ''Y'' ); END' ; — Gokulnath, May 15 '19 at 07:12
please refer below.. my comment as a separate answer. (To have a good formated look of the SQL) — Gokulnath, May 15 '19 at 07:17
@GokulNath - You may need rethink your problem.. Perhaps you dont need to create the table - have them created once.. And have your code do just the insertion — Gurwinder Singh, May 15 '19 at 07:34

How to create 2 tables using EXECUTE IMMEDIATE only once?

1 Answers1

EDIT: