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 schema.

 

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 Stored Procedure 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 Stored Procedure Description File

For details on the structure of the Stored Procedure Description file, see Stored Procedure Description File Schema and Rules for Specifying Stored Procedure Description Files.

The Stored Procedure 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 Stored Procedure 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 Stored Procedure Description File in the Liquid Data Console

Perform the following steps to specify the Stored Procedure Description File in the data source description:

1. In the Liquid Data 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 Stored Procedure 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>