Link to home
Start Free TrialLog in
Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America

asked on

Truncate tables' data - multiple tables - Stored Procedure

How do I create a stored procedure to remove the data from multiple tables within an Oracle database?

New to stored procedures?

K
Avatar of johnsone
johnsone
Flag of United States of America image

A stored procedure does not directly use a truncate command, you have to use it within an EXECUTE IMMEDIATE statement like this:

create or replace procedure t as
begin
    EXECUTE IMMEDIATE 'truncate table abc';
end;
/

Be aware that if the table has constraints that reference it you cannot use a truncate statement.  You have to either use a delete or disable the constraint and then do the truncate.
also please add exception handling section to the code to trap/log errors if any during the code execution.

create or replace procedure t as
begin
    EXECUTE IMMEDIATE 'truncate table abc';
exception
  when others then
   dbms_output.put_line('in procedure t error - sqlcode:' || sqlcode );
   dbms_output.put_line('in procedure t error - sqlerrm:' || sqlerrm );
  -- alternatively you can insert into log/errors table in this section.
end;
/

and to execure the procedure,

exec t
Avatar of Karen Schaefer

ASKER

I am looking for a stored procedure that will truncate or delete teh records in all tables except for a few designated tables.  I am new to this so any help getting me started would be appreciated.

Thanks.
How do you determine which tables are to be include or excluded?  The examples given are just that.  They should be enough to get you started.  However, we don't know the structure of these tables and what constraints exist if any.  Also, truncate or delete are 2 very different ways of getting this done.
this what I have so far and I am getting the following error msg.
declare tablecursor cursor for
select name from sysobjects
where xtype ='U'
and name not like '<ACTION_TABLE> and name not like '<ACTIVE_AIRPLANES> and name not like '<TA_AIRPLANEINFO_BAK>'

DECLARE @table_name varchar(255)
DECLARE @sql as varchar(4000)

OPEN tablecursor

FETCH NEXT FROM tablecursor into @table_name
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        set @sql = 'TRUNCATE TABLE ' + @table_name
        exec sp_executesql @sql
    END
    FETCH NEXT FROM tablecursor INTO @table_name
END

Open in new window



Error starting at line 1 in command:
declare tablecursor cursor for
select name from sysobjects
where xtype ='U'
and name not like '<ACTION_TABLE> and name not like '<ACTIVE_AIRPLANES> and name not like '<TA_AIRPLANEINFO_BAK>'

DECLARE @table_name varchar(255)
DECLARE @sql as varchar(4000)

OPEN tablecursor

FETCH NEXT FROM tablecursor into @table_name
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        set @sql = 'TRUNCATE TABLE ' + @table_name
        exec sp_executesql @sql
    END
    FETCH NEXT FROM tablecursor INTO @table_name
END
Error report:
ORA-06550: line 1, column 28:
PLS-00103: Encountered the symbol "FOR" when expecting one of the following:

   := . ( @ % ; not null range default character
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
I also already have a table that contains the list of tables I need to remove the data from - maybe I should use this as the source of the list of tables - table "Action Table"

What do you think?

K
I'm confused here.  This question is posted in an Oracle zone, but the code you have posted is clearly SQL Server code.  Which one are we using here?  I see that the error messages are Oracle, but I'm very confused by the code.

I'm not that familiar with SQL Server TSQL code, but this may be the Oracle equivalent of what you have.

 begin

  for c1rec in (select table_name from dba_tables where owner = '<own>' and table_name not in ('ACTION_TABLE', 'ACTIVE_AIRPLANES', 'TA_AIRPLANEINFO_BAK') loop
   execute sql 'truncate table ' || c1rec.table_name;
  end loop;
end;
/

Open in new window


You are going to have to substitute in the correct owner for the objects where I have <own> in that code.

There is no error checking and if there are any referential integrity constraints, the truncate statements will fail.
ASKER CERTIFIED SOLUTION
Avatar of Karen Schaefer
Karen Schaefer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks for the input see last posting for final solution.