Menu
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?
525
The SQL TRUNCATE TABLE command is used to delete complete data from an existing table. You can also use DROP TABLE command to delete complete table but it would remove complete table structure form the database and you would need to re-create this table once again if you wish you store some data.
You can just use DELETE FROM @tableVariable , as described in the accepted answer, to get functionality substantially equivalent to TRUNCATE TABLE (except for the logging - this could certainly be a problem if there were a lot of rows in the variable, or the SQL that created the variable was being run very often).
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.
All DDL statements in Oracle PL/SQL should use Execute Immediate before the statement. Hence you should use:
execute immediate 'truncate table schema.tablename'; 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.
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
