How to find out if a table already exists with PL/SQL 
Author Message
 How to find out if a table already exists with PL/SQL
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



Wed, 18 Jun 1902 08:00:00 GMT
 How to find out if a table already exists with PL/SQL

use the DBMS_SQL package to execute the DROP statement.
"wrap" the PARSE in anonymous block, to catch the exception
if the table does not exist.

v_csr := ...
v_drop_stmt := 'DROP TABLE TEMPTBL';
BEGIN
  DBMS_SQL.PARSE( v_csr , v_drop_stmt );
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;

check the PL/SQL docs for information on executing "dynamic SQL"
for examples.  in Oracle 8.1.x, there is new syntax that is easier to
use than the DBMS_SQL package...

Quote:

> 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



Wed, 18 Jun 1902 08:00:00 GMT
 How to find out if a table already exists with PL/SQL
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;
/


Quote:
> 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



Tue, 29 Apr 2003 14:13:09 GMT
 How to find out if a table already exists with PL/SQL
Hi TIA,

If you are going to use SQL*Plus with script file,
make script file like following dorptmp.sql
and execute this script file like this:


Mitsunori Ishikawa

----- droptmp.sql -----
set newpage 0
set space 0
set linesize 80
set pagesize 0
set echo off
set feedback off
set heading off
set termout off

spool dropping.sql
select 'PROMPT Dropping table '||table_name||'.....'||CHR(10)||'DROP TABLE '||table_name||';'
  from user_tables where table_name = 'TEMPTBL';
spool off
set feedback on

----- droptmp.sql -----

Quote:

> 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



Wed, 18 Jun 1902 08:00:00 GMT
 How to find out if a table already exists with PL/SQL
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


Quote:
> 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



Wed, 18 Jun 1902 08:00:00 GMT
 How to find out if a table already exists with PL/SQL
Sorry,
made a little typo-error. Of course it should be:
...
if (1 = v_ret) then
...

Klaus


Quote:
> 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


> > 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



Wed, 18 Jun 1902 08:00:00 GMT
 How to find out if a table already exists with PL/SQL
H,
Create a function like:-
FUNCTION sp_tableexists( i_s_tablename IN VARCHAR2) RETURN BOOLEAN IS

    CURSOR cq_usertab (i_s_tablename VARCHAR2) IS
    SELECT table_name FROM user_tables
    WHERE table_name = i_s_tablename;

    l_r_tablerow cq_usertab%rowtype;

BEGIN
    OPEN cq_usertab (i_s_tablename);
    FETCH cq_usertab INTO l_r_tablerow;
    IF cq_usertab%notfound THEN
        CLOSE cq_usertab;
        RETURN FALSE;
    ELSE
        CLOSE cq_usertab;
        RETURN TRUE;
    END IF;
END sp_tableexists;

HTH.
George Barbour.

Quote:
> 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



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. How to check if a table already exists

2. vb: check if table already exists??

3. check if table already exists??

4. How to check if an tempory table already exists

5. Temporary Table already exists

6. table already exists ???

7. an already existing alter table drop column ?!?!?!

8. add new table unless already exists

9. Add a new Database field to a already-existing Paradox table at RUNTIME

10. table already exists error

11. Checking if a #temp1 table exists already

12. Adding PRIMARY KEY to already existing table's field


 
Powered by phpBB® Forum Software