Skip navigation.
Downloads | Product Documentation | Support

Building Queries and Data Views

# Accessing SQL Calls: Stored Procedures and SQL Queries

If you have stored procedures defined in your databases, you can expose them to Liquid Data as a data source and use them in your Liquid Data queries.

You can also expose any query from the database as a data source. You expose these "SQL Calls" to Liquid Data through a SQL Call Description File. This chapter describes how to define stored procedures and SQL queries in a SQL Call Description File, and contains the following sections:

- Defining Stored Procedures to Liquid Data
- SQL Call Description File
- Rules for Specifying SQL Call Description Files
- Sample SQL Call Description Files
- Stored Procedure Support by Database
- Using Stored Procedures in Queries

For an example and a demo of defining a stored procedure and using it in a query, see Example: Defining and Using a Customer Orders Stored Procedure. For information on samples installed with Liquid Data, including a stored procedure sample and a SQL Call example, see Samples Installed with Liquid Data in Liquid Data by Example.

# Defining Stored Procedures to Liquid Data

To use stored procedures in Liquid Data, you must create a SQL Call Description File. The SQL Call Description File is an XML schema file that defines the types and the functions for a set of stored procedures. For details on defining a SQL Call Description File, see SQL Call Description File and Rules for Specifying SQL Call Description Files. For database-specific information, see Stored Procedure Support by Database.

## To Define Stored Procedures to Liquid Data

Perform the following steps to define a stored procedure for use with Liquid Data.

1. Create your stored procedures in the underlying database, if they do not already exist. For details about Liquid Data support of stored procedures for your database, see Stored Procedure Support by Database.
2. In the WebLogic Console, create a JDBC Connection Pool to access your database, if one does not already exist.
3. In the WebLogic Console, create a JDBC Data Source for the connection pool created in the previous step.
4. Create a SQL Call Description File for your stored procedures and save it to the `sql_calls` directory of the Liquid Data repository. For details, see SQL Call Description File and Rules for Specifying SQL Call Description Files.
5. In the WebLogic Administration Console (to access the Liquid Data Console, click the Liquid Data link at the bottom of the list on the WebLogic Administration Console), click the Data Sources tab.
6. Click the Relational Databases tab.
7. Click the Configure a New Relational Data Source Description Link (or open an existing Data Source to modify it).
8. If you are creating a new data source, enter values for Name, Data Source Name, and Schema fields in the Configure Relational Data Source Description screen. For more details on configuring relational data sources, see Configuring Access to Relational Databases in the Administration Guide.
9. In the Configure Relational Data Source Description screen, specify a SQL Call Description File by clicking the Browse Repository link next to the SQL Call Description File field.
10. In the Repository Browser, select the file you created containing your stored procedure definitions. After making your selection, click the Select button.

11. In the Configure Relational Data Source Description screen, click the Apply button to save your Data Source definition.
12. Check the WebLogic Server log file for any errors, and correct them as necessary.

You can now access your stored procedures in the Data View Builder. If you are already connected to the server in the Data View Builder, you must re-connect by selecting File —> Connect from Data View Builder menu. The Stored Procedures tab appears in the Design view under the sources tab of the Data View Builder. You can now use your stored procedures as you do other building blocks (for example, data sources, XQuery functions, and so on) to build queries.

# SQL Call Description File

The SQL Call Description File is an XML file that defines stored procedures to Liquid Data. This section describes the schema of the SQL Call Description File, and contains the following sections:

- Basic Structure
- Schema Definition File for SQL Call Description File
- Element and Attribute Reference for SQL Call Description File
- Supported Datatypes

For sample SQL Call Description Files, see Sample SQL Call Description Files.

## Basic Structure

The SQL Call Description File has the following main sections:

- Type Definitions
- Function Definitions

### Type Definitions

The type definitions section of the SQL Call Description File is defined in the `<types>` element. This element defines namespaces and complex types for the stored procedures defined in the SQL Call Description File.

### Function Definitions

The function definitions section of the SQL Call Description File is defined in the `<functions>` element. Within the `<functions>` element are `<function>` elements, each of which defines the signature of a stored procedure. You can define one or more stored procedures in a single SQL Call Description File.

## Schema Definition File for SQL Call Description File

The following is the schema definition file for the SQL Call Description File:

