You cannot have DDL (create, alter, drop, etc) natively coded within a PL/SQL program. It can run
- plsql code
- sql code
so if you need to do something that is DDL, it must be passed over to the sql engine, using dynamic SQL. Here's a simple example:
SQL> create or replace
2 procedure MY_PROC is
3 begin
4 --
5 -- truncate is DDL
6 --
7 truncate table T;
8 end;
9 /
Warning: Procedure created with compilation errors.
SQL> sho err
Errors for PROCEDURE MY_PROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/12 PLS-00103: Encountered the symbol "TABLE" when expecting one of
the following:
:= . ( @ % ;
The symbol ":= was inserted before "TABLE" to continue.
SQL>
SQL> create or replace
2 procedure MY_PROC is
3 begin
4 execute immediate 'truncate table T';
5 end;
6 /
Procedure created.
SQL>
SQL> exec my_proc;
PL/SQL procedure successfully completed.
So dynamic sql is needed for thoses case. But that doesnt mean you should go overboard, because dynamic SQL is only resolved at runtime, eg
SQL> create or replace
2 procedure MY_PROC is
3 begin
4 execute immediate 'this is a piece of nonsense';
5 end;
6 /
Procedure created.
and hence debugging etc is harder.