Re: Cant see my error in SQL

On Sep 4, 12:16 pm, JaffaB <jaffa_br..._at_yahoo.co.uk> wrote:
> Hi all,
>
> I have been looking at this script for days - just cant see where the
> error is. In my script, I have the following logic (does the table
> exist, if not, create it)....
>
> bk number(30);
> PCID number(10);
> begin
> select count(*) into bk from ALL_TAB_COLS where
> table_name='YourLocalTempTable';
> if bk=0 then
> begin
> CREATE TABLE "YOURLOCALTEMPTABLE" <------- * * * Error Line * *
> * *
> ("ID" NUMBER,
> "PARENTID" NUMBER,
> "ARTICLEID" NUMBER,
> etc....
>
> However, when I try to compile it (Ora 10g), get the error...
>
> Line # = 13 Column # = 4 Error Text = PLS-00103: Encountered the
> symbol "CREATE" when expecting one of the following:
>
> begin case declare exit for goto if loop mod null pragma
> raise return select update while with <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> <<
> close current delete fetch lock insert open rollback
> savepoint set sql execute commit forall merge pipe
>
> Please, somebody take me out of this madness and tell me what is wrong
> with my script.
>
> Many tahnks in advance.

You can not directly execute DDL statements from PL/SQL. See the execute immediate statement and the dbms_sql package documentation for handling dynamic SQL.

Why are you ckecking to see if the table exists and then creating via a script anyway? Tables should be created once then just used.

HTH -- Mark D Powell -- Received on Tue Sep 04 2007 - 12:02:03 CDT