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

ddloracleplsql

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 ?

Best Answer

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.

Related Question