looping through tables to delete

I created an Oracle stored procedure to delete some backup tables. The script will fail on line 5, and I can not figure out why. The error message thinks that str is a stored procedure. Can anyone see what the issue might be here?

1 declare str varchar2(200);
2 begin for rec in (select table_name from all_tables where table_name like '%INFO_BAK_%')
3 loop
4 str := 'drop table  ' || rec.table_name;
5 execute immediate str;

6 end loop;

end;
/
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

slightwv (䄆 Netminder) Commented:
What you posted is an anonymous PL/SQL block not a stored procedure.

What you posted runs as-is using sqlplus:
SQL> declare
  2     str varchar2(200);
  3  begin
  4  for rec in (select table_name from all_tables where table_name like '%INFO_BAK_%') loop
  5     str := 'drop table  ' || rec.table_name;
  6     execute immediate str;
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>

Open in new window


How are you running it?
0

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
slightwv (䄆 Netminder) Commented:
You also don't need the str variable.

Here is a simplified PL/SQL block and doing it in an actual stored procedure.

begin
for rec in (select table_name from all_tables where table_name like '%INFO_BAK_%') loop
	execute immediate 'drop table  ' || rec.table_name;
end loop;
end;
/ 




create or replace procedure myproc as
begin
for rec in (select table_name from all_tables where table_name like '%INFO_BAK_%') loop
	execute immediate 'drop table  ' || rec.table_name;
end loop;
end;
/ 

exec myproc

Open in new window

1
brgdotnetcontractorAuthor Commented:
Hello. Can you try running the code when you get a chance? I also tried just using the same line below, but it still did not work?

execute immediate 'drop table  ' || rec.table_name;
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

awking00Information Technology SpecialistCommented:
>>I also tried just using the same line below, but it still did not work?<<
What do you mean when you say "it still did not work"? Are any tables being dropped? Is a specific table not being dropped? Are you getting an error message and, if so, what error? It may be that you have read access to some tables in the all_tables view, but not have the privilege to drop the table.
0
awking00Information Technology SpecialistCommented:
What does the following do?
begin
for t in (select owner, table_name from all_tables where table_name like '%INFO\_BAK\_%' escape '\') loop
execute immediate 'drop table '||t.owner||'.'||t.table_name;
end loop;
end;
/
0
slightwv (䄆 Netminder) Commented:
>>Hello. Can you try running the code when you get a chance?

I did run the code I posted.

Here is my complete test.  I create a table that matches, run the code and it is gone.

SQL> create table MY_INFO_BAK_TEST(col1 char(1));

Table created.

SQL>
SQL> select table_name from user_tables where table_name like '%INFO_BAK_%';

TABLE_NAME
-------------------------------------------------------------------------------------------------------------------
MY_INFO_BAK_TEST

SQL>
SQL> begin
  2  for rec in (select table_name from all_tables where table_name like '%INFO_BAK_%') loop
  3          execute immediate 'drop table  ' || rec.table_name;
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> select table_name from user_tables where table_name like '%INFO_BAK_%';

no rows selected

SQL>

Open in new window


I wonder if you don't have permission to drop the tables?  You are using the ALL_TABLES view which shows tables you can query.  They might be in a different schema and you might not have permission to drop them.
0
Mark GeerlingsDatabase AdministratorCommented:
Some suggestions:
1. Change your select to be from "user_tables" instead of from "all_tables".
2. If you want to use "all_tables", then add the schema owner also like this:
    begin for rec in (select owner, table_name from all_tables where table_name like '%INFO_BAK_%')
     loop
     str := 'drop table  ' || rec.owner||'.'||rec.table_name;
3. Change your "execute immediate..." line to do this instead:  (you will need "serveroutoput on")
    dbms_output.put_line('drop table  ' || rec.owner||'.'||rec.table_name||';'):
    Then, copy that/those line(s) into SQL Plus or SQL Developer and run it/them as separate SQL commands.  That way you will get a specific error message returned if Oracle cannot execute command(s) successfully.
0
brgdotnetcontractorAuthor Commented:
I will close this out today. Thanks all for your help. I am still figuring out the solution
0
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.