DSXchange

Posted: **Mon Apr 23, 2007 3:19 pm**

Hi,

I am updating records in DRS Stage with the following update statment.(generated statment)

UPDATE tablename
SET EFF_STATUS=?,
DESCR=?,
DESCRSHORT=?,
GVT_VET_RECR_CODE=?,
GVT_VET_RECR_PTS=?,
HRS_ROW_ADD_DTTM=TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'),
HRS_ROW_ADD_OPRID=?,
HRS_ROW_UPD_DTTM=TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'),
HRS_ROW_UPD_OPRID=?,
LOAD_ERROR=?,
DATA_ORIGIN=?,
CREATED_EW_DTTM=TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'),
LASTUPD_EW_DTTM=TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'),
BATCH_SID=?
WHERE GVT_VET_PREF_APPT=? AND EFFDT=TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') AND SRC_SYS_ID=?;

After updating records it is throwing this warning message
ORA-00900: invalid SQL statement

I am not able to pin point the problem in the sql statment.Can you please help me out

Thanks
Sri

Posted: **Mon Apr 23, 2007 3:48 pm**

So the job runs and records are updated in the database but then it throws a warning at the end?

Have you tried running it with just one record which you know performs an update? Does the warning still occur? It may be data related, otherwise not sure why it would work for some records and then throw warning.

Posted: **Mon Apr 23, 2007 3:57 pm**

You might want to check your After SQL tab and make sure nothing is there. I've seen goofy things like that when a space ends up getting put in it by accident.

Posted: **Mon Apr 23, 2007 4:34 pm**

Also check if there is an output link from this stage to any other stage such as a Hashed File and if the select statement in that one is ok...

Posted: **Mon Apr 23, 2007 5:44 pm**

From Oracle Error message list i got this, I don't know but it might help you.

Code: Select all

ORA-00900 invalid SQL statement

Cause: The statement is not recognized as a valid SQL statement. This error can occur if the Procedural Option is not installed and a SQL statement is issued that requires this option (for example, a CREATE PROCEDURE statement). You can determine if the Procedural Option is installed by starting SQL*Plus. If the PL/SQL banner is not displayed, then the option is not installed.

Action: Correct the syntax or install the Procedural Option.

Posted: **Mon Apr 23, 2007 6:09 pm**

Yes Craig,

there was a space in sql after tab.After removing it ,it went fine.Thanks

Thanks for all of your suggestions

Posted: **Mon Apr 23, 2007 6:45 pm**

:D Please mark as Resolved!

Posted: **Mon Apr 23, 2007 7:32 pm**

Now thats what you call, experience

Posted: **Mon Apr 23, 2007 7:54 pm**

Experience is what you get when what you expected to happen doesn't.

Posted: **Mon Apr 23, 2007 10:51 pm**

O yea, left out the `ed from experienced. O well, potayto potaato.

DSXchange

ORA-00900: invalid SQL statement

ORA-00900: invalid SQL statement

Re: ORA-00900: invalid SQL statement