Can Drop table be done here?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Can Drop table be done here?

  1. #1
    Join Date
    Dec 2000
    Posts
    87

    Angry

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    do you have to drop any table privilege granted directly to the user executing the procedure?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Dec 2000
    Posts
    87
    Yes, I double checked it.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Dec 2000
    Posts
    87
    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.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Dec 2000
    Posts
    87

    Smile

    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
  •  



Click Here to Expand Forum to Full Width