Trouble with TRUNCATE in a procedure

 truncate table table_name

It works, but when I try to put the same instruction in a procedure:

 AS BEGIN truncate table table_name; END;

It doesn't work.

 Line # = 3 Column # = 11 Error Text = PLS-00103: Se ha encontrado el simbolo "=" cuando se esperaba uno de los siguientes: := . ( @ % ;

DDL and commands like TRUNCATE are not allowed in Oracle PL/SQL blocks. However, there is a workaround. Native dynamic SQL with the EXECUTE IMMEDIATE command will do the trick. Here is an example:

 create procedure MyProc as begin execute immediate 'truncate table MyTable'; end;

The SQL is executed as if from a host application. Pretty much anything you can run from a SQL prompt can be run from inside a PL/SQL block this way. Because running dynamic native SQL executes a string containing SQL, PL/SQL suddenly becomes very flexible. Dynamic native SQL in Oracle allows you to combine the benefits of a host language with the benefits of stored procedures. Here's an example of the previous procedure changed to include a parameter for the name of the table name to be truncated:

 create procedure MyProc (pTable in varchar2) as begin execute immediate 'truncate table ' || pTable; end;

For More Information

• What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.