>     <?xml version="1.0" encoding="UTF-8"?>
>     <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
>     	<xsd:element name="definitions">
>     		<xsd:complexType>
>     			<xsd:sequence>
>     				<xsd:element ref="types"/>
>     				<xsd:element ref="functions"/>
>     			</xsd:sequence>
>     			<xsd:attribute name="targetNamespace" use="optional"
>                          type="xsd:string"/>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:element name="types">
>     		<xsd:complexType>
>     			<xsd:sequence>
>     				<xsd:element ref="schema"/>
>     			</xsd:sequence>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:element name="schema" type="xsd:anyType"/>
>     	<xsd:element name="functions">
>     		<xsd:complexType>
>     			<xsd:sequence>
>     				<xsd:element ref="function" maxOccurs="unbounded"/>
>     			</xsd:sequence>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:element name="function">
>     		<xsd:complexType>
>     			<xsd:sequence>
>     				<xsd:element ref="argument" minOccurs="0"
>                           maxOccurs="unbounded"/>
>     				<xsd:element ref="presentation" minOccurs="0"/>
>     				<xsd:element ref="description" minOccurs="0"/>
>     			</xsd:sequence>
>     			<xsd:attribute name="name" use="required"
>                          type="xsd:string"/>
>     			<xsd:attribute name="return_type" use="required"
>                          type="xsd:string"/>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:element name="argument">
>     		<xsd:complexType>
>     			<xsd:attribute name="type" use="required"
>                          type="xsd:string"/>
>     			<xsd:attribute name="label" use="required"
>                          type="xsd:string"/>
>     			<xsd:attribute name="mode" use="required" type="modeType"/>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:simpleType name="modeType">
>     		<xsd:restriction base="xsd:string">
>     			<xsd:enumeration value="input_only"/>
>     			<xsd:enumeration value="output_only"/>
>     			<xsd:enumeration value="input_output"/>
>     		</xsd:restriction>
>     	</xsd:simpleType>
>     	<xsd:element name="presentation">
>     		<xsd:complexType>
>     			<xsd:attribute name="group" use="required"
>                          type="xsd:string"/>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:element name="description" type="xsd:string"/>
>     </xsd:schema>

## Element and Attribute Reference for SQL Call Description File

Table 10-1 lists and describes the elements and attributes of the SQL Call Description File.

Table 10-1 SQL Call Description File XML elements and descriptions

Element | Attribute | Description
--- | --- | ---
>     <definitions>
>     targetNameSpace
| The namespace declared for the stored procedures.
>     <types>
| Declares any primitive or complex data types used in the stored procedure.
>     <functions>
| Contains the function definitions for all of the stored procedures represented in this file.
>     <function>
| Function definition for a single stored procedure.
>     name
| Name of the stored procedure as defined in the database. If the stored procedure is part of a package, the name is the fully qualified name of the stored procedure (for example, `packagename.sp_name`). If you are using procedure groups in Sybase or Microsoft SQL Server, see Rules for Procedure Names Containing a Semi-Colon.
>     return_type
| Return type of the stored procedure. The type is defined in the `<types>` element of this file. Note that this type differs from the type which the stored procedure returns. If you are hand-coding your own XQueries, you must perform a function signature transformation; for details, see Rules for Transforming the Function Signature When Hand Writing an XQuery.
>     <sql_statement>
| Contains the SQL statement text for a query against the data source. The query can then be used as a data source.
>     <argument>
| Contains the argument declarations for the inputs and/or outputs of the stored procedure.
>     label
| The name of the argument input or output. This name is used in queries and is displayed in clients such as the Data View Builder.
>     type
| Type of the argument. The type can be one of the types listed in Supported Datatypes, or it can be a complex type declared in the SQL Call Description File.
>     mode
| Lists whether the argument is part of the input, output, or both. Possible values are: `input_only` `output_only` `input_output`
>     <presentation group>
| Currently not supported.
>     <description>
| Comment text describing the stored procedures used in the SQL Call Description File.

Note: An element within the `types` definition with a name specified with `name="return_value"` is reserved to specify the return value from a function or a procedure. For an example, see Example 2: Type Definition with Simple Return Value.

## Supported Datatypes

