ORACLE Batching DDL statements within a Execute Immediate

Question

I'm trying to run multiple ddl statements within one Execute Immediate statement. i thought this would be pretty straight forward but it seems i'm mistaken.

The idea is this:

declare v_cnt number; 

begin 

select count(*) into v_cnt from all_tables where table_name='TABLE1' and owner = 'AMS'; 

if v_cnt = 0 then 

execute immediate 'CREATE TABLE TABLE1(VALUE VARCHAR2(50) NOT NULL)  ALTER TABLE TABLE1 ADD (MYVAL2 NVARCHAR2(10))'; 

end if; 

end;

however this results in an error

ORA-00911: invalid character ORA-06512: at line 10

Each of the statements within the batch run fine if i execute them by themselves. and if i take this statement and execute it, it will run fine (with the ; between the 2 statements). If i remove the ; between statements i get a different error about invalid option

the plan is that i'll be able to build a table, export the table schema for this table including all it's alter statements, and then run the batch against another system as part of an install/update process.

So, how do i batch these DDL statements within a single execute immediate? Or is there a better way to do what i'm needing?

I'm a bit of a Oracle newb, i must admit. Thank you all for your patience.

Answer 1

The semicolon is not part of Oracle's SQL syntax. SQL*Plus and other client side tools use semicolon to signal the end of a SQL Statement, but the server doesn't see it.

We can force SQL*Plus to pass the semicolon to the DB:

SQL> set sqlterminator off
SQL> select * from user_tables;
  2  /
select * from user_tables;
                         *
ERROR at line 1:
ORA-00911: invalid character

If i take this statement and execute it, it will run fine (with the ; between the 2 statements) The client tool you are using, breaks it into two calls to the DB.

So, I don't think it is possible to pass multiple statements inside an execute immediate.

I suppose one could call execute immediate with a string containing an anonymous PL/SQL block, with individual calls to execute immediate inside it ... and I don't know what the point of doing that would be. ;)

Answer 2

Why do you need a single EXECUTE IMMEDIATE call? Surely just do it as 2 calls?

Bear in mind that each DDL statement contains an implicit COMMIT, so there's no concurency benefit to doing it as a single call.

Also, why not just set up the table correctly in the first call? You could do...

CREATE TABLE TABLE1(VALUE VARCHAR2(50) NOT NULL, MYVAL2 NVARCHAR2(10))

...instead of needing 2 calls.

Also, have you looked at DBMS_METADATA... it can generate DDL for objects such as tables for you.