Re: PL/SQL Problem

From: <seci_at_ludens.elte.hu>
Date: 1997/09/06
Message-ID: <1997Sep6.190640.42730@ludens>#1/1

hello
In article <01bcba20$8e0cbad0$28ccb7c7_at_dmitry40e>, "Dmitry Babitsky" <dimitry_at_ix.netcom.com> writes:
> I need to find out in my procedure if a table exists and drop and recreate
> it .
> This is how it's done in Transact-SQL(SQL Server):
> IF EXISTS(SELECT * FROM sysobjects where name = my_table) DROP TABLE
> my_table
> .....
> CREATE TABLE my_table

declare
  n integer;
  c integer; -- cursor
  st integer; -- status
...

begin
...
  select count(*) into n from user_tables where table_name=my_table;   if n=0 then

     c := dbms_sql.open_cursor;
     dbms_sql.parse(c, 'drop table '||my_table, dbms_sql.native);
     st := dbms_sql.execute(c);
     dbms_sql.close_cursor(c);

  end if;
...

for more info take a look at $ORACLE_HOME/rdbms/admin/dbms*.sql

>
> Can anybody help me do it in PL/SQL? Thanks in advance and if you can -
> please e-mail your answer.
>
> --
> Dmitry Babitsky. MCSE, MCSD
>
> dimitry_at_ix.netcom.com

HTH
        peter Received on Sat Sep 06 1997 - 00:00:00 CDT