Create Physical Data Services from Stored Procedures This page last changed on Nov 28, 2008. .stickable, h1, h2, h3, h4, h5 false true Show Sticky notes Hide Sticky notes Save Cancel Oracle Data Service Integrator Documentation > Data Services Developer's Guide # How To Create Physical Data Services from Stored Procedures Stored procedures are database objects that group an executable set of SQL and native database programming language statements together to perform a specific task locally. Advanced DBMS systems utilize stored procedures to improve query performance, manage and schedule data operations, enhance security, and so forth. In Oracle Data Service Integrator you can, for specifically supported databases, create physical data services based on stored procedures. It is often convenient to leverage independent routines as part of managing enterprise information through a data service. An obvious example would be to leverage standalone update or security functions through data services. Such functions have no XML type; in fact, they typically return nothing (or void). Stored procedures are very often side-effecting from the perspective of the data service, since they perform internal operations on data. In such cases all you need to do is identify the stored procedures as a data service procedure when your physical data service is created. After you have identified the stored procedures that you want to add to your data service, you also have an opportunity to identify which of these should be identified as data service procedures. Each stored procedure that is imported becomes a separate data service. In other words, if you have five stored procedures, you will create five data services. The following topics describe how to create a physicald data service from a stored procedure: Importing Stored Procedure Metadata Using the Physical Data Service Creation Wizard Setting Up the Physical Data Service Creation Wizard Setting Up the Import Wizard for Relational Objects Selecting Stored Procedure Objects for Import Configuring Selected Stored Procedures Stored Procedure Configuration Reference Setting Properties for New Data Service Operations Verifying Data Service Composition Adding Operations to an Existing Data Service Support for Stored Procedures in Popular Databases
| References Terms Commonly Used When Discussing Stored Procedures For details on creating and managing stored procedures in your database, see the documentation provided with the DBMS.
## Importing Stored Procedure Metadata Using the Physical Data Service Creation Wizard The following topics cover the actions necessary to create physical data services from relational stored procedures. ## Setting Up the Physical Data Service Creation Wizard Physical data services are created using a wizard. ##### Physical Data Service Creation Wizard ### Starting the Wizard To start the physical data service creation wizard: Right-click on your dataspace project or any folder in your project. Choose New > Physical Data Service ##### Creating a New Physical Data Service ## Setting Up the Import Wizard for Relational Objects When importing a relational object available options include the ability to: Set a location for your new data service to be saved within your project. Select a data source from the dropdown listbox. Select the database type for the selected source (PointBase for the sample RDBMS) from the dropdown listbox. Select among the relational source types listed in the following table. ##### Types of available relational data sources
Relational Type | Description
--- | ---
Tables and Views | Displays all public tables and views in the selected data source.
Stored Procedures | Displays all public stored procedures in the selected data source.
SQL Statement | Allows creation of a SQL statement for extracting relational data from the data source.
Database Function | Allows creation of an XQuery function in a library data service based on build-in or custom database functions.
## Selecting Stored Procedure Objects for Import To create physical data services based on stored procedures: Select the Stored Procedures option. Click Next. A list of available stored procedures appears. Objects are grouped based on the relational data sources catalog and/or schema. You can use wildcards to support importing metadata on internal stored procedures. For example, entering the following string as a stored procedure filter:
    %TRIM%
retrieves metadata on the system stored procedure:
    STANDARD.TRIM
In such a situation you may want to make a "nonsense" entry in the Table/View field in order to avoid retrieving all tables and views in the database.
| Database-specific Catalog and Schema Considerations
Simply check the desired objects or their container, which will select all enclosed stored procedures. ##### Stored Procedure Objects Selected for Import ### Filtering SQL Objects Using Search The Search option available when creating a physical data service can be especially useful when: You know specific names of the data source objects you want to turn into data services. Your data source may be so large that a filter is needed. You may be looking for objects with specific naming characteristics such as:
    %audit2003%
The above search command retrieves all objects that contain the enclosed string. #### Using JDBC Syntax to Search SQL Objects You can search through available SQL objects using standard JDBC wildcard syntax. An underscore (_) creates a wildcard for an individual character. A percentage sign (%) indicates a wildcard for a string. Entries are case-sensitive. Another example:
    CUST%, PAY%
