| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: if exists and dropping tables
On Tue, 7 Oct 1997 03:37:29 GMT, TerryTraub_at_world.std.com wrote:
>
>Hi,
>I'm doing an Oracle project after many years of Sybase and while I'm
>favorably impressed with many aspects of Oracle, I greatly miss the Sybase
>syntax for checking if a table exists:
>
> if exists (select * from sysobjects where name = 'my_table')
> begin
> drop table my_table
> end
> go
>
> create table my_table (...)
> ...
>
>This made for nice clean scripts. How do you do the equivalent
>in Oracle, to avoid getting lots of spurious error messages when
>installing tables the first time? Looks like this calls for some
>convoluted PL/SQL to check the USER_TABLES view or some such.
>
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;
/
NOTE: since roles are not enabled in stored objects, the procedure execute_immediate_proc only runs with priveleges you have been granted directly. If you cannot run the following in sqlplus:
SQL> set role none; SQL> drop table foo; SQL> create table foo; SQL> drop table foo;
without getting an insufficient priveleges error, you will need to have CREATE TABLE granted directly to you first.
>While I'm at it... Why can't you obtain result sets using stored
>procedures in Oracle? It makes for cleaner code to keep all the SQL
>in the server, and yet this limitation forces us to use Pro*C or OO4O,
>which binds the front end code too tightly to the database schema.
>Thanks for any enlightening postings!
>
With 7.2 and 7.3 of the database you have cursor variables. Cursor variables are cursors opened by a pl/sql routine and fetched from by another application or pl/sql routine (in 7.3 pl/sql routines can fetch from cursor variables as well as open them). The cursor variables are opened with the privelegs of the owner of the procedure and behave just like they were completely contained within the pl/sql routine. It uses the inputs to decide what database it will run a query on.
Here is a 7.2 / 7.3 example in SQL*Plus and PRO*C
create or replace package empCurType
as
cursor c1 is select ename, mgr, dept.deptno, dname, loc from emp, dept;
type empCur is ref cursor return c1%rowtype;
procedure open_cursor( p_cursor in out empCur, p_deptno in number );
create or replace package body empCurType as
procedure open_cursor( p_cursor in out empcur, p_deptno in number )
is
begin
if ( p_deptno in ( 10, 20 ) ) then -- open local query open p_cursor for select ename, mgr, dept.deptno, dname, loc from emp, dept where emp.deptno = dept.deptno and dept.deptno = p_deptno; else open p_cursor for select ename, mgr, dept.deptno, dname, loc from emp_at_aria.world, dept_at_aria.world where emp.deptno = dept.deptno and dept.deptno = p_deptno; end if;
end empCurType;
/
show errors
REM --------------- SQL*Plus using RefCursor to display results ------
variable C refcursor
exec empCurType.open_cursor( :C, 10 )
print C
REM ----------- PRO*C Getting the cursor ------------------REM
REM typedef char asciiz; REM EXEC SQL TYPE asciiz IS STRING(100); REM SQL_CURSOR my_cursor; REM asciiz ename[40]; REM int mgr; REM short mgr_i; REM int deptno; REM asciiz dname[50]; REM asciiz loc[50]; REM int i;
REM EXEC SQL EXECUTE BEGIN empCurType.open_cursor( :my_cursor, 10 ); end;
REM END-EXEC;
REM
REM for( ;; )
REM {
REM EXEC SQL WHENEVER NOTFOUND DO BREAK;
REM EXEC SQL FETCH :my_cursor
REM INTO :ename, :mgr:mgr_i, :deptno, :dname, :loc;
REM
REM printf( "%s, %d, %d, %s, %s\n",
REM ename, mgr_i?-1:mgr, deptno, dname, loc );
REM }
REM EXEC SQL CLOSE :my_cursor;
Also, The beta version of Microsoft's latest Oracle ODBC driver (v2.0) is available at: http://www.microsoft.com/msdownload/rds/rdsdownload.htm. It is part of the RDS Server 1.5 Beta download. When you download it you have the option to install any of the included components (ADO, OLE DB, the Oracle ODBC driver, etc...).
Some of the features of the new driver are:
.....
- It can return a resultset from a stored procedure.
.....
>-Terry
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |