| 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: PL/SQL query
A copy of this was sent to c.raczko_at_usa.net
(if that email address didn't require changing)
On Fri, 12 Feb 1999 21:46:41 GMT, you wrote:
>I'm in the process of translating some SQL from SQL Server to Oracle and have
>had very little luck with the following simple statement:
> IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sometable')
> DROP TABLE sometable
>
>The simplest way of replicating this functionality that I found involves using
>PL/SQL, exceptions and cursors:
>
>DECLARE
> cursor_name INTEGER;
>BEGIN
> cursor_name := dbms_sql.open_cursor;
> dbms_sql.parse(cursor_name, 'DROP TABLE sometable', dbms_sql.v7);
> dbms_sql.close_cursor(cursor_name);
>EXCEPTION
> WHEN OTHERS THEN NULL;
>END;
>
>Surely there must be an easier way? Any help greatly appreciated.
since you don't care about the error (you must not, you have WHEN OTHERS, ignores all errors) why not just:
drop table sometable;
and ignore the error?
short of that why not make the above a procedure like:
create procedure drop_table( p_tname in varchar2 ) as
cursor_name INTEGER;
BEGIN
cursor_name := dbms_sql.open_cursor;
dbms_sql.parse(cursor_name, 'DROP TABLE ' || p_tname, dbms_sql.v7);
dbms_sql.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
and then you can:
drop_table( 't1' );
drop_table( 't2' );
....
>
>Cezary
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Feb 12 1999 - 18:15:50 CST
 |
 |