entered in the Tables/Views field the above search string returns all tables and views starting with either CUST or PAY. #### Special Considerations When Searching Stored Procedures If no items are entered for a particular field, all matching items are retrieved. For example, if no filtering entry is made for the Procedure field, all stored procedures in the data object will be retrieved. ## Configuring Selected Stored Procedures When Oracle Data Service Integrator introspects a stored procedure, the process may not be complete. For example, a required item of information such as a schema file or type cannot be determined. When such introspection problems occur, the stored procedure in question is highlighted in red. This setting means that additional information about the procedure must be provided by the user before the data service can be created. Your goal in correcting an "<unknown>" condition associated with a stored procedure is to bring the metadata obtained by the import wizard into conformance with the actual metadata of the stored procedure. In some cases this will be by correcting the location of the return type. In others you will need to adjust the type associated with an element of the procedure or add elements that were not found during the initial introspection of the stored procedure. ##### Configure Stored Procedure Dialog When several stored procedures are selected at the same time for physical data service creation, all the selected procedures must be adequately configured before any data services based on the procedures can be created.
| An alternative to configuring a incomplete stored procedure before proceeding is to use the wizard Back button to de-select the procedure in question.
4. Here are the steps involved in editing a set of stored procedures that will be imported as data services: Scroll through the list of selected procedures. For each procedure in red type, use the Edit button to correct the configuration settings. Make any other changes. (In some cases the data architect may know of requirements that are not identified during the introspection process.) Click Next when all the procedures in the selected set are valid.
   | If a stored procedure has only one return value and the value is either simple type or a RowSet which is mapping to an existing schema, no schema file is created. This stored procedure by definition become a library data service.
### Editing Stored Procedure Configurations Stored procedure configuration can be complicated. An understanding of the characteristics of the stored procedure in your database is an essential prerequisite. This section describes stored procedure options in detail. ##### Stored Procedure Metadata Editing Options Once in stored procedure configuration edit mode, options are available in three general areas: Parameters. Stored procedures requiring complex parameters can only be turned into data services once a schema has been identified. In addition, retrieved information on parameters required by a stored procedure may be incorrect. For example, additional parameters may be needed. Return type. Stored procedures returning complex data require a local schema to handle data returned from the call. In addition, retrieved information on stored procedure return types may be incorrect or it may be the case that no returned data is wanted. Row set. A row set identifies a schema and its associated library data service to hold information returned by a stored procedure. In some cases multiple row sets may need to be specified. ##### Stored Procedure Editing Options
Category | Option | Settings | Discussion
--- | --- | --- | ---
Parameters | Name | Parameter name | Editable.
| Mode | on/out/inout |
| Type | XQuery type | May be derived from the stored procedure. Primative XQuery type settings are also available.
| Schema location | XSD file | Schema file must be in the project.
Return type | Type | XQuery type or global type from selected schema |
| Schema location | XSD file | Schema file must be in the project.
Row set | Type | Data service | Derived from selected schema.
| Schema location | XSD file | Schema file must be in the project.
## Stored Procedure Configuration Reference The following topics provide detailed information regarding various configuration options associated with creating data services based on stored procedures. ### In Mode, Out Mode, Inout Mode In, Out, and Inout mode settings determine how a parameter passed to a stored procedure is handled.
Parameter Mode | Effect
--- | ---
In | Parameter is passed by reference or value.
Inout | Parameter is passed by reference.
Out | Parameter is passed by reference. However the parameter being passed is first initialized to a default value. If your stored procedure has an OUT parameter requiring a complex element, you may need to provide a schema.
### Procedure Profile Each element in a stored procedure is associated with a type. If the item is a simple type, you can simply choose from the pop-up list of types. If the type is complex, you may need to supply an appropriate schema. Click on the schema location button and either enter a schema pathname or browse to a schema. The schema must reside in your application. After selecting a schema, both the path to the schema file and the URI appear. #### Complex Parameter Types Complex parameter types are supported under only three conditions: As the output parameter As the Return type As a rowset #### About Rowsets A rowset type is a complex type. The rowset type contains a sequence of a repeatable elements (for example called CUSTOMER) with the fields of the rowset. In some cases the wizard can automatically detect the structure of a rowset and create an element structure. However, if the structure is unknown, you will need to provide it.
| All rowset-type definitions must conform to this structure.
The name of the rowset type can be: The parameter name (in case of a input/output or output only parameter). An assigned name. The referenced element name (result rowsets) in a user-specified schema. Not all databases support rowsets. In addition, JDBC does not report information related to defined rowsets. #### Using Rowset Information In order to create data services from stored procedures that use rowset information, you need to supply the correct ordinal (matching number) and a schema. If the schema has multiple global elements, select the one you want from the Type column. Otherwise the type used match the first global element in your schema file. The order of rowset information is significant; it must match the order in your data source. Use the Move Up / Move Down commands to adjust the ordinal number assigned to the rowset.
| XML types in data services generated from stored procedures do not display native types. However, you can view the native type in the Source editor; it is located in the pragma section.
#### Stored Procedure Version Support Only the most recent version of a particular stored procedure can be imported into Oracle Data Service Integrator. For this reason you cannot identify a stored procedure version number when creating a physical data service based on a stored procedure. Similarly, adding a version number for your stored procedure in the Source editor will result in a query exception. ### Supporting Stored Procedures with Nullable Input Parameter(s) If you know that an input parameter of a stored procedure is nullable (can accept null values), you can change the signature of the function in Source View to make such parameters optional by adding a question mark at end of the parameter. For example (question-mark (?) shown in bold):
    function myProc($arg1 as xs:string) ...
