patternMinor
Why can't we write ddl statement directly into the PL/SQL block
Viewed 0 times
whycanthestatementsqlintoblockwriteddldirectly
Problem
Why can't we write ddl statements directly in PL/SQL block, for example when i write
But,
Why second one executed successfully ?
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:
A
When using
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.