-
Hi all,
Can I do this in a stored procedure?
select count(1) into tablecount from all_tables where owner='ABC' and table_name='XXX;
if (tablecount=1) then
execute immediate 'drop table ABC.XXX;
end if;
Somehow 'drop table ABC.XXX' it's not working!
I didn't get any error message, just that the table is not dropped. Please advise.
-
do you have to drop any table privilege granted directly to the user executing the procedure?
Jeff Hunter
-
Yes, I double checked it.
-
Not sure what's wrong, it worked for me:
system@dev815nt.us> desc jeff.xxx
Name Null? Type
----------------------------------------- -------- ----------------------------
X CHAR(1)
system@dev815nt.us> @test1.sql
Input truncated to 1 characters
dropping tab...
PL/SQL procedure successfully completed.
system@dev815nt.us> desc jeff.xxx
ERROR:
ORA-04043: object jeff.xxx does not exist
system@dev815nt.us> l
1 declare
2 t1 number;
3 begin
4 select count(1) into t1
5 from all_tables where owner='JEFF' and table_name='XXX';
6 if (t1=1) then
7 dbms_output.put_line('dropping tab...');
8 execute immediate 'drop table JEFF.XXX';
9 end if;
10* end;
system@dev815nt.us>
Jeff Hunter
-
doh, let me take that back.
If I re-create exactly as in your example, the problem comes in from the fact that I can't see the table in the ALL_TABLES view. You might have to have select privs on the table in order to use this particular piece of code...
jeff@dev815nt.us> desc abc.xxx
Name Null? Type
----------------------------------------- -------- ----------------
X CHAR(1)
jeff@dev815nt.us> l
1 declare
2 t1 number;
3 begin
4 select count(1) into t1
5 from all_tables where owner='ABC' and table_name='XXX';
6 if (t1=1) then
7 dbms_output.put_line('dropping tab...');
8 execute immediate 'drop table ABC.XXX';
9 end if;
10* end;
jeff@dev815nt.us> /
PL/SQL procedure successfully completed.
Jeff Hunter
-
Thanks marist89,
I'm able to see the table name from ALL_TABLES view, if I simply do a select from sqlplus, so that should mean I have the SELECT privilege on ALL_TABLES:
select table_name from all_tables where table_name='ABC.XXX';
So I don't know what's wrong.
-
Not necessarily. You must be granted explicit select permission on the all_tables view to see it in a procedure. Seeing it from sqlplus does not necesarily mean you can see it in a procedure.
Jeff Hunter
-
You're right, thanks very much. Learn something new today.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|