Re: PL/SQL question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/09/05
Message-ID: <341252d5.20187568_at_newshost>#1/1

On 5 Sep 1997 15:16:23 GMT, "Dmitry Babitsky" <dimitry_at_ix.netcom.com> 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.

first, you'll need to use dynamic sql in plsql to create tables. if you are creating a stored procedure (as opposed to the anonymous block i show you below which is pl/sql but not a stored procedure) you'll need to make sure the owner of the procedure has CREATE TABLE granted directly to them (not via a role) else you'll get insufficient priveleges at runtime.

second, we'll forget about trying to see if the table is there (although you could, you would just query the data dictionary). Instead, we'll just drop the table and handle the error that would happen if the table didn't exist gracefully.

so your plsql code might look like:

declare

    TABLE_OR_VIEW_DOES_NOT_EXIST exception;     pragma exception_init( TABLE_OR_VIEW_DOES_NOT_EXIST, -942 ); begin

  begin
    execute_immediate_proc( 'drop table foo' );   exception
    when TABLE_OR_VIEW_DOES_NOT_EXIST then null;   end;  

  execute_immediate_proc( 'create table foo( x int )' ); end;
/

the execute_immediate_proc is a convienence routine I use around the dbms_sql package, it is:

create or replace
function execute_immediate( stmt in varchar2 ) return number
as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

    return rows_processed;
exception

    when others then

      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

end;
/    

create or replace
procedure execute_immediate_proc( stmt in varchar2 ) as

    rows_processed number default 0;
begin

    rows_processed := execute_immediate( stmt ); end;
/

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 05 1997 - 00:00:00 CEST