Parameterizing SQL in oracle Enterprise Stage

[somu_june]

Hi,

Iam passing user defined SQL through a job parameter and statement looks like this psql (Parameter) string and its default value = SELECT * from EMP where EMP_NO = 'EMPNO~Seq'

and in oracle stage Read Method = user-defined and in SQL Query = #psql#.

When this job runs it is failing by throwing the following error

ORAR_EMP_TABLE: GenericQuery:esqlErrorHandler
Prepare failed for: GenericStmt_1

SELECT * from EMP where EMP_NO = EMPNO~Seq
sqlcode is: -911

esql complaint: ORA-00911: invalid character


When I checked above statement from log . Oracle stage is skipping single quotes around EMPNO~Seq. When I re checked the parameter value it has single quotes but when it is passed to the stage single quotes were discarded. Can some one tell me how to solve this problem.

Thanks,
Somaraju.

[somu_june]

My problem was solved by placing \' and \'

before \'EMPNO~Seq \'




Thanks,
Somaraju.

[chulett]

Yup, they need to be "escaped" to be preserved.

[dsuser_cai]

Thats right, also make sure that when you have single quote (') you use forward slashes (/).

[ray.wurlod]

Forward slashes? I don't think so.

[jaganmo]

I encountered a similar problem but the \' '\ didn't work for me.

This is my query

select coln from table where coln = \'xyz\'

my where clause changes dynamically in the above query containing one or more expression check depending on run time conditions.

Please suggest any work around.

[chulett]

Suggest you start your own thread.