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

From: news for eric <eric_ong_at_tkk.att.ne.jp>
Date: Fri, 10 Nov 2000 15:13:09 +0900
Message-ID: <8ug3vm$45c$1@newsflood.tokyo.att.ne.jp>

TIA, if i were to do the same thing in oracle, i'd create a function object to check if the table exists and procedure object the do the actual table drop (using dynamic SQL). after compiling the two objects, execute the procedure and pass along TEMPTBL as a parameter (i.e., execute p_tbldrop('TEMTBL')). if you could include the table owner, the function would respond much faster.

good luck!
Eric

create or replace function fb_table_exists(tbl_nm in varchar2) return boolean is

    tempval all_tables.table_name%type;     retval boolean := TRUE;
    cursor c_getval is

        select table_name
          from all_tables
         where table_name = tbl_nm;

begin

    open c_getval;
   fetch c_getval into tempval;
   close c_getval;
   if tempval is null then
      retval := FALSE;

   end if;
   return(retval);
end fb_table_exists;
/

create or replace procedure p_tbldrop(tbl_nm in varchar2) as

    cid integer;
begin

    if f_table_exists(tbl_nm) then

        cid := dbms_sql.open_cursor;
        dbms_sql.parse(cid, 'DROP TABLE '|| tbl_nm , dbms_sql.v7);
        dbms_sql.close_cursor(cid);

    end if;

exception

    when others then

        dbms_sql.close_cursor(cid);
        <<exception handling script goes here>>

end p_tbldrop;
/

"NoSpam" <NoSpam_at_NoSpam.com> wrote in message news: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 - 00:13:09 CST