The stored procedures you define in the SQL Call Description Files must use the XML data types shown in Table 10-2. You must map the database data types to one of the types in this table. For data type support by database, see Stored Procedure Support by Database.

Except for user-defined complex data types, the types are all primitive data types.

Table 10-2 XML data types and their Java equivalents for SQL Call Description Files

XML Data Type | Equivalent Java Data Type
--- | ---
>     xs:boolean
>     java.lang.boolean
>     xs:byte
>     java.lang.byte
>     xs:short
>     java.lang.short
>     xs:integer
>     java.lang.Integer
>     xs:int
>     java.lang.Integer
>     xs:long
>     java.lang.Long
>     xs:float
>     java.lang.float
>     xs:double
>     java.lang.double
>     xs:decimal
>     java.math.BigDecimal
>     xs:string
>     java.lang.String
>     xs:dateTime
>     java.util.Date
> Complex Element Type |
>     org.w3c.dom.Element

For JDBC and database-specific type mapping, see Datatypes in the XQuery Reference Guide.

# Rules for Specifying SQL Call Description Files

This section describes rules for the `return_type` attribute of the `<function>` element and the `mode` attribute of the `<argument>` element. This section includes the following rules:

- Rules for Element and Attribute Names
- Rules for Procedure Names Containing a Semi-Colon
- Rules and Examples of <type> Declarations to Use in the <function> return_type Attribute
- Rules for the mode Attribute output_only <argument> Element
- Rules for Transforming the Function Signature When Hand Writing an XQuery

## Rules for Element and Attribute Names

XML requires that element and attribute names begin with a non-numeric character. Therefore, when you specify the `name` attribute of the `<xs:element>` element in a SQL Call Description File, you must specify a name that does not start with a numeric character. For example, if you have a stored procedure that returns a cursor, and the cursor returns columns that start with a numeric character, you must map those column names to valid XML element names in your SQL Call Description File.

For the W3C definition of a valid name for an attribute or element, see:

http://www.w3.org/TR/2000/WD-xml-2e-20000814#dt-name

For example, consider a cursor named `MY_CURSOR` is declared with the following SQL statement:

>     open MY_CURSOR for
>       select 1_column, 2_column, 3_column
>       from MY_TABLE
>     return MY_CURSOR

When you define the cursor type in the `<types>` section of your SQL Call Description File, you must map the column names from the cursor output to start with a non-numeric character so the resulting XML generated is valid. You could use the following type definition for this cursor, which starts each numeric column name with an underscore character (_):

>     	<types>
>     		<xs:element name="MY_CURSOR" minOccurs="0"
>                    maxOccurs="unbounded">
>     			<xs:complexType>
>     				<xs:sequence>
>     					<xs:element name="_1_column" type="xs:integer"/>
>     					<xs:element name="_2_column" type="xs:string"/>
>     					<xs:element name="_3_column="xs:string"/>
>     				</xs:sequence>
>     			</xs:complexType>
>     		</xs:element>
>     	</types>

For some notes on relational database object names and how to specify them so they can be used in an XQuery, see Relational Databases.

## Rules for Procedure Names Containing a Semi-Colon

Sybase and Microsoft SQL Server databases provide the ability to group stored procedures by using a semicolon character (;) to separate a procedure name with a number. For example, you can have two stored procedures with the following names:

>     MY_SP;1
>     MY_SP;2

When you specify these procedures in the SQL Call Description File, use the database name (the name with the semicolon character). When you use these procedure names in an XQuery, however, you must substitute an underscore character (_) for the semicolon character. The Data View Builder automatically substitutes the underscore character for the semicolon character in the XQuery it generates.

For example, consider the following definition for a stored procedure in a SQL Call Description File:

>     <function name="MY_SP;2" return_type="Results">
>     	<argument label="COLUMN_123" mode="input_only"
>                   type="xs:string"/>
>     	<argument label="ANOTHER_COLUMN" mode="output_only"
>     				type="xs:int"/>
>     </function>

When you reference this function in an XQuery, it is referred to as follows:

>     MY_SP_2

## Rules and Examples of <type> Declarations to Use in the <function> return_type Attribute

The `return_type` attribute of the `<function>` element specifies the complex type for the stored procedure. The complex type must be declared in the `<types>` section of the SQL Call Description File. For example, the following element opening tag shows a function named `myFunction` with a `return_type` of `myReturnType`:

