subquery from file in oci stage

bobbysridhar · Post by **bobbysridhar** » Fri Aug 03, 2012 8:54 am

Hi,
I want to run the following query in OCI stage.

select a1.idx,a1.namex,a1.creationdatex,a1.specialhandlingx,
a1.CONFIDENTIALX,b.codex from accountjdox a1,
brokeragencyjdox b
where a1.defaultbroker_fkx=b.idx and a1.idx in(#Dir#/Accountfile.txt)

I opt for read sql query from file as NO.
I have query to select Accountnumbers in Accountfile which is in UNIX and it will change frequenty.
when I test for it it is giving the error - missing expression.
Could somebody please help

Thanks,
Sridhar

ArndW · Post by **ArndW** » Fri Aug 03, 2012 9:34 am

In cases like this the only way to get a good answer is to cut-and-paste the entire error message, those two words "missing expression" aren't quite enough information.

chulett · Post by **chulett** » Fri Aug 03, 2012 9:40 am

Simple answer... you cannot use parameters in a SQL file.

ArndW · Post by **ArndW** » Fri Aug 03, 2012 9:45 am

bobbysridhar · Post by **bobbysridhar** » Fri Aug 03, 2012 9:56 am

I am not using parameter in file.
I am writing a query and in that there is subquery which is from file.

ther error message is
The OCI function OCIStmtExecute returned status-1. Error code:936, Error message: ORA-00936; missing expression

chulett · Post by **chulett** » Fri Aug 03, 2012 10:03 am

bobbysridhar wrote:I am not using parameter in file.

Yes, you are - hence my answer.

Code: Select all

 in(#Dir#/Accountfile.txt)

#Dir# is the parameter.

bobbysridhar · Post by **bobbysridhar** » Fri Aug 03, 2012 10:12 am

the sql query i posted, i am using it as user defined sql.
i am not reading sql from file
in user defined sql i am calling file which has accountnumber with paramerter/filename.txt.
we use parameters in user defined sql
and that is what i am doing

ArndW · Post by **ArndW** » Fri Aug 03, 2012 10:18 am

Then back to my request for the full error message.

bobbysridhar · Post by **bobbysridhar** » Fri Aug 03, 2012 10:19 am

the error message is
The OCI function OCIStmtExecute returned status-1. Error code:936, Error message: ORA-00936; missing expression

chulett · Post by **chulett** » Fri Aug 03, 2012 11:21 am

OK, didn't look close enough at the SQL. Oracle won't automagically import that file data into your query for the subselect. You'll need to pass the entire "in list" in as a parameter.

bobbysridhar · Post by **bobbysridhar** » Fri Aug 03, 2012 12:13 pm

but that 'in list' is dynamic and business want to provide the 'in list' query dynamically whatever they want.
thats why I want to give them a file in Unix to put their query in that file.
is there a way I can acheive this

chulett · Post by **chulett** » Fri Aug 03, 2012 12:58 pm

As I said, pass it in as a job parameter. Use something in a Sequence job to read the file and craft the string that needs to be passed - a routine can be used or it may be as simple as an Execute Command stage that does a 'cat' and passes the result to the parameter. All depends on the structure of the data in the file.