Re: Checkout for table existance: Without use of procedure?
Date: Wed, 18 Dec 2002 15:04:24 -0600
Message-ID: <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;
select count(*) into cnt from dba_objects where owner = 'JSMITH'
and object_name = 'CUSTOMER'
if cnt = 0 then
execute immediate 'CREATE TABLE JSMITH.CUSTOMER (blah blah blah)';
else
execute immediate 'TRUNCATE TABLE JSMITH.CUSTOMER';
end if;
begin
and object_type = 'TABLE';
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 Wed Dec 18 2002 - 22:04:24 CET