CODEWITHSUNDEEP
javasqloracle-databaseoracle-adf
Rahul Gulwani
Rahul Gulwani

Reputation: 200

how to set the value of Bind variable in SQL query inside IN clause

I have binded a variable in my view Object. I am dynamically changing the bind variable and executing the View Object. the issue is that i suspect that bind variable is taking some '' or "".

I have debugged the issue and also try to print the query. But still inside the bebugger, i am getting the same query including the bind variable.

my query

SELECT APFileHeaderEO.ACTIVE,     
       APFileHeaderEO.CUSTOM_HEADER,     
       APFileHeaderEO.ENT_BY,     
       APFileHeaderEO.ENT_DATE,     
       APFileHeaderEO.FEEDER_ID,     
       APFileHeaderEO.FILE_HEADER_ID,     
       APFileHeaderEO.FILE_NAME,     
       APFileHeaderEO.MODIFY_BY,     
       APFileHeaderEO.MODIFY_DATE,     
       APFileHeaderEO.NEW_FILE_NAME,     
       APFileHeaderEO.SOURCE_SYSTEM,     
       APFileHeaderEO.STATUS,     
       APFileHeaderEO.TOTAL_RECORD_COUNTS,     
       APFileHeaderEO.COMMENTS,     
       APFileHeaderEO.CONTROL_TOTAL,     
       APFileHeaderEO.ACCOUNTING_DATE,     
       APFileHeaderEO.GENERIC_HEADER_ID,     
       APFileHeaderEO.GL_DATE,     
       APFileHeaderEO.GROSS_TOTAL_AMOUNT,     
       APFileHeaderEO.INSTANCE_ID,     
       APFileHeaderEO.INVOICE_RECEIVED_DATE,     
       APFileHeaderEO.NET_TOTAL_AMOUNT,     
       APFileHeaderEO.NUMBEROFINVOICES,     
       APFileHeaderEO.SOURCE,     
       APFileHeaderEO.SOURCE_FILE_PRODUCE_DATE,     
       APFileHeaderEO.VAT_TOTAL_AMOUNT,     
       APFileHeaderEO.SOURCE_FILE_NAME    
FROM  EI_AP_FILE_HEADER APFileHeaderEO where APFileHeaderEO.source in (NVL(:source1,APFileHeaderEO.source))

java code //source is a String variable

// for example source ="'LCC','FIN','AR','CandD','Transactions','Creator'";

apFileHeaderVO.setsource1(source.toString());

apFileHeaderVO.executeQuery();

Upvotes: 0

Views: 1062

Answers (1)

Rahul Gulwani
Rahul Gulwani

Reputation: 200

I finally found the result. It can be archived using connect by and regular expresion

SELECT APFileHeaderEO.ACTIVE,     
       APFileHeaderEO.CUSTOM_HEADER,     
       APFileHeaderEO.ENT_BY,     
       APFileHeaderEO.ENT_DATE,     
       APFileHeaderEO.FEEDER_ID,     
       APFileHeaderEO.FILE_HEADER_ID,     
       APFileHeaderEO.FILE_NAME,     
       APFileHeaderEO.MODIFY_BY,     
       APFileHeaderEO.MODIFY_DATE,     
       APFileHeaderEO.NEW_FILE_NAME,     
       APFileHeaderEO.SOURCE_SYSTEM,     
       APFileHeaderEO.STATUS,     
       APFileHeaderEO.TOTAL_RECORD_COUNTS,     
       APFileHeaderEO.COMMENTS,     
       APFileHeaderEO.CONTROL_TOTAL,     
       APFileHeaderEO.ACCOUNTING_DATE,     
       APFileHeaderEO.GENERIC_HEADER_ID,     
       APFileHeaderEO.GL_DATE,     
       APFileHeaderEO.GROSS_TOTAL_AMOUNT,     
       APFileHeaderEO.INSTANCE_ID,     
       APFileHeaderEO.INVOICE_RECEIVED_DATE,     
       APFileHeaderEO.NET_TOTAL_AMOUNT,     
       APFileHeaderEO.NUMBEROFINVOICES,     
       APFileHeaderEO.SOURCE,     
       APFileHeaderEO.SOURCE_FILE_PRODUCE_DATE,     
       APFileHeaderEO.VAT_TOTAL_AMOUNT,     
       APFileHeaderEO.SOURCE_FILE_NAME    
FROM  EI_AP_FILE_HEADER APFileHeaderEO where APFileHeaderEO.source in (select trim('''' from regexp_substr(:source1,'[^,]+', 1, level))     
from dual     
connect by     
regexp_substr(:source1, '[^,]+', 1, level)
is not null);

Upvotes: 1

Related Questions