Re: Checkout for table existance: Without use of procedure?

Hmmm, a bit better. Will still have to change my code to handle this Oracle suckiness.

I'll give it a go,

Thanks.

Dino.

"Chris Leonard" <s_p_a_m_chris_at_hotmail.com> wrote in message news:tp5M9.36$HE3.99689_at_news.uswest.net...
> Dino - This can't be done "inline" in the same way you do it with
> Transact-SQL. However, you can do this without creating a procedure by
> using an anonymous PL/SQL block. Here's an example:
>
> declare
> cnt number;
> begin
> select count(*) into cnt from dba_objects where owner = 'JSMITH'
> and object_name = 'CUSTOMER'
> and object_type = 'TABLE';
>
> if cnt = 0 then
> execute immediate 'CREATE TABLE JSMITH.CUSTOMER (blah blah blah)';
> else
> execute immediate 'TRUNCATE TABLE JSMITH.CUSTOMER';
> end if;
> end;
> /
>
>
> --
> Hope this helps,
> Chris
>
> ___________________________________
>
> Chris Leonard, The Database Guy
> http://www.databaseguy.com
>
> Brainbench MVP for Oracle Admin
> http://www.brainbench.com
>
> MCSE, MCDBA, MCT, OCP, CIW
> ___________________________________
>
> "dino.fancellu" <dino_at_dinofancellu.comREMOVE> wrote in message
> news:3e00db90$0$305$bed64819_at_pubnews.gradwell.net...
> >
> > I want to check for the existance of a table, but don't want to have to
> > create a procedure to do the checking, i.e.
> > I want it to be inline.
> >
> > In SQL Server I would do this:
> >
> > IF( NOT EXISTS( SELECT * FROM sysobjects WHERE NAME='Customer' AND
> > type='U' ) )
> > ...
> >
> > Is there an equivalent in Oracle?
> >
> > It must be inline, i.e. please don't point me to the existing pl/sql
 procs.
> >
> > Thanks.
> >
> > Dino.
> >
> >
>
>
Received on Thu Dec 19 2002 - 01:54:32 CET