This discussion is archived
4 Replies Latest reply: Nov 15, 2012 12:11 PM by RVD RSS

Execute a query in Jdeveloper using DB adpater

923776 Newbie
Currently Being Moderated
Hi All ,

In jdeveloper, using DB adapter i want to execute the following query ,

select segment1,eng_item_flag from table_name where segment1 in ('itemnumber1',’itemnumber2’);

In the query the operation IN got used so could you please help me to proceed further
  • 1. Re: Execute a query in Jdeveloper using DB adpater
    Eric Elzinga Oracle ACE
    Currently Being Moderated
    i don't really understand what the problem is, can you explain a bit more ?
    you can just use the db adapter, use the 'select' option and paste the sql statement in it, right ?
  • 2. Re: Execute a query in Jdeveloper using DB adpater
    veejai24 Pro
    Currently Being Moderated
    Those are the basics in db adapter.

    http://docs.oracle.com/cd/E11036_01/integrate.1013/b28994/adptr_db.htm#CHDCFCJI

    In the above link, check the heading "*4.3.4 Selecting the Operation Type*", in that check the select query option.
    If you go down, you can see "*Figure 4-15 Adapter Configuration Wizard: Define WHERE Clause*", section and you can see DeptParam is a parameter that is passed into the query below.

    The same way you can use for your usecase.
    Let us know how you go. If you encounter any issue paste the exception.

    Thanks,
    Vijay
  • 3. Re: Execute a query in Jdeveloper using DB adpater
    923776 Newbie
    Currently Being Moderated
    Thanks vijay/Eric for your response .

    I went though the link , tried the same and could not find the operator "IN" .

    If i select the operator "=" then it works but my requirement is to pass multipe values usng "IN" operator

    For ex request xml would be like this

    <partnumber>1,2,3,4,5.........n</partnumber>

    Note : multiple partnumber comes as comma seperator value in single tag

    once Bpel receives the request xml , it has to fetch the corresponding attributes for the partnumbers from data base

    query
    -------

    select attributes,attributes2 from table name where partnumber in (#partnumber) . -> Value of #partnumber will be = 1,2,3,4,5...n


    The response i need to send to calling client , how to acheive this in data base adapter .
  • 4. Re: Execute a query in Jdeveloper using DB adpater
    RVD Newbie
    Currently Being Moderated
    DB adpater wraps the parameter by ' (apostrophe), so using IN clause it becomes '1,2,3,4,5.........n' and the query returns nothing.

    You need to use below query in DB Adpater

    select attributes,attributes2 from table name where partnumber in (WITH VALUE_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 VALUE_LIST CONNECT BY LEVEL <=
    (SELECT(LENGTH(val) -LENGTH(REPLACE(val, ',', NULL)))
    FROM VALUE_LIST) + 1)

    Value of <partnumber> tag can be mapped to DB Adapter Inputvariable.

    If you create a very big value list for IN query (greater than 4000 characters) you will get ORA-01704: string literal too long error. In that case you need to break value list and invoke this query multiple times.

    Thanks
    Ravdeep

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points