>     <function name="myFunction" return_type="myReturnType" >

The return type `myReturnType` must be declared in the `<types>` section. The type must contain the actual return value of the stored procedure (if it has a return value) and the row set definitions (if applicable). The row set definitions must appear in the order in which the stored procedure returns them.

When a stored procedure returns a primitive type, you must declare the primitive type using the `return_value` keyword for the `name` attribute. For an example of this, see Example 2: Type Definition with Simple Return Value.

### Example 1: Type Definition with No Return Value

The following is a type definition for a stored procedure that has no return value and returns no row sets.

>     	<types>
>     		<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
>     			<xs:element name="EmptyOutput">
>     				<xs:complexType>
>     					<xs:sequence>
>     					</xs:sequence>
>     				</xs:complexType>
>     			</xs:element>
>     		</xs:schema>
>     	</types>

Use a similar type definition in a stored procedure that does not have any return value. This `EmptyOutput` type definition is required for all stored procedures and functions that do not return anything.

### Example 2: Type Definition with Simple Return Value

The following is a type definition for a stored procedure that has a simple return value (`xs:integer`) and returns no row sets.

>     	<types>
>     		<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
>     			<xs:element name="SimpleOutput">
>     				<xs:complexType>
>     					<xs:sequence>
>     						<xs:element name="return_value"
>     								type="xs:integer" />
>     					</xs:sequence>
>     				</xs:complexType>
>     			</xs:element>
>     		</xs:schema>
>     	</types>

Use a similar type definition with a stored procedure that returns a status code or a single value.

### Example 3: Type Definition for Complex Row Set Type

The following is a type definition for a stored procedure that returns a row set, which is a complex type.

>     	<types>
>     		<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
>     			<xs:element name="customerTable">
>     				<xs:complexType>
>     					<xs:sequence>
>     						<xs:element name="CUSTOMER" minOccurs="0"
>     								maxOccurs="unbounded" >
>     							<xs:complexType>
>     								<xs:sequence>
>     									<xs:element name="C_NAME"
>     											type="xs:string"/>
>     									<xs:element name="C_ACCTBAL"
>     											type="xs:decimal"/>
>     								</xs:sequence>
>     							</xs:complexType>
>     					</xs:sequence>
>     				</xs:complexType>
>     			</xs:element>
>     		</xs:schema>
>     	</types>

Use a similar type definition with a stored procedure that returns a result set (for example, in Sybase, Microsoft SQL Server, or DB2).

### Example 4: Type Definition with Complex Return Value

The following is a type definition for a stored procedure that returns a complex type. Assume that the `customerTable` complex type (shown in the previous example) is defined in the same Stored Procedure Definition file.

>     	<types>
>     		<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
>     			<xs:element name="return_value" type="customerTable">
>     			</xs:element>
>     		</xs:schema>
>     	</types>

Use a similar type definition with an Oracle stored procedure that returns a cursor with a complex type.

### Example 5: Type Definition with Simple Return Value and Two Row Sets

The following is a type definition for a stored procedure that has a simple return value (`xs:integer`) and returns two row sets.

>     	<types>
>     		<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
>     ......
>     ......{-- Definitions for complex types customerTable and
>               ordersTable go here --}
>     ......
>     			<xs:element name="OutputName">
>     				<xs:complexType>
>     					<xs:sequence>
>     						<xs:element name="return_value"
>     								type="xs:integer" />
>     						<xs:element ref="customerTable" />
>     							{-- customerTable defined above--}
>     						<xs:element ref="ordersTable" />
>     							{-- ordersTable defined above --}
>     					</xs:sequence>
>     				</xs:complexType>
>     			</xs:element>
>     		</xs:schema>
>     	</types>

## Rules for the mode Attribute output_only <argument> Element

If you define a function that has an `<argument>` element that has the mode `output_only`, then you need only reference the type definition in the function definition. The following example references the `customerTable` type (defined in the `<types>` section of the SQL Call Description File, as described in Example 3: Type Definition for Complex Row Set Type). Assume that the `customerTable` type maps to a cursor returned from an Oracle stored procedure.

