I explained how to pass the multiple parameters to IN clause in Pure SQL here. However, the solution needed to assign the same input string to bind parameter #val 9 times. Later on while browsing Oracle Forums, I came across a more-refined query that would address this concern.
To select employees with f_name as 'NEERAJ' or 'JACOB' or 'ROBERT', write your Pure SQL in DB Adapter like below. This would create DB schema automatically further to which you might need to specify the type of the bind parameters as xs:string in this schema.
Now, invoke the DB Adapter and using the Assign activity , assign the bind parameter values in the above query as under:
#InputString - Your Input String with delimiters (but no spaces) e.g. 'NEERAJ,JACOB,ROBERT'
#Delimiter1 - Assign the delimiter e.g. ','. This is to suffix above string with same delimiter.
#Delimiter2 - Assign the same delimiter e.g. ','
And that's it. Invoke your process and test it. Happy Learning....
Thanks for your valuable inputs.
ReplyDeletecan u share a video on this pls...
ReplyDeleteCan you please let us know the refined query ........
ReplyDeleteThat's what is shown above. Let me know if any doubts.
DeleteThis comment has been removed by the author.
ReplyDeleteOn mobile version the query is not showed, causing confusion. I clicked view as desktop and understood the post then. Thank you
ReplyDeleteHI Neeraj,
ReplyDeletei have a similar requirment ....can us hare the step by step instruction to achieve the same.
my email id sanju03@gmail.com
regards
Sanjeev
any issue do you see if we use something like this
ReplyDeleteselect * from my_table where ',param_1,param_2,param_3,param_4,' LIKE '%,'||column_name||',%'
select * from employees
WHERE ','||DELIMITED_INPUT_STRING||',' like '%,'||f_name||',%'x
Performance..
DeleteI am Trying to use same logic to update the column but i got an error:-
ReplyDeleteException occurred during invocation of JCA binding: "JCA Binding execute of Reference operation 'dbPureSQL' failed due to: Pure SQL Exception.
Pure SQL Execute of Update emp set empName = 'Hello' WHERE empID IN (SELECT SUBSTR (DELIMITED_INPUT_STRING , DECODE(LEVEL, 1, 1, INSTR(DELIMITED_INPUT_STRING, DELIMITER, 1, LEVEL-1)+1) , INSTR(DELIMITED_INPUT_STRING, DELIMITER, 1, LEVEL) - DECODE(LEVEL, 1, 1, INSTR(DELIMITED_INPUT_STRING, DELIMITER, 1, LEVEL-1)+1) ) FROM (SELECT ? || ? AS DELIMITED_INPUT_STRING , ? AS DELIMITER FROM DUAL) CONNECT BY INSTR(DELIMITED_INPUT_STRING, DELIMITER, 1, LEVEL)>0) order by empID failed.
Caused by java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
Can any one help me to resolve this problem...!
Hi Neeraj,
ReplyDeleteMay i know how to use 2 InputString in the query