user defined sql

[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!

[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?

[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

[Sainath.Srinivasan]

The comma is meant to be there. It implies ANY option of the 2 values provided.

[chulett]

And the space isn't an issue either. It can be there or not. 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?

[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).

[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.