Home » SQL & PL/SQL » SQL & PL/SQL » Truncating a Table from SP
Truncating a Table from SP [message #36627] Thu, 06 December 2001 18:01 Go to next message
Messages: 7
Registered: October 2001
Junior Member

I've written a Stored Procedure to rollback my previous transactions. In that SP i want to Truncate some 11 tables. But when I tried to do so, i got an error. I understood we cann't use a DDL statement in a Stored Procedure. But I want to do it from there only. Is there any other way to do so?
Need your help on this.

Thanx and Rgds

Re: Truncating a Table from SP [message #36630 is a reply to message #36627] Thu, 06 December 2001 18:47 Go to previous message
Satish Shrikhande
Messages: 167
Registered: October 2001
Senior Member
hello ,

If u r using oracle 8 then you will have to go for dbms_sql package
but in oracle 8i u can do it by
execute immediate
First take all the tables in a cursor
open it in loop
prepare a sql (trucate) staetment
and fire it through execute immediate

create or replace procedure a12 as
cursor c1 is
select table_name from user_objects
for z in c1 loop
execute immediate 'truncate table '||z.table_name;
end loop;

Previous Topic: how do i issue the truncate table tab1 statement within a plsql procedure
Next Topic: Re: New triggers in Oracle 8i what are they
Goto Forum:

Current Time: Thu May 23 18:02:25 CDT 2024