DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Anonymous Block

  1. #1
    Join Date
    Dec 2000
    Posts
    87
    Hi all,

    Can someone tell me what's wrong with the following, and how do I correct it, it's not working. Or is there a simple way to do this?
    Thanks.

    declare
    num integer;
    begin
    select 1 into num where exists(select 1 from user_objects where object_name='T');
    if (num=1) then
    execute immeidate 'drop table t cascade';
    end if;
    end;
    /

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    one thing I would recommend, is to change your code to this :

    begin
    execute immediate 'drop table T cascade';
    end;

    Why bother to check first? worst case, you will just get an
    error saying the object does not exist.

    However, if you really want to check first, your query is
    wrong (you don't need where exists). should be like this :

    select 1 into num from user_tables where object_name='T';

    Also, I think you should select from user_tables and not user_objects since you are dropping a table.

    -John

  3. #3
    Join Date
    Dec 2000
    Posts
    87
    Yes, I changed my code but it simply do not run in sqlplus worksheet. Can I do
    execute immeidate 'drop table t cascade'
    Please advise!

    declare
    num integer;
    begin
    select 1 into num from user_tables where table_name='T';
    if (num=1) then
    execute immeidate 'drop table t cascade';
    end if;
    end;
    /

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    What is the error you get? Errro codes and messages make it easier for us to help you.

    I see two problems:
    1. It is EXECUTE IMMEDIATE, not EXECUTE IMMEIDATE.
    2. If there are no tables satisfying your condition, exception NO_DATA_FOUND is raised, and control is passed from execution section of the block to the exception handler.

    If you want to check for the existence of the table first try something like:

    SELECT COUNT(*)
    INTO num
    FROM user_tables
    WHERE table_name = 'T';



  5. #5
    Join Date
    Nov 2000
    Posts
    245

    can you just use
    drop table T cascade;

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