Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to conditionally copy and then create it using SQL scripts

Re: How to conditionally copy and then create it using SQL scripts

From: George Barbour <gbarbour_at_csc.com>
Date: Wed, 10 Oct 2001 15:26:30 +0100
Message-ID: <3bc45795$1@pull.gecm.com>


If it doesn't exist then create it yourself.

Try this
FUNCTION sp_tableexists( i_s_tablename IN VARCHAR2) RETURN BOOLEAN IS

    CURSOR cq_usertab (i_s_tablename VARCHAR2) IS     SELECT table_name FROM user_tables
    WHERE table_name = i_s_tablename;

    l_r_tablerow cq_usertab%rowtype;

BEGIN
    OPEN cq_usertab (i_s_tablename);
    FETCH cq_usertab INTO l_r_tablerow;
    IF cq_usertab%notfound THEN

        CLOSE cq_usertab;
        RETURN FALSE;
    ELSE
        CLOSE cq_usertab;
        RETURN TRUE;

    END IF;
END sp_tableexists;

George Barbour.

"Gene Hubert" <gwhubert_at_hotmail.com> wrote in message news:7e3fa619.0110100524.2ccbcace_at_posting.google.com...
> Use a pl/sql anonymous block and native dynamic sql. Something like:
>
> declare
> cnt pls_integer := 0;
> begin
> select count(*) into cnt from user_tables where table_name='XXX';
> if cnt = 0 then
> execute immediate 'create table junk (x number);
> end if;
> dbms_output.put_line(cnt);
> end;
> /
>
> Gene Hubert
> Durham, NC
>
>
> lzhang_at_bj.bexcom.com (zl) wrote in message
news:<de83f985.0110100042.43dac84f_at_posting.google.com>...
> > In SQL server, the following SQL statements can be used to create a
> > table if it is not existed.
> >
> >
> > IF EXISTS (SELECT * FROM sysobjects
> > WHERE name = 'BusinessDocIdGenerator'
> > AND type = 'U')
> > DROP TABLE BusinessDocIdGenerator
> >
> > CREATE TABLE BusinessDocIdGenerator (
> > NumericGeneratorId int NULL,
> > .....
> > )
> >
> >
> > How can I do this in Oracle? Oracle doens't allow "if" appears in
> > sqlplus.
Received on Wed Oct 10 2001 - 09:26:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US