>     <function name="GetAllCustomersByState" return_type="EmptyOutput">
>     	<argument label="state" mode="input_only" type="xs:string"/>
>     	<argument label="CustomersOutput" mode="output_only"
>     				type="customerTable"/>
>     </function>

## Rules for Transforming the Function Signature When Hand Writing an XQuery

There are two issues to remember when hand crafting an XQuery that accesses a stored procedure:

- Namespace Declaration
- Function Transformation

### Namespace Declaration

All queries that access stored procedures must have a unique namespace with a URI of the of the following form:

>     urn:<Liquid_Data_Relational_data_source_name>

Declare the namespace in the query prolog. The namespace declaration has the following syntax:

>     namespace <alias>="<URI>"

For example:

>     namespace SY_WL_NS="urn:SY-WL"

You can then access the stored procedure using the namespace alias and the name of the stored procedure object. For example:

>     SY_WL_NS:wireless.dbo.RetAndOpParamTransformation("CUSTOMER_11")

### Function Transformation

For stored procedures that return both a return value (for example, an integer return value) and have `output` or `input_output` parameters, the function signature in the SQL Call Description File is different from the signature that is used to write queries that access the stored procedure. If you look at the schema that displays in the Stored Procedures palette of the Data View Builder, you will see the transformed signature.

The transformed signature combines the return value and any `output` or `input_output` parameters.

For example, consider an example using a Sybase stored procedure with the following signature:

>     create proc RetAndOpParamTransformation (@custidPattern varchar(64), @custCount numeric(10) output )
>      as
>           select @custCount = count(*)  from CUSTOMER
>           where CUSTOMER.CUSTOMER_ID Like '%' +  @custidPattern  + '%'
>     RETURN 1

The following is the SQL Call Description File for this stored procedure:

>     <?xml version="1.0" encoding="UTF-8"?>
>     <definitions>
>     	<types>
>     		<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

>     <!-- The stored procedure returns an integer, which is mapped as the return_value, a reserved element name for stored procedure with a return.
>     -->
>     			<xs:element name="RetAndOpParamTransformation">
>     				<xs:complexType>
>     					<xs:sequence>
>     						<xs:element name="return_value"
>                                      type="xs:integer"/>
>     					</xs:sequence>
>     				</xs:complexType>
>     			</xs:element>
>     		</xs:schema>
>     	</types>

>     <!-- The stored procedure signature mapping. The element RetAndOpParamTransformation wraps the return_value of the stored procedure. This stored procedure has custidPattern as an input parameter. custCount is defined as an output parameter of type integer (because it returns an integer count).
>     -->
>     	<functions>
>     		<function name="wireless.dbo.RetAndOpParamTransformation"
>                      return_type="RetAndOpParamTransformation">
>     			<argument label="custidPattern" mode="input_only"
>                          type="xs:string"/>
>     			<argument label="custCount" mode="output_only"
>                          type="xs:integer"/>
>     			<presentation group="Sample to show transformation of return_value and output prameter in a stored procedure"/>
>     		</function>
>     	</functions>
>     </definitions>

Because this stored procedure has a return value and an output parameter, the output of the function is transformed to the following schema:

>     <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
>     	<xsd:element name="RetAndOpParamTransformation">
>     		<xsd:complexType>
>     			<xsd:sequence>
>     				<xsd:element name="return_value"
>                                type="xsd:integer"/>
>     				<xsd:element name="custCount" type="xsd:integer"/>
>     			</xsd:sequence>
>     		</xsd:complexType>
>     	</xsd:element>
>     </xsd:schema>

where the `return_value` is the return from the stored procedure and `custCount` is the output parameter. If you view this stored procedure in the Data View Builder, you will see the transformed schema.

The following is a query against this stored procedure:

>     namespace SY-WL-NS = "urn:SY-WL"

>     let $SY_WL_SP_Return := SY-WL-NS:wireless.dbo.RetAndOpParamTransformation("CUSTOMER_11")
>     return

>     <RetAndOpParamTransformation>
>      <return_value>{ xf:data($SY_WL_SP_Return/RetAndOpParamTransformation/return_value) }
>      </return_value>
>        <custCount>{ xf:data($SY_WL_SP_Return/RetAndOpParamTransformation/custCount) }
>        </custCount>
>     </RetAndOpParamTransformation>

