Geek Answers Handbook

ORACLE Grouping DDL statements as part of Execute Immediate

I am trying to run multiple ddl statements in a single Execute Immediate statement. I thought it would be pretty straight forward, but it seems like I'm wrong.

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 leads to an error

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

Each of the statements in the package runs fine if I execute them myself. and if I take this statement and execute it, it will work fine (using two operators). If I remove; between operators I get another error about an invalid option

the plan is that I can build a table, export the table schema for this table, including all of its commands that will change, and then run the package against another system as part of the installation and upgrade process.

, DDL ? , ?

Oracle newb, . .

+5
2

EXECUTE IMMEDIATE? , 2 ?

, DDL COMMIT, .

, ? ...

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

... .

DBMS_METADATA... DDL , .

+2

Oracle SQL. SQL * Plus , SQL-, .

SQL * Plus :

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

, ( ). , , .

, , .

, execute , PL/SQL, ... , .;)

+4

More articles:


All Articles