Using Oracle cursors in WebSphere DataPower SOA Appliances

WebSphere DataPower SOA Appliances are purpose-built, easy-to-deploy network devices that simplify, secure, and accelerate XML and Web services deployments within an SOA. The WebSphere DataPower XI50 Integration Appliance can efficiently communicate with enterprise relational databases such as Oracle, and serve as a broker in an Information as a Service (IaaS) pattern. This article focuses on how the XI50 handles the return value of Oracle cursors, and shows you how to create a stored procedure with a reference cursor output parameter, call the stored procedure, and handle the return value.

Valerie Bennett (vmbennet@us.ibm.com), Software Engineer, IBM

Photo of Valerie Bennett Valerie Bennett is a Software Engineer for IBM Pervasive Computing in Research Triangle Park, NC. She has held various software development and project management positions in her 3-year career with IBM. Valerie has a BS degree in Applied Science from the University of North Carolina at Chapel Hill. You can contact her at vmbennet@us.ibm.com.



Keith Barber (barberk@us.ibm.com), Software Engineer, WebSphere DataPower SOA Appliance Development Team, IBM

Photo of Keith BarberKeith Barber is a Software Engineer on the WebSphere DataPower SOA Appliance Development Team, specializing in enterprise integration. He is the primary developer for the SQL feature on the DataPower XI50 Appliance and also contributed to the DataPower XB60 Appliance. Before joining the DataPower Team, he worked on the IBM Tivoli Autonomic Computing and Test Automation Teams. He has a BS in Computer Science Engineering from Michigan State University.



30 June 2010

Introduction

In database packages, a cursor is a structure that enables traversing records in a result set. Oracle/PL SQL Cursors are defined as a SELECT statement within the declaration scope of the PLSQL code. The IBM® WebSphere® DataPower® XI50 Appliance supports Oracle cursors by reading in the entire cursor return value, and converting it to XML. The XI50 does not support manipulation of Oracle cursors. This article shows you step-by-step how to use Oracle cursors in the WebSphere DataPower XI50 Appliance.

Step 1. Define the package

Database packages are conceptually linked cursors, functions, procedures, variables, PLSQL statements, and so on that promote reuse of data in the database. Listing 1 is an example of how to create a database package in Oracle:

Listing 1. Package definition
create or replace PACKAGE  "MYPACKAGE"
IS
   CURSOR G1 is select key_column from myXMLTable;
   TYPE CursorType IS REF CURSOR return G1%rowtype;
END MyPackage;

Step 2. Define a stored procedure with a reference cursor output parameter

Stored procedures are a mainstay in databases due to the procedural roots of enterprise architectures. Stored procedures enable the reuse of SQL statements. Once the database package is defined, you can use the Oracle cursor in a stored procedure.

Listing 2. Stored procedure definition
create or replace PROCEDURE   "GET_RESULT_SET"
   (outCursor IN OUT MyPackage.CursorType) AS
BEGIN
   open outCursor for select key_column from myXMLTable;
END;

select key_column from myXMLTable can be an arbitrary select query, as long as it is consistent with the package cursor definition. The cursor in this example is a reference cursor returned via an output parameter, which is the only kind of cursor that DataPower supports (forward-only via an output parameter).

Step 3. Invoke the stored procedure

You can execute stored procedures on the WebSphere DataPower XI50 Appliance by using the <dp:sql-execute> extension element in an XSL stylesheet:

Listing 3. Invoking stored procedure
   <xsl:variable name="statement">{call get_result_set}</xsl:variable>
<dp:sql-execute statement="$statement" source="'first'">
<arguments>
   <argument mode="OUTPUT"/>
</arguments>
</dp:sql-execute>

There are two important distinctions in this syntax used above:

  1. There are no ? parameter markers in the SQL statement.
  2. There is an empty <argument mode="OUTPUT"/> element.

Normally there is a one-to-one correlation between ? parameters markers and <argument>elements, but this is not the case for reference cursors.

Example return code

The cursor return value is read in and returned as an XML structure. Listing 4 is an example of the output:

Listing 4. Example return output
<sql result="success">
<resultSet>
<metadata>
<columnInfo index="1" name="KEY_COLUMN" nullable="false" precision="10" scale="0" 
    type="SQL_VARCHAR"></columnInfo></metadata>
<row>
<column>
<name>KEY_COLUMN</name>
<value>Michalis</value></column></row>
<row>
<column>
<name>KEY_COLUMN</name>
<value>oracle</value></column></row>
<row>
<column>
<name>KEY_COLUMN</name>
<value>panagiotis</value></column></row>
<row>
<column>
<name>KEY_COLUMN</name>
<value>panos</value></column></row>
<row>
<column>
<name>KEY_COLUMN</name>
<value>xml2</value></column></row>
<row>
<column>
<name>KEY_COLUMN</name>
<value>xquery</value></column></row>
<row>
<column>
<name>KEY_COLUMN</name>
<value>xquery2</value></column></row></resultSet></sql>

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=498780
ArticleTitle=Using Oracle cursors in WebSphere DataPower SOA Appliances
publish-date=06302010