4 Replies Latest reply: Nov 15, 2012 2:11 PM by RVD RSS

    Execute a query in Jdeveloper using DB adpater

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