Truncate 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's not with

 ERROR line 3, col 14, ending_line 3, ending_col 18, Found 'table', Expecting: @ ROW or ( or . or ; := 

What for?

Answer 1

All DDL statements in Oracle PL / SQL must use Execute Immediate before the statement. Therefore, you should use:

 execute immediate 'truncate table schema.tablename'; 

Answer 2

As well as immediate execution, you can also use

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

The statement does not work because the stored process is executing a DDL, and some instances of DDL may invalidate the stored process. When using immediate execution or exec_ddl, DDL approaches are implemented through unparsed code.

In doing so, you must ensure that DDL issues implicit commit both before and after execution.

Answer 3

try the code below

 execute immediate 'truncate table tablename' ; 

Answer 4

You should know that it is not possible to directly run the DDL statement, as you do for DML, from a PL / SQL block, because PL / SQL does not support late binding directly, it only supports compile time binding, which is great for DML. therefore, to overcome this type of problem, the oracle provided a dynamic SQL approach that can be used to execute DDL statements. The sql dynamic approach refers to parsing and binding sql strings at runtime. You should also remember that DDL statements are automatically committed by default, so you should be careful about any of the DDL statements using the dynamic SQL approach if you have some DML (which must be executed explicitly using TCL) before executing the DDL in the stored proc / function procedure.

You can use any of the following dynamic SQL approaches to execute the DDL statement from the pl / sql block.

1) Perform an immediate

2) DBMS_SQL package

3) DBMS_UTILITY.EXEC_DDL_STATEMENT (parse_string IN VARCHAR2);

Hope this answers your explanation question.