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
Those are the basics in db adapter.
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/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
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
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 .
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.