All Forums UDA
jerboa 10 posts Joined 04/12
28 Apr 2012
stored procedure

Hi everyone!

I want to write SP for removing tables. The first need to find these tables (they need satisfy some conditions)  and  the second - remove it. I don't know how pass the list of tables to variables for removing theirs. And some errors occurred  during compilation.


create procedure drop_tt (DName VARCHAR(128))


declare DatabaseName varchar (128);

declare TableName varchar (128);

declare sql_stmt varchar (100);

declare cname cursor for

select t.DatabaseName,



  from DBC.Tables t 

 where DatabaseName like '%' || :DName || '%' 

   and  TableName like any ('%_BKP%','%_TMP%','%_BACKUP%')

   and  CreateTimeStamp < (current_date - interval '1' month;

open cname;

fetch cname into DatabaseName, Tablename;

set sql_stmt = 'drop table' || :DatabaseName || '.' || :TableName;

prepare stmt1 from sql_stmt;

execute stmt1;

close cname;



9261 Stored procedure compiled with empty SPL file.

Help me, please!

gryback 271 posts Joined 12/08
02 May 2012

Sorry, can't help directly on this one but you may want to post this to the Database channel versus Enterprise as you may find the expertise more so there. 

You must sign in to leave a comment.