Re: PL/SQL question

From: Slava Natapov <SNatapov_at_johnbryce.co.il>
Date: 1997/09/08
Message-ID: <34143BB3.538C@johnbryce.co.il>#1/1

Dmitry Babitsky wrote:
>
> 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
>
> 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

The problem is that you can't use DDL statements (like drop ,create) inside PL/SQL.
But you may use DBMS_SQL package.
Here is an example:

DECLARE

x		number;
cursor_id		integer;
return_code     integer;

BEGIN
  BEGIN
    select 1 into x
    from user_tables
    where table_name= 'MY_TABLE';

    cursor_id := dbms_sql.open_cursor;     dbms_sql.parse(cursor_id,'DROP TABLE MY_TABLE',dbms_sql.v7);     return_code:= dbms_sql.execute(cursor_id);     dbms_sql.close_cursor(cursor_id);
  EXCEPTION
     when no_data_found then null;
  END;
  cursor_id := dbms_sql.open_cursor;
  dbms_sql.parse(cursor_id,'CREATE TABLE MY_TABLE (col_a NUMBER,col_b VARCHAR2(10))',dbms_sql.v7);
  return_code:= dbms_sql.execute(cursor_id);   dbms_sql.close_cursor(cursor_id);
END;
/

The better way to do such things, is to create store procedure for DDL statements.
For example:

CREATE OR REPLACE procedure DO_DDL(cmd varchar2) as

cursor_id       integer;
return_code     integer;

BEGIN
 cursor_id := dbms_sql.open_cursor;
 dbms_sql.parse(cursor_id,cmd,dbms_sql.v7);  return_code := dbms_sql.execute(cursor_id);  dbms_sql.close_cursor(cursor_id);
END;
/

-- 
+----------------------------+
|      Slava Natapov.        |
|  Snatapov_at_johnbryce.co.il  |
+----------------------------+

Received on Mon Sep 08 1997 - 00:00:00 CDT