Enterprise databases utilize stored procedures to improve query performance, manage and schedule data operations, enhance security, and so forth. Stored procedures are essentially database objects that logically group a set of SQL and native database programming language statements together to perform a specific task.
You can import stored procedure metadata from any relational data available to the BEA WebLogic Server. ALDSP then uses that metadata to generate a physical data service that you can then use in logical data services.
Objectives
After completing this lesson, you will be able to:
Import stored procedures as a Java project within an application.
Import stored procedure metadata into a data service.
Overview
Imported stored procedure metadata is quite similar to imported metadata for relational tables and views. Stored procedure metadata generally contains:
A data service file with a pragma that describe the parameters of the stored procedure.
A schema file with the same primary name as the procedure name.
Note:
If a stored procedure includes only one return value and the value is either simple type or a row set that is mapping to an existing schema, no schema file is created.
Handling Stored Procedure Row Sets
A row set type is a complex type, whose name can include:
The parameter name, if there is an input/output or output only parameter.
An assigned name such as RETURN_VALUE, if there is a return value.
The referenced element name (result rowsets) in a user-specified schema.
The row set type contains a repeatable element sequence (for example, called CUSTOMER) with the fields of the row set.
Notes:
All row set-type definitions must conform to the structure in the stored procedure itself. In some cases the Metadata Import Wizard will be able to automatically detect the structure of a row set and create an element structure. However, if the structure cannot be determined, you will need to provide it through the wizard.
Each database vendor approaches stored procedures differently. Refer to your database documentation for details on managing stored procedures.
XQuery support limitations are, in general, due to JDBC driver limitations.
ALDSP does not support rowset as an input parameter.
31.1 Importing a Stored Procedure into the Application
The first step in demonstrating ALDSP's ability to access data through a stored procedure is to import the procedure into the application.
Objectives
In this exercise, you will:
Import stored procedures as a Java project.
Test the results.
Instructions
Import storedprocs as a Java project, adding it to the Evaluation application. The project is located in <beahome>\weblogic81\samples\LiquidData\EvalGuide.
Build the storedprocs project. The storedprocs.jar file will be added to the Libraries folder.
Shutdown the PointBase database, by stopping WebLogic Server.
Note:
Stopping WebLogic Server calls the PointBase shutdown script.
Open the startPointBase.cmd in a text editor such as Notepad. The file is located in <beahome>\weblogic81\common\bin.
In the startPointBase.cmd script, search for the string "@REM Add PointBase classes to the classpath" and add the complete path of the storedprocs.jar file below this line in the script as follows:
set CLASSPATH=<beahome>\user_projects\applications\Evaluation\APP-INF\lib\storedprocs.jar;%POINTBASE_CLASSPATH%
Note:
For reference, the modified startPointBase.cmd is included in samples\liquiddata\EvalGuide.
The CLASSPATH depends on your WebLogic Server installation. User can copy the correct path from the Output window of WebLogic Workshop.
Start WebLogic Server, which in turn starts the PointBase database.
Run CreditRatingStoredProcedure.java to define the stored procedures in PointBase.
Click OK at the pop-up message.
Confirm that the stored procedure executed, by reviewing the contents in the Output window. You should see the credit rating for CUSTOMER3.