Lookup Generating ORA-01036: illegal variable name/number

[dtatem]

Hi all:

I am performing a lookup based on the following SQL which is UserDefined from Table and columns instead of the "Generated SQL Query". DataBase is Oracle.

SELECT
CODE_LOOKUP_TYPE.CD_LKP_TYPE_ID,
CODE_LOOKUP_TYPE.CD_LKP_TYPE_CD
FROM ODSR1.CODE_LOOKUP_TYPE WHERE CODE_LOOKUP_TYPE.CD_LKP_TYPE_CD in (#pListValues#)

I created a parameter called pListValues. I am using a Sequencer to run the job. In the Sequencer, the first stage is the UserVariables----> JobActivity.

When the job runs, the parameter #pListValues# gets substituted as seen below:The values are defined in the sequencer

SELECT
CODE_LOOKUP_TYPE.CD_LKP_TYPE_ID,
CODE_LOOKUP_TYPE.CD_LKP_TYPE_CD
FROM ODSR1.CODE_LOOKUP_TYPE WHERE CODE_LOOKUP_TYPE.CD_LKP_TYPE_CD in ('STU930','STU940')

Now when the job runs, I get the following error message
ORA-01036: illegal variable name/number

I did a search for this error in the forum, but nothing seems to resolve it

Thanks in Advance,
dtatem

[ray.wurlod]

What are the data types of the two columns mentioned, both in Oracle as as used in DataStage?

[chulett]

Search more better, we've talked about this more than once.

That's one of the dangers of 'user defined' sql, especially when you don't understand the rules required by the stage. Check some things for a reference lookup:

1. You must select the same number of fields as you have columns defined in the stage and select them in the same order.
2. Fields marked as Key fields must be referenced in the 'where' clause with the appropriate numbered parameter marker.

So, for this you would need two fields defined with neither marked as a key. Is that what you have done?

[dtatem]

Hi all:

I am performing a lookup based on the following SQL which is UserDefined from Table and columns instead of the "Generated SQL Query". DataBase is Oracle.

SELECT
CODE_LOOKUP_TYPE.CD_LKP_TYPE_ID,
CODE_LOOKUP_TYPE.CD_LKP_TYPE_CD
FROM ODSR1.CODE_LOOKUP_TYPE WHERE CODE_LOOKUP_TYPE.CD_LKP_TYPE_CD in (#pListValues#)

I created a parameter called pListValues. I am using a Sequencer to run the job. In the Sequencer, the first stage is the UserVariables----> JobActivity.

When the job runs, the parameter #pListValues# gets substituted as seen below:The values are defined in the sequencer

SELECT
CODE_LOOKUP_TYPE.CD_LKP_TYPE_ID,
CODE_LOOKUP_TYPE.CD_LKP_TYPE_CD
FROM ODSR1.CODE_LOOKUP_TYPE WHERE CODE_LOOKUP_TYPE.CD_LKP_TYPE_CD in ('STU930','STU940')

Now when the job runs, I get the following error message
ORA-01036: illegal variable name/number

I did a search for this error in the forum, but nothing seems to resolve it

Thanks in Advance,
dtatem

Ray - to answer your question, the data types in Oracle and Datastage
are varchar(6) and this column is a key column. I am doing a lookup and joining on this key column by passing the parameter (#pListValues#)..

[chulett]

And? The data types have nothing to do with your issue.

[arun_kumar]

HI

I think you want to pass the value to that query.For that you just specify '#Variable#' .try like this ...

[vijay.barani]

Hi Friends,
I got the same issue "ORA-01036".But In my simpe job,I have source drs stage(4 columns) and one lookup(2 columns).In the transformer stage(to which the lookup is added) I made relation between source and lookup for one column.
I have no user-defined query neither any variables defined !!
Even then facing the issue.May i have some help in this regard

[sureshreddy2009]

:D
Hi Sir,
you first check the following steps
1.columns selected at user defined query and at columns tab must be equal.
2.according to your error it is not accepting the parameter value in Where clause.so first check whether it is project level parameter or job level
2.1.if project level, then use the query like this, select (required columns) from table_name where column in ('#$parameter#')
2.2.if the parameter is job level use the query like , select (required columns) from table_name where column in ('#parameter')

May be this will help you to some percent

[sureshreddy2009]

if that is local then '#parameter#'

[chulett]

I have no user-defined query neither any variables defined !!

All of the advice noted above still applies. The number and order of columns defined in the stage must match the number and order of columns selected in the query and every column bound into the 'where' clause must be marked as a Key. Period. Somehow you're not doing that.

[chulett]

First note down,variable is project level or job level

I have no user-defined query neither any variables defined !! (emphasis mine)

[wahi80]

I think the problem is with the parameters
Ensure that all ' are replaced by\' before being read by oracle stage
i.e your Where condition should read like this WHERE CODE_LOOKUP_TYPE.CD_LKP_TYPE_CD in (\'STU930\',\'STU940\')

For testing purposes change condition to do a test on a number where quotes not required, if that passess then the error is definitely in the parameters

Regards
Wah

[wahi80]

I think the problem is with the parameters
Ensure that all ' are replaced by\' before being read by oracle stage
i.e your Where condition should read like this WHERE CODE_LOOKUP_TYPE.CD_LKP_TYPE_CD in (\'STU930\',\'STU940\')

For testing purposes change condition to do a test on a number where quotes not required, if that passess then the error is definitely in the parameters

Regards
Wah

[chulett]

Now you are responding to the original poster's question rather than the 'hijack' by Suresh, which is what we are currently addressing.

People, please start your own post even when it seems like you have "the same problem" as someone else. You don't, you have your own perhaps similar problem, but it's still yours and for that we need to know your version, your O/S, your specific symptoms, etc etc. And that way you can mark the thread Resolved when your question is answered, something that cannot be done here.

ps. If you really think it's just like someone else's problem, link to it, don't post into it.