bea.com | products | dev2dev | support | askBEA

Download Docs | Site Map | Glossary
Search

Building Queries and Data Views

Sample Stored Procedure Description Files

This section shows several sample Stored Procedure Description files. For simplicity and readability, each of the examples shown defines a single stored procedure and its supporting type; your Stored Procedure Description files can define multiple stored procedures and multiple types. This section includes the following examples:

- DB2 Simple input_only, output_only, and input_output Example

- Oracle Cursor Output Parameter Example

- DB2 Multiple Result Set Example

- Oracle Cursor as return_value

DB2 Simple input_only, output_only, and input_output Example

The following Stored Procedure Description file describes a DB2 stored procedure that returns simple data types with input_only, output_only, and input_output parameters.

>     <?xml version="1.0" encoding="UTF-8"?>
>     <definitions>
>     	<types>
>     		<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
>     			<xs:element name="EmptyOutput">
>     				<xs:complexType>
>     					<xs:sequence>
>     					</xs:sequence>
>     				</xs:complexType>
>     		</xs:schema>
>     	</types>
>     	<functions>
>     			<!-- given a customer id if valid, returns as output
>                       parameters all  the customer details -->
>     			<function name="CALLINCALLOUT" return_type="EmptyOutput">
>     				<argument label="custid" mode="input_only" type="xs:string"/>
>     				<argument label="fname" mode="output_only" type="xs:string"/>
>     				<argument label="lname" mode="output_only" type="xs:string"/>
>     				<argument label="telephoneNumber" mode="output_only"
>                                     type="xs:long"/>
>     				<argument label="customerSinceAsData" mode="output_only"
>                                     type="xs:date"/>
>     				<argument label="customerSinceAsTimeStamp"
>                                     mode="output_only" 	type="xs:dateTime"/>
>     			<presentation group="DB2 stored procedures"/>
>        		      </function>
>     	</functions>
>     </definitions>

The following is the stored procedure signature for this Stored Procedure Description file. This signature creates a procedure that has one simple input and returns five simple outputs.

>     CREATE PROCEDURE DB2ADMIN.CALLINCALLOUT (
>             IN CUSTID varchar(64), OUT FNAME varchar(4000),
>             OUT LNAME varchar(4000), OUT TELEPHONENUMBER bigint,
>             OUT CUSTOMERSINCE date, OUT CUSTOMERSINCE1 timestamp )
>     EXTERNAL NAME
>         '"DB2ADMIN".SQL30205005750980:db2test.CallInCallOut.callInCallOut'
>     SPECIFIC DB2ADMIN.CALLINCALLOUT
>     RESULT SETS 0
>     LANGUAGE JAVA
>     PARAMETER STYLE JAVA
>     NOT DETERMINISTIC
>     FENCED NO
>     DBINFO NULL
>     CALL MODIFIES SQL DATA

Oracle Cursor Output Parameter Example

The following Stored Procedure Description file describes an Oracle stored procedure that returns an output parameter as a cursor.

>     <?xml version="1.0" encoding="UTF-8"?>
>     <definitions>
>     	<types>
>     			<xs:element name="OutCursor">
>     				<xs:complexType>
>     					<xs:sequence>
>     						<xs:element name="CUSTOMER" minOccurs="0"
>                                             maxOccurs="unbounded">
>     							<xs:complexType>
>     								<xs:sequence>
>     									<xs:element name="C_CUSTKEY" type="xs:integer"/>
>     									<xs:element name="C_FNAME" type="xs:string"/>
>     									<xs:element name="C_LNAME" type="xs:string"/>
>     									<xs:element name="C_STATE" type="xs:string"/>
>     								</xs:sequence>
>     							</xs:complexType>
>     						</xs:element>
>     					</xs:sequence>
>     				</xs:complexType>
>     			</xs:element>
>     			<xs:element name="Output">
>     				<xs:complexType>
>     					<xs:sequence/>
>     				</xs:complexType>
>     			</xs:element>
>     		</xs:schema>
>     	</types>
>     	<functions>
>     		<function name="TEST_PACKAGE.GETCUSTOMER" return_type="Output">
>     			<argument label="CUSTID" mode="input_only" type="xs:string"/>
>     			<argument label="customer_OUT" mode="output_only"
>                                 type="OutCursor"/>
>     			<presentation group="OR-TEST stored procedures"/>
>     		</function>
>     	</functions>
>     </definitions>

The following is the stored procedure signature for this Stored Procedure Description file. This signature creates a procedure that returns a cursor as an output parameter.

>     create or replace package test_package as
>     -- Stored procedure that returns a cursor as an output parameter
>         procedure getCustomer
>             ( CUSTOMERID IN VARCHAR, cust_cursor1 OUT ref_cursor );
>     end test_package ;

DB2 Multiple Result Set Example

The following Stored Procedure Description file describes a DB2 stored procedure that returns multiple result sets.

>     <?xml version="1.0" encoding="UTF-8"?>
>     <definitions>
>     	<types>
>     		<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
>     		 <xs:element name="CustomerAndOrders">
>     				<xs:complexType>
>     					<xs:sequence>
>     							<xs:element ref="resultSetCustomer"/>
>     							<xs:element ref="resultSetCustomerOrders"/>
>     					</xs:sequence>
>     				</xs:complexType>
>     			</xs:element>
>     			<xs:element name="resultSetCustomer">
>     				<xs:complexType>
>     					<xs:sequence>

