Error during lookup

[dsdoubt]

HI,
I have a table

CODE VARCHAR2(10) PK NOT NULL,
CODE_DESC VARCHAR2(50)

If I do a userdefined query in a OCI stage and write to a file with the following command
SELECT CODE FROM CODE_LKP
it works.

If I perform the same in a job where this stage acts as a lookup, it gives me the following error.

Code: Select all

ORA-01036: illegal variable name/number

I have marked the CODE column as PK, Not nullable, still Iam getting the above.

Hope you guys can help me.

[ArndW]

It seems that "CODE" is an invalid column name. Does the error persist if you use and different column name?

[dsdoubt]

I think I need to change the code as
SELECT CODE FROM CODE_LKP WHERE CODE = :1

where :1 is the CODE from the main stream.

Now the fatal error vanishes.

[ArndW]

Yes, that is true. I was wondering how "code" could be wrong but didn't notice that you had specified your SELECT explicitly. Usually the query that DS generates by default is good enough. Sorry about misdirecting you.

[dsdoubt]

Do you mean "Fully Generated SQL Query".
But I couldnt see any SQL, If I select that option. Is it usual?
More over can I select only few columns using this option.
Or do you have any other suggestion?

[ArndW]

I'm not sure what your question is, dsdoubt. You need to make sure that the number of columns in the query matches that defined in the stage.

[dsdoubt]

May I know, what do you mean by "query that DS generates by default"?
Is it by the option "Fully Generated SQL Query" in the Query type on SQL tab?
If that is the case, I should need to click on Build, and select the tables which I should have been imported already. Even then, the select query comes with <instance>.<tablename>, where we might change it frequently.
Sorry, Iam new to this version, so not sure about the option that has been widely used.

[ray.wurlod]

Is CODE a reserved word in your database?

[chulett]

No, it was just the classic problem when people use their own sql instead of letting the stage generate it for them. Column order doesn't match the DML or the number of columns doesn't match the number in the DML or not all values are bound or key fields aren't marked correctly, or...

What Arnd meant is that these stages will generate the SQL for you by default. You have to specifically change it to 'User Defined SQL' and I've never understood people's some fascination with doing everything as user defined. I constantly have to whack folks for doing that for no good reason.

ps. I never use the 'Fully Generated' option, perferring the 'Generate from the Columns' approach. Note that these names changed in the latest releases.

[ramdev_srh]

HI,
I have a table

CODE VARCHAR2(10) PK NOT NULL,
CODE_DESC VARCHAR2(50)

If I do a userdefined query in a OCI stage and write to a file with the following command
SELECT CODE FROM CODE_LKP
it works.

If I perform the same in a job where this stage acts as a lookup, it gives me the following error.

Code: Select all

ORA-01036: illegal variable name/number

I have marked the CODE column as PK, Not nullable, still Iam getting the above.

Hope you guys can help me.

Hi,
try to cheak the syntax for seting the primary key.
col_name(10) notnull pk.
moreever cheak where code is reserve word in oracle....

[dsdoubt]

But my worry is, when I use those option, I get <instance>.<tablename> in the generated query (as I mentioned earliar.)
If i move the job from Dev to PROD wont this be affected?

PS: CODE is just an example. sorry for the confusion.

[loveojha2]

If i move the job from Dev to PROD wont this be affected?

Surely it will, if the schemas are different.
Use Job parameters for such cases.

[ray.wurlod]

#SchemaName#.TableName

Never make the table name a job parameter. Following this advice means that things like lineage analysis and usage analysis will work properly.

[chulett]

Also consider #SchemaName#Tablename where the value of the schema name includes the dot.

Allows you to pass an empty schema name to optionally take advantage of synonyms or local tables.

[dsdoubt]

For the same reason of using parameter for the SchemaName, i switched to user defiend query.
Is there a way to make datastage to handle by it self?