Wednesday, July 13, 2011

Pass multiple parameters, dynamic query in Pure SQL in Database Adapter

I came across a case in which I was required to pass the bind parameters values on the fly. The values of parameters were not fixed and hence I couldn't define all of them as the bind parameters. Hence in the search of the solution I wasted considerable time until I was directed to see this great solution. The solution was fantastic and I customized it further to make it work with SOA Suite11g. This can be further extended with Joins to do wonders as required. 


Use Case : I want the salaries of all the employees whose first names I'd pass at run time. Hence it can be single name or n names coming as parameters. The very simple query, as it comes to our mind, would be


SELECT SALARY FROM EMPLOYEES WHERE F_NAME IN (?) 
Where ? is the argument that is passed dynamically. 


However, the same query doesn't work in DB adapter for any combination passed dynamically such as ('NEERAJ', 'JACOB') or "('NEERAJ', 'JACOB')" or 'NEERAJ', 'JACOB' for the adapter tries to interpret the incoming argument as a bind parameter and not SQL string.


Solution :  If the parameters can be passed as a result of an inner query the problem can be solved. The following query uses CONNECT BY LEVEL clause to separate the individual parameter values separated by a and represents them as individual result row.


SELECT SALARY FROM EMPLOYEES WHERE F_NAME 
IN (WITH BIND_PARAM_LIST AS
(SELECT #val FROM dual)
SELECT SUBSTR(#val1, (decode(LEVEL, 1, 0, instr(#val2, ':', 1, LEVEL -1)) + 1), (decode(instr(#val3, ':', 1, LEVEL) -1, -1, LENGTH(#val4), instr(#val5, ':', 1, LEVEL) -1)) -(decode(LEVEL, 1, 0, instr(#val6, ':', 1, LEVEL -1)) + 1) + 1) a
FROM 
BIND_PARAM_LIST CONNECT BY LEVEL <=
(SELECT(LENGTH(#val7) -LENGTH(REPLACE(#val8, ':', NULL)))
FROM 
BIND_PARAM_LIST) + 1)


To understand the query one needs to be familiar with the CONNECT BY clause which is used for hierarchical queries. If you simply do 


SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=5, it prints 1 to 5 in each row like

LEVEL
=====
1
2
3
4
5


The same logic is being used in the above query to separate the various F_Names. It just sub-strings the incoming string NEERAJ:JACOB:ROBERT, wise for each LEVEL to feed the result in the IN clause as 


NEERAJ
JACOB
ROBERT


Usage : Just add this query in the DB Adapter. Be careful to use # instead of ? as in 11g the bind variable name can be defined as #bind_variable, in our case #val1, #val2 ... #val8. Also you'll have to assign the same input string to these multiple vals. The val variables get exposed as input parameters while assigning and can be assigned as 'NEERAJ:JACOB:ROBERT'(your parameter list). You can use any other separator too e.g. ; instead of : by customizing the query a bit. 

Note:The above solution requires to assign the same value to bind parameter #val 9 times. This limitation is addressed by a more-refined Query as suggested here.

Just deploy the project and it is done. The same thing can also be achieved by using query-database() function as described here.
   
      

13 comments:

  1. Hello Neeraj,

    I was trying to perform the same operation.
    we can try with the single binding_variable(#val) instead of 8 vals.

    # needs to be included at each place where val is given.

    Formated query lokks like:

    SELECT SALARY FROM EMPLOYEES WHERE F_NAME
    IN (WITH BIND_PARAM_LIST AS
    (SELECT #val FROM dual)
    SELECT SUBSTR(#val, (decode(LEVEL, 1, 0, instr(#val, ':', 1, LEVEL -1)) + 1), (decode(instr(#val, ':', 1, LEVEL) -1, -1, LENGTH(#val), instr(#val, ':', 1, LEVEL) -1)) -(decode(LEVEL, 1, 0, instr(#val, ':', 1, LEVEL -1)) + 1) + 1) a
    FROM BIND_PARAM_LIST CONNECT BY LEVEL <=
    (SELECT(LENGTH(#val) -LENGTH(REPLACE(#val, ':', NULL)))
    FROM BIND_PARAM_LIST) + 1)

    ReplyDelete
  2. Hi Shiv,

    Thanks for the suggestion.

    However, may I know which version you're trying this in? It works with one value in 10g. I tried the same in SOA Suite 11.1.1.3 and it doesn't work and throws a compilation error.

    Regards,
    Neeraj Sehgal

    ReplyDelete
  3. Hi.

    It's an awesome article!

    You have a misscount in number of times you use #val variable. It's 9 times, not 8.

    Again, thanks, you've helped a lot!

    ReplyDelete
    Replies
    1. Yes, updated. Thanks for correcting me.

      Delete
  4. HI Neerja,

    I want to use the Pure SQL for DB2 database with IN clause, how can i achieve this.

    ReplyDelete
    Replies
    1. I'm not sure but it should be same as Oracle. Can you post the error you're encountering?

      Delete
  5. Hi Neeraj,
    I am getting this kind of exception when I tried the approach.The whole query is printed in the exception trace.Can you please giude if I am doing wrong.

    Caused by Exception [EclipseLink-6003] (Eclipse Persistence Services - 2.1.2.v20101206-r8635): org.eclipse.persistence.exceptions.QueryException Exception Description: The number of arguments provided to the query for execution does not match the number of arguments in the query definition. Query: ReadAllQuery(name="GetLoadO4POrders_O4P_OHP_DBAdapter_PLSelect" referenceClass=O4P sql="SELECT t1.O4ORD#, t1.O4SUFX, t1.ODCARR, t0.OHORD# FROM FRNTST021.O4P t1 LEFT OUTER JOIN FRNTST021.OHP t0 ON (t0.OHORD# = t1.O4ORD#) WHERE (t1.O4ORD# IN (WITH BIND_PARAM_LIST AS (SELECT #val FROM dual) SELECT SUBSTR(#val1, (decode(LEVEL, 1, 0, instr(#val2, ':', 1, LEVEL -1)) + 1), (decode(instr(#val3, ':', 1, LEVEL) -1, -1, LENGTH(#val4), instr(#val5, ':', 1, LEVEL) -1)) -(decode(LEVEL, 1, 0, instr(#val6, ':', 1, LEVEL -1)) + 1) + 1) a FROM BIND_PARAM_LIST CONNECT BY LEVEL <= (SELECT(LENGTH(#val7) -LENGTH(REPLACE(#val8, ':', NULL))) FROM BIND_PARAM_LIST) + 1))"). See root exception for the specific exception. This exception is considered not retriable, likely due to a modelling mistake. ". The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution.


    Regards
    Roy

    ReplyDelete
  6. Hi Neeraj,
    I am getting this error "java.sql.SQLException: [SQL0104] Token BIND_PARAM_LIST was not valid. Valid tokens: ,." while I write SQL in DB Adapter.
    Could you please suggest me how to resolve it?

    ReplyDelete
    Replies
    1. I am using 11g and BPEL 2.0

      Delete
  7. Hi Neeraj

    can we use the same for update statement

    ReplyDelete
  8. Nice post...I look forward to reading more, and getting a more active part in the talks here, whilst picking up some knowledge as well..

    Pass Box manufacturers

    ReplyDelete
  9. Hi,
    Can we update any record using pure sql operation in db adapter.If yes,can you please provide an example.

    Thanks in Advance!!

    ReplyDelete
  10. Nice experiment with use case.I read your posts in part of my oracle SOA suite training and try more in practice.thank you.

    ReplyDelete