Creating A Oracle Cursor

Hi There another SQL Question for you all....

I am trying to create a cursor with the SQL attached I am fairy confident works:

select table_name from user_tables
  where table_name like '%TRACE%'
  and table_name not in 'SIIPSCINPARAMETERSCHECKTRACE'
  and table_name not in 'SIIPSCINPARAMETERSTRACE'
  and table_name not in 'VMSTRANSACTIONDETAILSTRACE'
  and table_name not in 'VMSTRANSACTIONHEADERTRACE';

The cursor will initalise based on the results of the SQL. for every record returned from the cursor I want to store the tablename into a variable.  On every loop the variable will be added to a SQL delete command.

Basically I am trying to achieve the following:

Create a package that is executed via DBMS_Jobs.  This package will include the cursor that runs the above SQL.  This will be used to delete data from cursor results excluding tables above.  
dlrsuppportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kamal_therockyCommented:
Hi

You can use this code to create your package.

Declare

Cursor cur_user_table
Is
select table_name from user_tables
  where table_name like '%TRACE%'
  and table_name not in 'SIIPSCINPARAMETERSCHECKTRACE'
  and table_name not in 'SIIPSCINPARAMETERSTRACE'
  and table_name not in 'VMSTRANSACTIONDETAILSTRACE'
  and table_name not in 'VMSTRANSACTIONHEADERTRACE';

strSQL varchar2(10000);

Begin

For rec_user_table in cur_user_table Loop

      strSQL := 'Delete from ' || rec_user_table.table_name;
      Execute Immediate strSQL;

End Loop;

commit;
End;

Thanks
kamal
awking00Information Technology SpecialistCommented:
You can also use "implicit" cursor. Also, I would probably change not in to not = since you are showing full values. If not you could also substitute a single not in statement.

declare
strsql varchar2(255);
begin
for rec in
(select table_name from user_tables
  where table_name like '%TRACE%'
  and table_name not = 'SIIPSCINPARAMETERSCHECKTRACE'
  and table_name not = 'SIIPSCINPARAMETERSTRACE'
  and table_name not = 'VMSTRANSACTIONDETAILSTRACE'
  and table_name not = 'VMSTRANSACTIONHEADERTRACE')
loop
strsql := 'delete from '||rec.table_name;
execute immediate strsql;
end loop;
end;

OR;

declare
strsql varchar2(255);
begin
for rec in
(select table_name from user_tables
  where table_name like '%TRACE%'
  and table_name not in ('SIIPSCINPARAMETERSCHECKTRACE','SIIPSCINPARAMETERSTRACE'
                                   ,'VMSTRANSACTIONDETAILSTRACE','VMSTRANSACTIONHEADERTRACE')
loop
strsql := 'delete from '||rec.table_name;
execute immediate strsql;
end loop;
end;

Also, if there are no conditions for the delete statement, truncate will go faster but it can't be rolled back.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kamal_therockyCommented:
Hi

You can also Truncate the data instead of delete..if you feel you are not going to rollback the data and there is no condition in Where clause

Thanks
kamal
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

dlrsuppportAuthor Commented:
Thanks guys, I would put the cursor in the package body creation though would I not?  As i need to define some data retention which I am happy with....

This site is great :-)
dlrsuppportAuthor Commented:
Hi there,

Thanks for all your updates.  I am going down the route of Karmal's example as shown below:

Declare

Cursor cur_user_table
Is
select table_name from user_tables
  where table_name like '%TRACE%'
  and table_name not in 'SIIPSCINPARAMETERSCHECKTRACE'
  and table_name not in 'SIIPSCINPARAMETERSTRACE'
  and table_name not in 'VMSTRANSACTIONDETAILSTRACE'
  and table_name not in 'VMSTRANSACTIONHEADERTRACE';

strSQL varchar2(10000);

Begin

For rec_user_table in cur_user_table Loop

     strSQL := 'Delete from ' || rec_user_table.table_name;
     Execute Immediate strSQL;

End Loop;

commit;
End;

On the  strSQL := 'Delete from ' || rec_user_table.table_name; line I wish to put in a where statement like this:

strSQL := 'Delete from ' || tracedelloop.table_name where to_date(lastupdate) < to_date(sysdate) -90;

But its not having it, anyone got any ideas?

Thanks in advance.


slightwv (䄆 Netminder) Commented:
assuming every table has a column called 'lastupdate' and it is a character datatype (if it's a date datatype there is no need for to_date.  Also, sysdate is already a date.):

strSQL := 'Delete from ' || rec_user_table.table_name || 'where to_date(lastupdate) < sysdate-90';

awking00Information Technology SpecialistCommented:
It's fine that you are going to use the explicit cursor method that kamal suggested, but you still want to either change the "not in" statements to "not =" or use a single "not in" statement (where table_name not in ('SIIPSCINPARAMETERSCHECKTRACE','SIIPSCINPARAMETERSTRACE'
                                   ,'VMSTRANSACTIONDETAILSTRACE','VMSTRANSACTIONHEADERTRACE')).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.