Truncating a table in a stored procedure

Question

When I run the following in an Oracle shell it works fine  truncate table table name   But when I try to put it in a stored procedure  CREATE OR REPLACE PROCEDURE test IS BEGIN     truncate table table name  END test      it fails with  ERROR line 3  col 14  ending line 3  ending col 18  Found  table   Expecting       ROW  or       or       or            Why

User · Answer

As well as execute immediate you can also use

DBMS_UTILITY.EXEC_DDL_STATEMENT('TRUNCATE TABLE tablename;');

The statement fails because the stored proc is executing DDL and some instances of DDL could invalidate the stored proc. By using the execute immediate or exec_ddl approaches the DDL is implemented through unparsed code.

When doing this you neeed to look out for the fact that DDL issues an implicit commit both before and after execution.

User · Answer

All DDL statements in Oracle PL SQL should use Execute Immediate before the statement  Hence you should use   execute immediate  truncate table schema tablename

User · Answer

try the below code  execute immediate  truncate table tablename

User · Answer

You should know that it is not possible to directly run a DDL statement like you do for DML from a PL SQL block because PL SQL does not support late binding directly it only support compile time binding which is fine for DML  hence to overcome this type of problem oracle has provided  a dynamic SQL approach which can be used to execute the DDL statements The dynamic sql approach is about parsing and binding of sql string at the runtime  Also you should rememder that DDL statements are by default auto commit hence you should be careful about any of the DDL statement using the dynamic SQL approach incase if you have some DML  which needs to be commited explicitly using TCL  before  executing the DDL in the stored proc function   You can use any of the following dynamic sql approach to execute a DDL statement from a pl sql block    1  Execute immediate   2  DBMS SQL package  3  DBMS UTILITY EXEC DDL STATEMENT  parse string IN VARCHAR2    Hope this answers your question with explanation

[oracle] Truncating a table in a stored procedure

Examples related to oracle

Examples related to stored-procedures

Examples related to plsql

Examples related to ddl