Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

2 Answers 2

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.

share|improve this answer
    
i didn't get exact reason, just because of auto-commit nature of ddl statement, we can't write it directly ? How does it affect the pl/sql block? I'm not getting it –  jWeaver Mar 23 '13 at 15:57
    
It's nothing to do with that. Read the part of the documentation I quoted. DDL is only executable via execute immediate within PL/SQL blocks. This is a design decision by Otacle. I wrote the part about autocommit as advice to you. –  Phil Mar 23 '13 at 16:22
    
Well, you can also execute DDL through DBMS_SQL.PARSE, but very few people do that anymore. –  Adam Musch Mar 26 '13 at 15:18

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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