I need to create a function that takes a list of integers (StudentId’s) as input and returns a list of Students. I found this link. It works but it breaks on a parameter that contains the delimiting character so it’s not ideal.

Database Objects

For this solution we need three Oracle user defined types and a function. It relies on objects in the database.

-- A VARRAY of Integers used for the IN parameter of the function:

create or replace
TYPE int_array AS TABLE OF integer;

-- An Object describing a Person:

create or replace
type pers_row_type as object 
(persno integer
,firstname varchar2(10)
,lastname varchar2(30)
);

-- A VARRAY of pers_Row_Type used as the out parameter:

create or replace
TYPE pers_tab_type IS TABLE OF pers_Row_Type;

-- Create the function:

create or replace
function list_persons (perslist int_array) return pers_tab_type
is
  v_tab pers_tab_type := pers_tab_type();
begin
  for cur_row in (select * 
                  from person
                  ,    table(cast(perslist as int_array)) dum
                  where person.persno = dum.column_value)
                  
  loop
    v_tab.extend;
    v_tab(v_tab.last) := pers_row_type(cur_row.persno,cur_row.first_name,cur_row.last_name);
  end loop;
  return v_tab;
end;

Create Project

Open JDeveloper and start a new Project
Create a SOA project : SOAInListDemo

Use empty composite template
Finish

Create Database Adapter

Pull the component “Database Adapter” into the “External References” swimming lane

Give it a name : getStudentsByIds
Select connection to the database where you created the function
Speficy JNDI Name : eis/DB/dev11col
Select : “Call a Stored Procedure or Function”

Click browser and select the LIST_PERSONS function

Next|Finish
A WSDL and a XSD file are created with the schema definitions for the Oracle Types of the IN and OUT parameters

Create the Mediator

Pull the component “Mediator” into the middle “Components” swimming lane

Give it a name : “PersonsMediator”
Select from Template the option “Interface Definition from WSDL
To the right of the field WSDL URL click the icon “Find existing WSDL’s”
Select the file generated from the database adapter : getStudentsByIds.wsdl

Connecting the components

Connect the Mediator to the Database Adapter

Transformation

In the mediator we connect the source and target messages
Open the mediator

Click the icon right of the “transform Using” fields
Start with the request
Create new mapper file
Connect elements and insert XSLT construct “for each” as shown below

Open response mapper file
Connect elements and insert XSLT construct “for each” as shown below

Deploy and test

Request

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:list="http://xmlns.oracle.com/pcbpel/adapter/db/COL/LIST_PERSONS/">
   <soapenv:Header/>
   <soapenv:Body>
      <list:InputParameters>
         <!--Optional:-->
         <list:PERSLIST>
            <!--Zero or more repetitions:-->
            <list:PERSLIST_ITEM>1</list:PERSLIST_ITEM>
            <list:PERSLIST_ITEM>2</list:PERSLIST_ITEM>
            <list:PERSLIST_ITEM>3</list:PERSLIST_ITEM>
            <list:PERSLIST_ITEM>4</list:PERSLIST_ITEM>
         </list:PERSLIST>
      </list:InputParameters>
   </soapenv:Body>
</soapenv:Envelope>

Response

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing">
   <env:Header>
      <wsa:MessageID>urn:35D50010459911E1BF388DF20C4C6D71</wsa:MessageID>
      <wsa:ReplyTo>
         <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>
         <wsa:ReferenceParameters>
            <instra:tracking.ecid xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">0000JK9abd4Fw000jzwkno1F62iL000puA</instra:tracking.ecid>
         </wsa:ReferenceParameters>
      </wsa:ReplyTo>
   </env:Header>
   <env:Body>
      <db:OutputParameters xmlns:db="http://xmlns.oracle.com/pcbpel/adapter/db/COL/LIST_PERSONS/">
         <db:LIST_PERSONS>
            <db:LIST_PERSONS_ITEM>
               <db:PERSNO>1</db:PERSNO>
               <db:FIRSTNAME>Rob</db:FIRSTNAME>
               <db:LASTNAME>van Laarhoven</db:LASTNAME>
            </db:LIST_PERSONS_ITEM>
            <db:LIST_PERSONS_ITEM>
               <db:PERSNO>2</db:PERSNO>
               <db:FIRSTNAME>Bas</db:FIRSTNAME>
               <db:LASTNAME>Jansen</db:LASTNAME>
            </db:LIST_PERSONS_ITEM>
            <db:LIST_PERSONS_ITEM>
               <db:PERSNO>3</db:PERSNO>
               <db:FIRSTNAME>Mark</db:FIRSTNAME>
               <db:LASTNAME>Schenk</db:LASTNAME>
            </db:LIST_PERSONS_ITEM>
            <db:LIST_PERSONS_ITEM>
               <db:PERSNO>4</db:PERSNO>
               <db:FIRSTNAME>Koen</db:FIRSTNAME>
               <db:LASTNAME>van Dijk</db:LASTNAME>
            </db:LIST_PERSONS_ITEM>
         </db:LIST_PERSONS>
      </db:OutputParameters>
   </env:Body>
</env:Envelope>

Conclusion

The downside of this solution is that we have to manage (different versions of) Oracle database objects . But we can use the the power of PL/SQL procedures and functions. Every function and procedure in the database can be accessed using Web Services.