WHY DMLARE NOT ALLOWED IN PROCEDURE

WHY DML ARE NOT ALLOWED IN PROCEDURE

You should know that it is not possible to directly run a DDL statement like you do for DML from a PL/SQL block because PL/SQL does not support late binding directly it only support compile time binding which is fine for DML. hence to overcome this type of problem oracle has provided a dynamic SQL approach which can be used to execute the DDL statements. The dynamic sql approach is about parsing and binding of sql string at the runtime. Also you should remember that DDL statements are by default auto commit hence you should be careful about any of the DDL statement using the dynamic SQL approach incase if you have some DML (which needs to be commit explicitly using TCL) before executing the DDL in the stored Procedure / function.

You can use any of the following dynamic sql approach to execute a DDL statement from a pl/sql block.

Options :

1) Execute immediate
2) DBMS_SQL package
3) DBMS_UTILITY.EXEC_DDL_STATEMENT (parse_string IN VARCHAR2);