Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL question
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;
when others then
if dbms_sql.is_open(exec_cursor) then dbms_sql.close_cursor(exec_cursor); end if; raise;
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