Ora OCI Stage (ORA-01036: illegal variable name/number)
Moderators: chulett, rschirm, roy
Ora OCI Stage (ORA-01036: illegal variable name/number)
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.
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
I have the reject link, but the job aborts and it does not write anything to the reject link.
kcbland wrote: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.
Your query,
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.
, even if correct, will never return any values.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')
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.
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.
![Laughing :lol:](./images/smilies/icon_lol.gif)
You're right, the SQL looks screwy. Still, the user is sending invalid data to the DML.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Could you please explain a bit more. I am unable to understand the problem.
ArndW wrote:Your query,, even if correct, will never return any values.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')
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.
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.
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.
ArndW wrote: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"
The WHERE clause is not your ORA-01036 problem. Arnd nicely pointed out a new potential issue.
Your problem is DATA RELATED.
Your problem is DATA RELATED.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Re: Ora OCI Stage (ORA-01036: illegal variable name/number)
try using TO_CHAR and change the datatype to varchar