Page 1 of 1
user defined sql
Posted: Wed May 25, 2005 10:13 pm
by weela_lee
I have a user-defined sql in Oracle Stage, which works fine in parallel job but failed in server job.
UPDATE
test_table
SET
IS_EXPIRED = 'Y'
WHERE
(COL_ID in (666, 667) )
error message in server job is:
ORA-01722: invalid number
Does anyone know why it won't work in server job?
Thanks in advance!
Posted: Thu May 26, 2005 2:42 am
by Sainath.Srinivasan
Check whether
1.) Is_Expired is a numeric datatype
2.) COL_ID is not a numeric data type
Can you run the same successfully via sqlplus or toad?
Re: user defined sql
Posted: Thu May 26, 2005 5:20 am
by mpouet
HI,
1) There is a space between "666," and "667" in your query.
2) Are your sure you are not supposed to use "." as decimal separator.
Good luck
Matthieu
Posted: Thu May 26, 2005 6:31 am
by Sainath.Srinivasan
The comma is meant to be there. It implies ANY option of the 2 values provided.
Posted: Thu May 26, 2005 6:49 am
by chulett
And the space isn't an issue either. It can be there or not.
![Wink :wink:](./images/smilies/icon_wink.gif)
I'm sure it will come down to column types as mentioned earlier.
Not sure why you'd want to do a query like this, one that's not tied to the current row using bind variables / parameter markers. It will be executed once
for every row sent to the stage, regardless of the contents of that row. Is that really what you had in mind?
Posted: Thu May 26, 2005 7:10 am
by ray.wurlod
Basically, you have to track down why Oracle believes you've used an invalid number. The only possibility in your query is 'Y'. Check the data type of IS_EXPIRED (use DESCRIBE).
Posted: Thu May 26, 2005 7:11 am
by Sainath.Srinivasan
Good point Craig. Maybe the column name used as the place holder is causing the problem somewhere. But this is only a guess as there is no information on the design.