Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to find out if a table already exists with PL/SQL

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:34:00 +0100
Message-ID: <8ugi56$b4f$1@papyrus.erlm.siemens.de>

Sorry,
made a little typo-error. Of course it should be: ...
if (1 = v_ret) then
...

Klaus
Klaus Zeuch <KZeuchnospam_at_hotmail.com> schrieb in im Newsbeitrag: 8uggon$aad$1_at_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:34:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US