Trouble with TRUNCATE in a procedure
I'm trying this in my Oracle SQLplus editor:
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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.