DDL statements are not allowed in Procedures (PLSQL BLOCK) and why Dynamic SQL supports DDL


Before execution, oracle checks all access permission, validity, and dependencies of all schema objects like tables, views, indexes, synonyms, stored procs, etc referenced inside a pl/sql block. These things are required to build "DIANA" (Descriptive Intermediate Attributed Notation for Ada). This is a tree-structured intermediate language that is internally used by compilers and other tools. The DIANA is used to compile dependent procedures.

Now DML (Data Manipulation Language) statements do not create/alter/drop any schema objects which means that dependencies are still preserved. But this is not true for DDL (Data Definition Language) as it can create, alter, or drop schema objects. And as a result, it can change the object dependencies. So in this case it is not possible to create DIANA. This is the reason oracle does not allow DDL statements directly in PL/SQL block. But we can still include DDL in PL/SQL block using Dynamic query. As in this case, the actual query is not known until run-time, we can include the DDL statement.

Let's try to understand the concept with examples:

Lets first create a simple table ‘tab1’ which contains only one row for simplicity.

create table tab1(first_name varchar2(20),age number(2));

insert into tab1 values('Rajeev',30);


Table created.
1 row(s) inserted.


Below store procedure will fetch tab1 table data through a static 'select into' query

create or replace procedure p_static_fetch
as
fname varchar2(20);
begin
select first_name into fname from tab1;
dbms_output.put_line('first_name:'||fname);
end;
Procedure created.

Lets check it's status,
select object_name,object_type,status from all_objects where object_name=upper('p_static_fetch')
OBJECT_NAMEOBJECT_TYPESTATUS
P_STATIC_FETCHPROCEDUREVALID

Now if we drop the table tab1, this will make the 'p_static_fetch' invalid as the referenced schema object is no longer exist in the database

drop table tab1;
Table dropped.

select object_name,object_type,status from all_objects where object_name=upper('p_static_fetch')
OBJECT_NAMEOBJECT_TYPESTATUS
P_STATIC_FETCHPROCEDUREINVALID

Now let's create the same 'p_static_fetch' procedure but with the help of dynamic query and we will call it ‘p_dynamic_fetch’.

create or replace procedure p_dynamic_fetch
as
fname varchar2(20);
begin
execute immediate 'select first_name from tab1' into fname;
dbms_output.put_line('first_name:'||fname);
end;


Procedure created.

Now if we check the status of this procedure we find it as ‘valid’ because this time we do not know the actual SQL until we are running it.

select object_name,object_type,status from all_objects where object_name=upper('p_dynamic_fetch')
OBJECT_NAMEOBJECT_TYPESTATUS
P_DYNAMIC_FETCHPROCEDUREVALID

But if we try to execute the procedure we will face the below error as referenced table does not exist.

ORA-00942: table or view does not exist ORA-06512: at "SQL_FPAQWUJAWJWDDAGFTHQYFXMHK.P_DYNAMIC_FETCH", line 6 ORA-06512: at line 1 ORA-06512: at "SYS.DBMS_SQL", line 1721

So from the above example we can understand that with the help of dynamic query basically we are hiding our SQL query at the time of compilation and that is the reason oracle allows DDL statement as apart of Dynamic SQL.