would become:
    function myProc($arg1 as xs:string?) ...
## Setting Properties for New Data Service Operations Each new entity data service is created with a Read function that contains all the metadata elements identified during data service creation. It can be thought of as comparable to the following construct in the relational world:
    select * from <table>
- Use the Properties dialog to: Optionally modify the operation name. Set the Public option (check if you want your function to be available to client applications). Set the kind of operation (in some cases only Read will be available). Set the Primary option (check if you want your function to be the primary of its type).
  | In some cases this option may not be available.
Select a common XML namespace for the entire data service or individual target namespaces for specific operations. Set the target namespace. The root element, which is read-only, is also displayed.
| Initially the root element name matches the name of the data service.
- ##### Setting Properties for New Data Service Functions ### Default Naming Conventions There are several default naming conventions associated with new data services: When a table, view, or other data source object is the source for a data service, the nominated name is wherever possible the same as the source object name. In some cases, however, names are adjusted to conform with XML naming conventions. Initially the root element name matches the name of the data service.
  | XML Name Conversion Considerations
## Verifying Data Service Composition On the Review New Data Service(s) page you can set, confirm or, optionally, change suggested data service names depending on the type of physical data service you are creating. ### Default Physical Data Service Names The nominated name for a new data service is, wherever possible, the same as the source object name. In some cases, however, names are adjusted to conform with XML naming conventions.
| XML Name Conversion Considerations
### About Automatic Data Service Name Changes Name conflicts occur when there is a data service of the same name present in the target directory. Name conflicts are highlighted in red. There are several situations where you will need to change the name of your data service: There already is a data service of the same name in your application. You are trying to create multiple data services with the same name. Data services always have the file extension:
    .ds
## Adding Operations to an Existing Data Service You can add SQL statement or stored procedure operations based on the same data source to an existing physical data service based a stored procedure.
| Add an External Function to an Existing Physical Data Service
- ##### Adding a Stored Procedure or SQL Statement to a Data Service ## Support for Stored Procedures in Popular Databases Each database vendor approaches stored procedures differently. Oracle Data Service Integrator support limitations generally reflect JDBC driver limitations. ### General Restrictions There are several restrictions that apply to stored procedures generally: Oracle Data Service Integrator does not support rowset as an input parameter. Only data types supported by Oracle Data Service Integrator can be imported as part of stored procedures.
  | For a list of database types supported by Oracle Data Service Integrator XQuery-SQL Mapping Reference
### Oracle Stored Procedure Support The following table describes data service creation support for Oracle stored procedures.
Term | Usage
--- | ---
Procedure types | Procedures Functions Packages
Parameter modes | Input only Output only Input/Output None
- Parameter data types | Any Oracle PL/SQL data type except: ROWID UROWID
  | When defining function signatures, note that the Oracle %TYPE and %ROWTYPE types must be translated to XQuery types that match the true types underlying the stored procedure's %TYPE and %ROWTYPE declarations. %TYPE declarations map to simple types; %ROWTYPE declarations map to rowset types.
