Monday, May 21, 2012

Multiple IN parameters in DB Adapter Dynamic Query


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....


10 comments:

  1. Thanks for your valuable inputs.

    ReplyDelete
  2. can u share a video on this pls...

    ReplyDelete
  3. Can you please let us know the refined query ........

    ReplyDelete
    Replies
    1. That's what is shown above. Let me know if any doubts.

      Delete
  4. This comment has been removed by the author.

    ReplyDelete
  5. On mobile version the query is not showed, causing confusion. I clicked view as desktop and understood the post then. Thank you

    ReplyDelete
  6. HI Neeraj,

    i have a similar requirment ....can us hare the step by step instruction to achieve the same.

    my email id sanju03@gmail.com

    regards
    Sanjeev

    ReplyDelete
  7. any issue do you see if we use something like this

    select * 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

    ReplyDelete
  8. I am Trying to use same logic to update the column but i got an error:-

    Exception 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...!

    ReplyDelete