Re: Testing a table existence

Hi Sylvain,

You can't perform DDL statements (CREATE, ALTER, DROP etc) like that...you need dynamic SQL.
If you're version is 8i or later, look into the "EXECUTE IMMEDIATE" syntax, else look at the DBMS_SQL package.

Hope this helps,

Steve

"Sylvain BOURDETTE" <sbourdette_at_danet.fr> wrote in message news:3c1f1221$0$196$626a54ce_at_news.free.fr...
> Thanks for your answer, but i had another problem !
>
> i try to do this :
>
> EXISTENCE:=0;
> SELECT COUNT(*) INTO EXISTENCE FROM user_tables WHERE UPPER(table_name) =
> UPPER('catal_prod');
> IF EXISTENCE > 0 THEN
> DROP TABLE CATAL_PROD CASCADE CONSTRAINTS;
> END IF;
>
> But i'm unable to drop the table i didn't know why.
> I got the folowing error message :
>
> DROP TABLE CATAL_PROD CASCADE CONSTRAINTS;
> *
> ERREUR Ó la ligne 49:
> ORA-06550: line 49, column 7:
> PLS-00103: Encountered the symbol "DROP" when expecting one of the
> following:
> begin declare exit for goto if loop mod null pragma raise
> return select update while <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> <<
> close current delete fetch lock insert open rollback
> savepoint set sql commit <a single-quoted SQL string>
>
> Why don't i can do this ?
>
> Thanks
>
>
> George Barbour a écrit dans le message <3c14de7b$1_at_pull.gecm.com>...
> >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.
> >"Sylvain BOURDETTE" <sbourdette_at_danet.fr> wrote in message
> >news:3c14cac6$0$3179$626a54ce_at_news.free.fr...
> >> How in PL SQL, can test if a table exists ?
> >>
> >> I try this
> >>
> >> IF EXISTS MyTable THEN
> >>
> >> END IF;
> >>
> >> But it doesn't work
> >>
> >> Thanks
> >>
> >>
> >
> >
>
>
Received on Tue Dec 18 2001 - 06:34:21 CST