Q

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 editor@searchDatabase.com 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.


This was first published in March 2002

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close