In this query, the XPath expressions for `return_value` and for `custCount` have the same parent element.

# Sample SQL Call Description Files

This section shows several sample SQL Call Description Files. For simplicity and readability, each of the examples shown defines a single stored procedure and its supporting type; your SQL Call Description Files can define multiple stored procedures and multiple types. For information on samples installed with Liquid Data, including a stored procedure sample and a SQL Call example, see Samples Installed with Liquid Data in Liquid Data by Example.

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
- Oracle SQL Statement With Subquery

## DB2 Simple input_only, output_only, and input_output Example

The following SQL Call 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 SQL Call 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 SQL Call 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 SQL Call 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 SQL Call 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 SQL Call 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 SQL Call 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 SQL Call 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 ;

## Oracle SQL Statement With Subquery

The following SQL Call Description File defines a SQL statement with a subquery and syntax specific to Oracle.

>     <?xml version="1.0" encoding="UTF-8"?>
>     <definitions>
>     <types>
>     <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

>     	<xs:element name="Customers">
>     		<xs:complexType>
>     			<xs:sequence>
>     				<xs:element ref="resultSetCustomer"/>
>     			</xs:sequence>
>     		</xs:complexType>
>     	</xs:element>

>     	<xs:element name="resultSetCustomer">
>     		<xs:complexType>
>     			<xs:sequence>
>     				<xs:element name="customerRow" minOccurs="0"maxOccurs="unbounded">
>     					<xs:complexType>
>     						<xs:sequence>
>     							<xs:element name="CUSTOMER_ID" type="xs:string"/>
>     							<xs:element name="ORDER_ID" type="xs:string"/>
>     							<xs:element name="LINE_ID" type="xs:string"/>
>     						</xs:sequence>
>     					</xs:complexType>
>     				</xs:element>
>     			</xs:sequence>
>     		</xs:complexType>
>     	</xs:element>

>     </xs:schema>

>     </types>

>     	<functions>

>     		<function name="GetOrderInfoSQL" return_type="Customers" >
>     		<sql_statement>
>     		SELECT t1.customer_id, t2.order_id, t3.line_id from
>     			customer t1,
>     			(SELECT * from customer_order where customer_id != 'CUSTOMER_1' ) t2,
>     			customer_order_line_item t3
>     			where t1.customer_id = ? and t2.customer_id (+) = t1.customer_id and t3.order_id(+)=t2.order_id
>     		</sql_statement>
>     			<argument label="customer_id" mode="input_only" type="xs:string"/>
>     			<presentation group="Oracle SQL Call"/>
>     		</function>

>     	</functions>

>     </definitions>

# Stored Procedure Support by Database

This section lists stored procedure support by database vendor. Each vendor supports the data types supported in their respective databases. The following databases are supported:

- Oracle
- Microsoft SQL Server
- Sybase
- IBM DB2
- Informix

## Oracle

Table 10-3 describes the stored procedure support for Oracle databases. Table 10-4 describes Oracle stored procedures return values.

Table 10-3 Oracle Stored Procedure parameter support

Parameter Mode | Data Types Supported | Notes and Restrictions
--- | --- | ---
`input_only` | Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. | The PL/SQL `%TYPE` definitions must be translated to the XML schema types defined in Supported Datatypes.
`output_only` | A Cursor Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes.
`input_output` | Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes.

Table 10-4 Oracle Stored Procedure returned values support

Return Value | Types Supported
--- | ---
Primitive type | An primitive type such as an integer, a string, etc.
Return cursor | See Table 10-3.

## Microsoft SQL Server

Table 10-5 describes the stored procedure support for Microsoft SQL Server databases. Table 10-6 describes Microsoft SQL Server stored procedures return values.

Table 10-5 Microsoft SQL Server Stored Procedure parameter support

Parameter Mode | Data Types Supported | Notes and Restrictions
--- | --- | ---
`input_only` | Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. | You must map `TINYINT` values to `xs:short` in the SQL Call Description File.
`output_only` | Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. | You must map `TINYINT` values to `xs:short` in the SQL Call Description File.

Table 10-6 Microsoft SQL Server Stored Procedure returned values support

Return Value | Types Supported | Notes and Restrictions
--- | --- | ---
Return Status code | An integer value. |
Row Set | Single or multiple result sets. | You must map `TINYINT` values to `xs:short` in the SQL Call Description File.