Data returned from a function | Oracle supports returning PL/SQL data types such as NUMBER, VARCHAR, %TYPE, and %ROWTYPE as parameters.
Comments | The following identifies limitations associated with importing Oracle database procedure metadata. The data service creation process can only detect the data structure for cursors that have a binding PL/SQL record. For a dynamic cursor you need to manually specify the cursor schema. Data from a PL/SQL record structure cannot be retrieved due to an Oracle JDBC driver limitations. The Oracle JDBC driver supports rowset output parameters only if they are defined as reference cursors in a package. The Oracle JDBC driver does not support NATURALN and POSITIVEN as output only parameters.
### Sybase Stored Procedure Support The following table describes data service creation support for Sybase stored procedures.
Term | Usage
--- | ---
Procedure types | Procedures Grouped procedures Functions are categorized as a scalar or inline table-valued and multi-statement table-valued function. Inline table-valued and multi-statement table-valued functions return rowsets.
Parameter modes | Input only Output only
Parameter data types | For a list of database types supported by Oracle Data Service Integrator, see the XQuery-SQL Mapping Reference.
Data returned from a function | Sybase functions supports returning a single value or a table. Procedures return data in the following ways: As output parameters, which can return either data (such as an integer or character value). As return codes, which are always an integer value. As a rowset for each SELECT statement contained in the stored procedure or any other stored procedures called by that stored procedure. As a global cursor that can be referenced outside the stored procedure supports, returning single value or multiple values.
Comments | The following identifies limitations associated with importing Sybase database procedure metadata: The Sybase JDBC driver does not support input/output or output only parameters that are rowsets (including cursor variables). The Jconnect driver and some versions of the Oracle Sybase driver cannot detect the parameter mode of the procedure. In such a case, the return mode will be UNKNOWN, preventing importation of the metadata. To proceed, you need to set the correct mode.
### IBM DB2 Stored Procedure Support The following table describes data service creation support for IBM DB2 stored procedures.
Term | Usage
--- | ---
Procedure types | Procedures Functions Packages where each function is also categorized as a scalar, column, row, or table function. Here are additional details on function categorization: A scalar function returns a single-valued answer each time it is called. A column function is one which conceptually is passed a set of like values (a column) and returns a single-valued answer (AVG( )). A row function is a function that returns one row of values. A table function is a function that returns a table to the SQL statement that referenced it.
Parameter modes | Input only Output only Input/output
Parameter data types | For a list of database types supported by Oracle Data Service Integrator see the XQuery-SQL Mapping Reference. For a list of database types supported by Oracle Data Service Integrator, see the XQuery-SQL Mapping Reference.
Data returned from a function | DB2 supports returning a single value, a row of values, or a table.
Comments | The following identifies limitations associated with creating physical data services based on DB2 stored procedures: Column type functions are not supported. Rowsets as output parameters are not supported. The DB2 JDBC driver supports float, double, and decimal input only and output only parameters. Float, double, and decimal data types are not supported as input/output parameters.
### Microsoft SQL Server Stored Procedure Support The following table describes data service creation support for Microsoft stored procedures.
Term | Usage
--- | ---
Procedure types | SQL Server supports procedures, grouped procedures, and functions. Each function is also categorized as a scalar or inline table-valued and multi-statement table-valued function. Inline table-valued and multi-statement table-valued functions return rowsets.
Parameter modes | SQL Server supports input only and output only parameters.
Parameter data types | SQL Server procedures/functions support any SQL Server data type as a parameter. For a list of database types supported by Oracle Data Service Integrator, see the XQuery-SQL Mapping Reference.
Data returned from a function | SQL Server functions supports returning a single value or a table. Data can be returned in the following ways: As output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are rowsets that can be retrieved one row at a time). As return codes, which are always an integer value. As a rowset for each SELECT statement contained in the stored procedure or any other stored procedures called by that stored procedure.
Comments | The following identifies limitations associated with importing SQL Server procedure metadata. Result sets returned from SQL server (as well as those returned from Sybase) are not detected automatically. Instead you will need to manually add parameters as a result. The Microsoft SQL Server JDBC driver does not support rowset input/output or output only parameters (including cursor variables).

Document generated by Confluence on Jan 13, 2009 15:57