10

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 ?

6

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.