Passing SQL statement as a parameter to OCI stage

[pjangiti]

I have parametrized the source SQL in oci stage and that is functioning properly when the SQL is having simple select statement ,

but when i have a where clause with in the source sql with string comparison like

select abc, xyz from table1 where xyz='NEW'

iam able to view the data from the job but when i run the job iam getting invalid identifier NEW error

from the log i could see that when i run the job the OCI stage is trying to exacute

select abc, xyz from table1 where xyz=NEW instead of

select abc, xyz from table1 where xyz='NEW'

Notes :

1)select abc, xyz from table1 where xyz='NEW'
is a valid sql when i run this query from winsql/PL SQL Developer.

2)The job parameter through which iam passing the sql is defined as string type.

3)The job is running fine when sql do not contain any string comparisons.
eg : select abc, xyz from table1 where abc=8

abc datatype is interger and xyz datatype is varchar


Question :
Will Data stage allows quotes in the job parameter value when the parameter is of staring data type ?
If yes why is it not taking quotes in where condition when passing the sql to the stage ?

[BI-RMA]

Hi pjangiti,

when You pass the complete SQL to DataStage as a string, DataStage removes quotes from the string as standard procedure. The result is the wrong SQL and the error You see in the job log.

You have to mask the quotes by the escape character (generally \ on Unix) to keep them in your string.

[pjangiti]

Thanks Roland Melzer ,

Your suggestion to mask the quotes helped me resolve the issue.