Re: How to find out if a table already exists with PL/SQL

From: Klaus Zeuch <KZeuchnospam_at_hotmail.com>
Date: Fri, 10 Nov 2000 10:10:17 +0100
Message-ID: <8uggon$aad$1@papyrus.erlm.siemens.de>

If you are on Oracle8i:

declare

    v_ret number := -1;
BEGIN
    begin

        select  1
        into    v_ret
        from    dba_tables where table_name = 'TEMPTBL';
    exception
        when no_data_found then
            v_ret := -1;

    end;
    if (-1 = v_ret) then

        begin
            dbms_output.put_line('Deleting table temptbl');
            execute immediate 'drop table TEMPTBL';
        exception
            when others then
                dbms_output.put_line('something went wrong');
        end;

    end if;
end;

In Versions before 8i you have to replace execute immediate with calls to dbms_sql (see your documentation). Instead of view dba_tables you could use user_tables, if the table is within user's schema.

Klaus
NoSpam <NoSpam_at_NoSpam.com> schrieb in im Newsbeitrag: 8uekvs$ibi$1_at_ih292.ea.unisys.com...
> Hi,
>
> I'm porting a Sybase script to Oracle. There is this thing that we do in
> Sybase but I just can't do in Oracle. In Sybase, we do:
>
> if exists (select * from sysobjects where name = 'TEMPTBL')
> begin
> print "Deleting table TEMPTBL....."
> drop table TEMPTBL
> end
> go
>
> Just how do I do this in Oracle?
>
> TIA
>
>
>
Received on Fri Nov 10 2000 - 03:10:17 CST