Why is static ddl not allowed in PL/SQL?

Question

In an Oracle PL/SQL block, why is dynamic sql allowed

begin
    execute immediate 'drop table table_name';
end;

but static is not?

begin
    drop table table_name;
end;

I hope the answer is more insightful than "because that's how the language works".

Answer 1

The answer is PL/SQL does not support dynamic polymorphism. it only supports static polymorphism because

All PL/SQL generats a "DIANA" -> Descriptive Intermediate Attributed Notation for Ada , a tree-structured intermediate language. DIANA is used internally by compilers.

At compile time, PL/SQL source code is translated into system code and generates corresponding DIANA. Now think if there were a DDL statement like create table statement which at the compile time does not exists it will be created after running the program. how would your PL/SQL engine generate a DIANA then ????

The DIANA is plays an important role in PL/SQL to check/validate that the sub program. this is required because as we know that a sub-program can use database objects such as Tables,Views,Synonyms or other stored procs. it could be possible that the the objects may have changed/removed/droped when next time you run the program. For ex : some one might have droped the table, the stored proc or function singnature may have changed.

Thats why generally PL/SQL is used to manipulate the data within database structure, but not to manipulate those structures.

but there are ways to manipulate using dynamic SQL and DBMS_SQL package but theses methodlogy are again should be used cautiously. For example if you are creating a Table you should check first if this table is already exists or not using data dictionary views.

Answer 2

Probably because otherwise some code would be like:

  begin
    create table tmp (n number);
    insert into tmp values (1);
  end;

And we would expect the compiler to know that at time of the insert, the table exists. The compilation of the block would me much more difficult. Here it is a very simple case, but we can easily imagine some conditional branching, and complex blabla.

But, since we need to put the DDL in an execute immediate block, the limitation maybe somehow easier to understand.

Just an idea...