Can Drop table be done here?

[coolmandba]

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.

[marist89]

do you have to drop any table privilege granted directly to the user executing the procedure?

[coolmandba]

Yes, I double checked it.

[marist89]

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>

[marist89]

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.

[coolmandba]

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.

[marist89]

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.

[coolmandba]

You're right, thanks very much. Learn something new today.