ORA-01036: illegal variable name/number

elias.shaik · Post by **elias.shaik** » Thu Aug 20, 2009 2:58 am

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.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Aug 20, 2009 3:00 am

Have you got any user-defined sql?

can you post your sql?

elias.shaik · Post by **elias.shaik** » Thu Aug 20, 2009 3:06 am

Here is the SQL query i have used.

select FSCL_YR_NUM , TO_CHAR(cal_dt, 'YYYY-MM-DD') from TBLNAME

And data type for FSCL_YR_NUM is integer and data type for cal_dt is Varchar2(10).

Cal_dt is my key column used for doing lookup.

Thanks,
Elias.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Aug 20, 2009 3:09 am

Where is your field positions for reference key?

dxk9 · Post by **dxk9** » Thu Aug 20, 2009 3:12 am

Try this query:

select FSCL_YR_NUM , TO_CHAR(cal_dt, 'YYYY-MM-DD') as cal_dt from TBLNAME.

Regards,
Divya

dxk9 · Post by **dxk9** » Thu Aug 20, 2009 3:13 am

Try this query:

select FSCL_YR_NUM , TO_CHAR(cal_dt, 'YYYY-MM-DD') as cal_dt from TBLNAME.

Regards,
Divya

elias.shaik · Post by **elias.shaik** » Thu Aug 20, 2009 3:16 am

In columns tab it is 2nd column.

If it is not answering your question then i must have misunderstood the question.

Thanks,
Elias.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Aug 20, 2009 3:18 am

Make a copy of the job and set it to 'auto generated'.

You will see either :1, :2 etc or '?' in place of your source keys linking to reference.

chulett · Post by **chulett** » Thu Aug 20, 2009 5:29 am

No "where" clause? That means the key column is illegal, that or you need to add the clause and bind the Key into it as noted by position. And ensure your selected columns match the columns defined in the stage. Exactly.

elias.shaik · Post by **elias.shaik** » Fri Aug 21, 2009 3:29 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]

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Aug 21, 2009 4:19 am

elias.shaik wrote:But the job is giving different kind of errors.

What kind of errors ?

Post the unedited error so others can assist.

dxk9 · Post by **dxk9** » Fri Aug 21, 2009 4:28 am

Hi,

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')

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

Regards,
Divya

elias.shaik · Post by **elias.shaik** » Fri Aug 21, 2009 4:42 am

Issue is resolved

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')

Date format wasn't working.

Thanks to all for your support.

Thanks,
Elias.

chulett · Post by **chulett** » Fri Aug 21, 2009 5:26 am

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.

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.

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.