Problem solve Get help with specific problems with your technologies, process and projects.

Trouble with TRUNCATE in a procedure

I'm trying this in my Oracle SQLplus editor:

 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.

Dig Deeper on Using Oracle PL-SQL