Re: Is there an equivalent to the SQL Server "if exists" statement?

As far as I understood, some PL/SQL can't be used in SQL Worksheet. Maybe I'm wrong?

Anyway, we're using Oracle 7.x on NT and UNIX so any 8.x only solutions unfortunately don't help.

Thanks for your reply!

In article <931532844.28.0.nnrp-13.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> Can you expand on why you don't want
> to use PL/SQL ?
>
> A 'hidden PL/SQL' option if you have Oracle 8.1.5 -
> Create a table 'tables_to_drop (t_name varchar2(32));
> create an after row trigger with the 'autonomous transaction' pragma
> The trigger should check in PL/SQL to see if the table identified
> by :new.t_name exists, and drop if it does so (or just drop it
> and catch the exception if it doesn't)
>
> Your SQL code would then be:
> insert into table_to_drop values ('my_table');
> rollback;
>
> Actually, this might even be one of the examples that
> Thomas Kyte wrote up in his latest Digging in to 8i'
> article. Check out his SIG next time he sends a message
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> Franklin Bowen wrote in message <3785FB82.58F9FB07_at_my-deja.com>...
> >In SQL Server one can do this:
> >
> >if exists (select * from sysobjects where id = object_id('dbo.x') and
> >sysstat & 0xf = 3)
> > drop table dbo.x
> >
> >to drop a table only if it exists. I want to do the same thing in
> >Oracle SQL (*NOT* PL/SQL) if possible.
> >
>
>

--
Franklin Bowen

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jul 19 1999 - 10:37:47 CDT