Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to conditionally copy and then create it using SQL scripts
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;
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