If you are using procedure groups, see Rules for Procedure Names Containing a Semi-Colon for information on mapping the procedure names to the SQL Call Description File and using the names in an XQuery.

Microsoft SQL Server parameter names begin with the @ character, but the name must appear in the SQL Call Description File without the @ character. For example, a parameter named `@myInputParameter` must be mapped as `myInputParameter`.

## Sybase

Table 10-7 describes the stored procedure support for Sybase databases. Table 10-8 describes Sybase stored procedures return values.

Table 10-7 Sybase Stored Procedure parameter support

Parameter Mode | Data Types Supported | Notes and Restrictions
--- | --- | ---
`input_only` | Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. | You must map `TINYINT` values to `xs:short` in the SQL Call Description File.
`output_only` | Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. | You must map `TINYINT` values to `xs:short` in the SQL Call Description File.

Table 10-8 Sybase Stored Procedure returned values support

Return Value | Types Supported | Notes and Restrictions
--- | --- | ---
Return Status code | An integer value. |
Row Set | Single or multiple result sets. | You must map `TINYINT` values to `xs:short` in the SQL Call Description File.

If you are using procedure groups, see Rules for Procedure Names Containing a Semi-Colon for information on mapping the procedure names to the SQL Call Description File and using the names in an XQuery.

Sybase parameter names begin with the @ character, but the name must appear in the SQL Call Description File without the @ character. For example, a parameter named `@myInputParameter` must be mapped as `myInputParameter`.

## IBM DB2

Table 10-9 describes the stored procedure support for IBM DB2 databases. Table 10-10 describes IBM DB2 stored procedures return values.

Table 10-9 IBM DB2 Stored Procedure parameter support

Parameter Mode | Data Types Supported
--- | ---
`input_only` | Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes.
`output_only` | Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes.
`input_output` | Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes.

Table 10-10 IBM DB2 Stored Procedure returned values support

Return Value | Types Supported
--- | ---
Primitive type | An primitive type such as an integer, a string, etc.
Row Set | Single or multiple result sets.

## Informix

Table 10-11 describes the stored procedure support for Informix databases. Table 10-12 describes Informix stored procedures return values.

Table 10-11 Informix Stored Procedure parameter support

Parameter Mode | Data Types Supported
--- | ---
`input_only` | Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes.

Table 10-12 Informix Stored Procedure returned values support

Return Value | Types Supported
--- | ---
Row Set | Single or multiple result sets.

# Using Stored Procedures in Queries

You can use stored procedures to build queries in the Data View Builder just like you use other data sources. Drag and drop input elements into the inputs of the procedure and drag and drop output elements to combine with other sources or to map onto a target XML schema.

Figure 10-13 Drag and Drop Input Elements into the Elements of the Stored Procedure

This section shows an example of defining a stored procedure and then using it in a query, and is divided into the following sections:

- Define Stored Procedures to Liquid Data
- Example: Defining and Using a Customer Orders Stored Procedure

## Define Stored Procedures to Liquid Data

You must define the stored procedures to Liquid Data before you can use them in queries. For details, see To Define Stored Procedures to Liquid Data. To use a stored procedure in the Data View Builder, select Stored Procedures from the Sources tab, navigate to your stored procedure, then drag and drop it into the design workspace. You can then connect data by dragging and dropping inputs and outputs.

## Example: Defining and Using a Customer Orders Stored Procedure

This example details the steps to define a stored procedure to Liquid Data and then use it in a query. This example is similar to the example installed in the following directory:

>     BEA_HOME/liquiddata/samples/buildQuery/stored-procedure

The demo in this directory includes the SQL Call Description File and a Data View Builder project file.

### Business Scenario

The stored procedure in this example answers the following business question: For all orders greater than or equal to $500.00, find the number of orders and the total value of all of those orders for a given customer.

### View a Demo

Stored Procedure Demo... If you are looking at this documentation online, you can click the "Demo" button to see a viewlet demo showing how to define a stored procedure and use it in a query. This demo previews the steps described in detail in the following sections.

### Step 1: Create the Stored Procedure in the Database

You must have stored procedures defined in your database before you can access them through Liquid Data.

