Handling REF CURSOR in SOA Layer

Refer to my previous post on handling strong & weak REF CURSOR output in PLSQL.

In this post, let's discuss about handling REF CURSOR output in SOA layer.

Consider following sample procedure having weak REF CURSOR as output. Compile this procedure in database.

CREATE OR REPLACE PROCEDURE PROC_WEAK_REF_CUR(
                                                in_dept_id  IN  DEPARTMENTS.DEPARTMENT_ID%TYPE,
                                               out_ref_cur OUT SYS_REFCURSOR)
IS
BEGIN
       OPEN    out_ref_cur FOR
       SELECT EMP.*
       FROM   EMPLOYEES EMP,
                     DEPARTMENTS DEPT,
                     LOCATIONS LOC
       WHERE  EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID
       AND       DEPT.LOCATION_ID = LOC.LOCATION_ID
       AND       DEPT.DEPARTMENT_ID = in_dept_id;
END;

Refer following steps to convert Weak XSD to Strong XSD:

• Create a sample SOA composite application in JDeveloper.
• Open composite.xml and Drag Database adapter to External References lane.
• Choose Operation Type 'Call a Stored Procedure or Function'
• In the next step 'Specify Stored Procedure', click on Browse and select the procedure given above. Here verify the output parameter type.

•  In the next step 'RowSet', by default, database adapter generates Weak XSD as shown below. 

• The XML output from Weak XSD is hard to use. It is very difficult to write an Xpath expression or XSL based on a weakly typed XSD and column names as attribute values instead of element names.

• To convert Weak XSD to Strong XSD, enter valid value for input parameters. Click on Introspect.

• If the stored procedure or function returns a row set with at least 1 row, the RowSets page is updated to display a strongly typed XSD in the XSD text field a shown below. 

• In case of Strongly typed XSD, it's easy to identify elements and write an transformation map.