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.

execute immediate 'CREATE TABLE "YOURLOCALTEMPTABLE"

                    ("ID" NUMBER,
                     "PARENTID" NUMBER,
                     "ARTICLEID" NUMBER,
                     etc.... )';

Just some considerations (sorry for the English, it's not my native language):

1. If you're creating a 'temp table', as you call it on this example, have you considerate using GLOBAL TEMPORARY TABLES on 10g? when creating a global temporary table you don't need to create and drop the table each time you run the script, global temporary tables don't write undo and redo logs, look for more information here http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2213
2. try to not double quotes your object names or you might have a big headache, because for oracle "YOURLOCALTEMPTABLE" <> "YourLocalTempTable" and so on

Regards,

Eliezer Daneluti Received on Tue Sep 04 2007 - 12:06:13 CDT