Every database has its own way of creating stored procedures. This sample uses a Pointbase database, and Pointbase uses Java stored procedures. The source code for the sample stored procedure is installed with Liquid Data in the following file:

>     BEA_HOME/liquiddata/samples/buildQuery/stored-procedure/pbsp.java

The signature for this stored procedure is created with the following SQL statements:

>     create procedure
>              GetOrderInfo( IN P1 VARCHAR(20),  IN P2 INTEGER,
>                            OUT P3 INTEGER, OUT P4 INTEGER)
>     LANGUAGE JAVA
>     SPECIFIC GetOrderInfo
>     EXTERNAL NAME  "com.bea.ldi.sample.pbsp::GetOrderInfo"
>     PARAMETER STYLE SQL;

### Step 2: Create the SQL Call Description File

For details on the structure of the SQL Call Description File, see SQL Call Description File and Rules for Specifying SQL Call Description Files.

The SQL Call Description File for this example defines an empty complex type in the `<types>` section and defines a function that returns that complex type in the `<functions>` section. The function definition contains `<argument>` elements for each input and output argument. The `<argument>` elements specify the name (`label` attribute), parameter type (`mode` attribute), and data type (`type` attribute) for each input and output of the stored procedure.

The following is a code listing of the SQL Call Description File for this example.

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

>     		<function name="GetOrderInfo" return_type="Results">
>     			<argument label="customer_id" mode="input_only"
>                         type="xs:string"/>
>     			<argument label="order_amount" mode="input_only"
>                         type="xs:integer"/>
>     			<argument label="totalsum" mode="output_only"
>                         type="xs:integer"/>
>     			<argument label="totalorder" mode="output_only"
>                         type="xs:integer"/>
>     			<presentation group="Pointbase stored procedures"/>
>     		</function>

>     	</functions>
>     </definitions>

### Step 3: Specify the SQL Call Description File in the Liquid Data Console

Perform the following steps to specify the SQL Call Description File in the data source description:

1. In the WebLogic Administration Console (to access the Liquid Data Console, click the Liquid Data link at the bottom of the list on the WebLogic Administration Console), click the Data Sources tab.
2. Click the Relational Databases tab.
3. Select an existing relational data source and edit it or create a new relational data source.

If you are creating a new data source, you must also configure a JDBC Connection Pool to access your database and a JDBC Data Source for the connection pool.

4. In the Configure Relational Data Source Description screen, enter values for Name, Data Source Name, and Schema fields, if they are not already entered. For more details on configuring relational data sources, see Configuring Access to Relational Databases in the Administration Guide.
5. In the Configure Relational Data Source Description screen, click the Browse Repository link next to the SQL Call Description File field.
6. In the Repository Browser, select the file you created containing your stored procedure definitions. After making your selection, click the Select button.
7. In the Configure Relational Data Source Description screen, click the Apply button to save your Data Source definition.

### Step 4: Open the Data View Builder to See Your Stored Procedures

Start the Data View Builder and connect to the Liquid Data server. If you are already connected, run the File > Connect command to reconnect. If you configured the Stored procedure correctly, it appears in the Sources tab as one of the stored procedures.

### Step 5: Use the Stored Procedure in a Query

Perform the following steps in the Data View Builder to create a query that uses the stored procedure.

1. Start a new Data View Builder project (File —> New Project).
2. Open the source and target schemas.

- Drag and drop Source —> Stored Procedure —> PB-WL —> `getOrderInfo` into the design area.
- Set the target schema to `getorderinfo.xsd` (in the repository).

3. Create a query parameter named `CUST_ID` of type `xs:string` for `customer_id`.
4. Drag the `CUST_ID` query parameter into the `customer_id` stored procedure input.
5. Create a numeric constant of 500 and drag it into the `order_amount` input parameter.
6. Drag the `totalsum` stored procedure output to the `totalsum` element of the target schema.
7. Drag the `totalorder` stored procedure output to the `totalorder` element of the target schema.

### Step 6: Run the Query

Perform the following to run this query:

1. Click the test tab in the Data View Builder.
2. Enter a value for the `CUST_ID` query parameter. For example, enter `CUSTOMER_1`.
3. Click the Run button. The results will look similar to the following:

>     <Results>
>         <totalsum>7000</totalsum>
>         <totalorder>3</totalorder>
>     </Results>