>     						<xs:element name="customerRow" minOccurs="1"
>                                           maxOccurs="unbounded">
>     							<xs:complexType>
>     								<xs:sequence>
>     									<xs:element name="C_CUSTKEY" type="xs:string"/>
>     									<xs:element name="C_FNAME" type="xs:string"/>
>     									<xs:element name="C_LNAME" type="xs:string"/>
>     									<xs:element name="C_STATE" type="xs:string"/>
>     									<xs:element name="C_SINCE" type="xs:date"/>
>     									<xs:element name="C_TELEPHONENO" type="xs:long"/>
>     								</xs:sequence>
>     							</xs:complexType>
>     						</xs:element>
>     					</xs:sequence>
>     				</xs:complexType>
>     			</xs:element>

>     			<xs:element name="resultSetCustomerOrders">
>     				<xs:complexType>
>     					<xs:sequence>
>     						<xs:element name="orderRow" minOccurs="1"
>                                           maxOccurs="unbounded">
>     							<xs:complexType>
>     								<xs:sequence>
>     									<xs:element name="C_CUSTKEY" type="xs:string"/>
>     									<xs:element name="CO_ORDERKEY" type="xs:string"/>
>     									<xs:element name="CO_ORDERDATE" type="xs:date"/>
>     									<xs:element name="CO_SHIPMETHOD" type="xs:date"/>
>     									<xs:element name="CO_TOTALORDERAMT" type="xs:decimal"/>
>     								</xs:sequence>
>     							</xs:complexType>
>     						</xs:element>
>     					</xs:sequence>
>     				</xs:complexType>
>     			</xs:element>

>     		</xs:schema>
>     	</types>
>     	<functions>
>     		      <!-- result one returns a customer, result 2 has the
>                                 orders for that customer -->
>     		<function name="CALLMULTIPLERESULTSET"
>                                           return_type="CustomerAndOrders">
>     				<argument label="custid" mode="input_only" type="xs:string"/>
>     			<presentation group="DB2 stored procedures"/>
>     		</function>
>     	</functions>
>     </definitions>

The following is the stored procedure signature for this Stored Procedure Description file. This signature creates a procedure that returns multiple result sets.

>     CREATE PROCEDURE DB2ADMIN.CALLMULTIPLERESULTSET (
>             IN CUSTID varchar(64) )
>     EXTERNAL NAME           '"DB2ADMIN".SQL30206110348560:db2test.CallMultipleResultSet.callMultipleResultSet'
>     SPECIFIC DB2ADMIN.CALLMULTIPLERS
>     RESULT SETS 2
>     LANGUAGE JAVA
>     PARAMETER STYLE JAVA
>     NOT DETERMINISTIC
>     FENCED NO
>     DBINFO NULL
>     CALL MODIFIES SQL DATA

Oracle Cursor as return_value

The following Stored Procedure Description file describes an Oracle stored procedure that returns a cursor as a return_value.

>     <?xml version="1.0" encoding="UTF-8"?>
>     <definitions>
>     	<types>
>     		<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
>     			<xs:element name="Output_TEST_PACKAGE.GETCUSTOMERBYID">
>     				<xs:complexType>
>     					<xs:sequence>
>     						<xs:element name="return_value">
>     							<xs:complexType>
>     								<xs:sequence>
>     									<xs:element maxOccurs="unbounded" minOccurs="0"
>                                                       name="customer">
>     										<xs:complexType>
>     											<xs:sequence>
>     												<xs:element name="FIRST_NAME" type="xs:string"/>
>     												<xs:element name="LAST_NAME" type="xs:string"/>
>     												<xs:element name="CUSTOMER_ID" type="xs:string"/>
>     												<xs:element name="STATE" type="xs:string"/>
>     												<xs:element name="ZIPCODE" type="xs:string"/>
>     												<xs:element name="CITY" type="xs:string"/>
>     												<xs:element name="STREET_ADDR2"
>                                                               type="xs:string"/>
>     												<xs:element name="STREET_ADDR1"
>                                                               type="xs:string"/>
>     												<xs:element name="CUSTOMER_SINCE"
>                                                               type="xs:dateTime"/>
>     												<xs:element name="EMAIL_ADDRESS"
>                                                               type="xs:string"/>
>     												<xs:element name="TELEPHONE_NUMBER"
>                                                               type="xs:string"/>
>     											</xs:sequence>
>     										</xs:complexType>
>     									</xs:element>
>     								</xs:sequence>
>     							</xs:complexType>
>     						</xs:element>
>     					</xs:sequence>
>     				</xs:complexType>
>     			</xs:element>

>     		</xs:schema>
>     	</types>
>     	<functions>
>     		<function name="TEST_PACKAGE.GETCUSTOMERBYID"
>                               return_type="Output_TEST_PACKAGE.GETCUSTOMERBYID">
>     			<argument label="CUSTID" mode="input_only" type="xs:string"/>
>     		</function>
>     	</functions>
>     </definitions>

The following is the stored procedure signature for this Stored Procedure Description file. This signature creates a procedure that returns a cursor.

>     create or replace package body test_package  as
>     -- SP that returns a cursor
>         FUNCTION  getCustomerByID (custID varchar)
>                 RETURN CUST_CURSOR IS cur CUST_CURSOR;
>         BEGIN
>             open  cur for
>                     select first_name, last_name, customer_id, state,
>                                   zipCode,city, street_address2, street_address1,
>                                   customer_since, email_address, telephone_number
>                     from wireless.customer
>                     where customer_id = custID;
>             return cur;
>         END;
>     end test_package ;