Why can't we write ddl statement directly into the PL/SQL block

Question

Why can't we write ddl statements directly in PL/SQL block, for example when i write

CREATE OR REPLACE PROCEDURE test IS
BEGIN
    truncate table table_name; // error
END test;
/

But,

CREATE OR REPLACE PROCEDURE test IS
BEGIN
    execute immediate 'truncate table table_name'; // works fine
END test;
/

Why second one executed successfully ?

Answer 1

As it says in the documentation:

Only dynamic SQL can execute the following types of statements within PL/SQL program units:

• Data definition language (DDL) statements such as CREATE, DROP, GRANT, and REVOKE

A TRUNCATE operation is DDL.

When using EXECUTE IMMEDIATE, remember that any DDL operations you execute will implicitly COMMIT the current transaction.

Answer 2

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. Link- https://stackoverflow.com/questions/15232252/why-is-static-ddl-not-allowed-in-pl-sql

Answer 3

DDL inside PL/SQL code is more exception than real need. Parse can be viewed as structure verification, which is lost if your structure changes on execution. Procedures are intended to be parsed again other objects (tables, or other pl/sql code, views etc). Each time depending object changes, it should be recompiled. So, making parsed code of something than change structure can't be verified and as such compiled. Consider case

DROP TABLE T1;

During parse time, table would be found and procedure succesfully compiled but on 1st execution, table is dropped and your code is not valid anymore (next time DROP TABLE would result in error). Similarly, any change to table DDL would create a need to recompile, so losing advantage of code parsing.