HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whycanthestatementsqlintoblockwriteddldirectly

Problem

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 ?

Solution

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.

Context

StackExchange Database Administrators Q#37362, answer score: 7

Revisions (0)

No revisions yet.