Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, radha .

Asked: April 13, 2016 - 1:02 pm UTC

Last updated: April 15, 2016 - 3:41 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hello Sir,
Why PL/SQL does not support DDL ?

and Connor said...

It does support DDL, eg

begin
execute immediate 'create table T ( x int )';
end;

But more importantly....DDL is a *rare* event in Oracle (unlike some other databases). If you need to run lots of DDL from PL/SQL, I'd be interested in seeing the justification for that.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

source of this question

A reader, April 14, 2016 - 11:23 am UTC

I too have read ddl is /was not possible from plsql.Is it due to
a) without using execute immediate clause
b)was it enabled in oracle 9 onwards ?


Connor McDonald
April 14, 2016 - 12:03 pm UTC

As Connor says, if you want to run DDL in PL/SQL you have to use execute immediate. This is still the case:

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production                 0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

5 rows selected.

SQL>
SQL> select * from t;
select * from t
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> begin
  2    create table t (x int);
  3  end;
  4  /
  create table t (x int);
  *
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge


SQL>
SQL> begin
  2    execute immediate 'create table t (x int)';
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

no rows selected

N.B.

Chuck Jolley, April 14, 2016 - 3:28 pm UTC

An important thing to remember when using ddl like that is that it executes an implicit commit.
If the code would break with a commit at that point, eg it is in the middle of a logical transaction, then it will be broken by ddl too.
Chris Saxon
April 15, 2016 - 3:41 am UTC

Good input, thanks.

Martin Rose, April 19, 2016 - 9:08 am UTC

> An important thing to remember when using ddl like that is that it executes an implicit commit.

If the code would break with a commit at that point, eg it is in the middle of a logical transaction, then it will be broken by ddl too.


Not if you do it in an autonomous transaction. ;-)