Ora OCI Stage (ORA-01036: illegal variable name/number)

[bapajju]

Hello,

I am using one SQl query that has a where claus. In the where clause, I have a condition that looks like this:

Where
USER_ID=:1
AND SALES_RCRD_TSP = TO_DATE (:11,'YYYY-MM-DD HH24:MI:SS')
AND SALES_RCRD_TSP = TO_DATE (:12,'YYYY-MM-DD HH24:MI:SS')

But while running the job, I am gettin the error message like:
"ORA-01036: illegal variable name/number"

Can anyone provide some slue to this, please.

Thanks in Advance.

[kcbland]

Somewhere you are passing in a column value for updating that does not match the datatype. I suggest you check your Reject link output file (you did have one, right? Best practice tip) and see which column on the rejected row didn't match the datatype and put the appropriate derivation/constraint checks to prevent/correct.

[bapajju]

I have the reject link, but the job aborts and it does not write anything to the reject link.

Somewhere you are passing in a column value for updating that does not match the datatype. I suggest you check your Reject link output file (you did have one, right? Best practice tip) and see which column on the rejected row didn't match the datatype and put the appropriate derivation/constraint checks to prevent/correct.

[ArndW]

Your query,

USER_ID=:1
AND SALES_RCRD_TSP = TO_DATE (:11,'YYYY-MM-DD HH24:MI:SS')
AND SALES_RCRD_TSP = TO_DATE (:12,'YYYY-MM-DD HH24:MI:SS')

, even if correct, will never return any values.

What happens when you do a "view data" in your job? Does it function correctly from within the Designer? This is an Oracle error, so you should also be able to view the SQL in the designer and then paste it into your query tool to see if it is syntactically correct.

[kcbland]

Hey, both cursor variables occasionally might be the same.


You're right, the SQL looks screwy. Still, the user is sending invalid data to the DML.

[bapajju]

Could you please explain a bit more. I am unable to understand the problem.

Your query,

USER_ID=:1
AND SALES_RCRD_TSP = TO_DATE (:11,'YYYY-MM-DD HH24:MI:SS')
AND SALES_RCRD_TSP = TO_DATE (:12,'YYYY-MM-DD HH24:MI:SS')

, even if correct, will never return any values.

What happens when you do a "view data" in your job? Does it function correctly from within the Designer? This is an Oracle error, so you should also be able to view the SQL in the designer and then paste it into your query tool to see if it is syntactically correct.

[ArndW]

bapajju,

if are doing two different date comparisons on your SALES_RCRD_TSP column and are AND'ing them together. If I were to rephrase the query it would be like saying "WHERE x=1 AND x=2"

[bapajju]

Got you. I changed the query to

USER_ID=:1
AND SALES_RCRD_TSP >= TO_DATE (:11,'YYYY-MM-DD HH24:MI:SS')
AND SALES_RCRD_TSP <= TO_DATE (:12,'YYYY-MM-DD HH24:MI:SS')

but still I am getting the same error.

bapajju,

if are doing two different date comparisons on your SALES_RCRD_TSP column and are AND'ing them together. If I were to rephrase the query it would be like saying "WHERE x=1 AND x=2"

[kcbland]

The WHERE clause is not your ORA-01036 problem. Arnd nicely pointed out a new potential issue.

Your problem is DATA RELATED.

[sudheer05]

try using TO_CHAR and change the datatype to varchar