• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

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;
/
0
brgdotnet
Asked:
brgdotnet
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
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
 
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
awking00Commented:
>>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
 
awking00Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now