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 ?
Passing SQL statement as a parameter to OCI stage
Moderators: chulett, rschirm, roy
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.
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.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon