ORA-01036: illegal variable name/number
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 51
- Joined: Sat Dec 09, 2006 3:32 am
ORA-01036: illegal variable name/number
Hi all,
In a server job i have a sequential file as source and oracle table as lookup table.
And using transformer we are doing lookup on oracle table.
The problem is,job is aborting with below error .
"ORA-01036: illegal variable name/number" .
But we are able to do view data properly from oracle referece table.
Please suggest.
Thanks,
Elias.
In a server job i have a sequential file as source and oracle table as lookup table.
And using transformer we are doing lookup on oracle table.
The problem is,job is aborting with below error .
"ORA-01036: illegal variable name/number" .
But we are able to do view data properly from oracle referece table.
Please suggest.
Thanks,
Elias.
------------
Elias
Elias
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 51
- Joined: Sat Dec 09, 2006 3:32 am
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 51
- Joined: Sat Dec 09, 2006 3:32 am
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 51
- Joined: Sat Dec 09, 2006 3:32 am
we have changed the query to auto generated query and
SELECT TO_CHAR(CAL_DT, 'YYYY-MM-DD HH24:MI:SS'),FSCL_YR_NUM FROM TBLNAME WHERE CAL_DT=TO_DATE(:1, 'YYYY-MM-DD HH24:MI:SS')
But the job is giving different kind of errors.Just want to know whether we can use oracle stage (OCI) stage as reference table to do lookup in server job.
Can someone pls conform this.
Thanks,
Elias[/b]
SELECT TO_CHAR(CAL_DT, 'YYYY-MM-DD HH24:MI:SS'),FSCL_YR_NUM FROM TBLNAME WHERE CAL_DT=TO_DATE(:1, 'YYYY-MM-DD HH24:MI:SS')
But the job is giving different kind of errors.Just want to know whether we can use oracle stage (OCI) stage as reference table to do lookup in server job.
Can someone pls conform this.
Thanks,
Elias[/b]
------------
Elias
Elias
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Hi,
Regards,
Divya
"where" clause is ideally not required for input table or reference table. So it should not make a difference. Try it with alias name for the 1st field in the query, Oracle stage will expect a alias name same as the name that you mention in the columns field.elias.shaik wrote: SELECT TO_CHAR(CAL_DT, 'YYYY-MM-DD HH24:MI:SS'),FSCL_YR_NUM FROM TBLNAME WHERE CAL_DT=TO_DATE(:1, 'YYYY-MM-DD HH24:MI:SS')
Regards,
Divya
-
- Participant
- Posts: 51
- Joined: Sat Dec 09, 2006 3:32 am
Issue is resolved
We have used timestamp format in columns tab.
Query is auto generated.
Date format wasn't working.
Thanks to all for your support.
Thanks,
Elias.
We have used timestamp format in columns tab.
Query is auto generated.
Code: Select all
SELECT TO_CHAR(CAL_DT, 'YYYY-MM-DD HH24:MI:SS'),FSCL_YR_NUM FROM TBLNAME WHERE CAL_DT=TO_DATE(:1, 'YYYY-MM-DD HH24:MI:SS')
Thanks to all for your support.
Thanks,
Elias.
------------
Elias
Elias
Unfortunately, a couple of problems with this. First, a where clause is very much ideally required for a reference lookup (which is what is being discussed here) and makes a huge difference. It changes the behaviour from a static lookup to one driven by the input row data.dxk9 wrote:"where" clause is ideally not required for input table or reference table. So it should not make a difference. Try it with alias name for the 1st field in the query, Oracle stage will expect a alias name same as the name that you mention in the columns field.
Secondly, the 'alias' comment is applicable to PX but not to Server. The Server product couldn't care less about column names in your select - it goes strictly by column position.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers