Create Physical Data Services from Stored Procedures
This page last changed on Nov 28, 2008.
Oracle Data Service Integrator Documentation > Data Services Developer's Guide How To Create Physical Data Services from Stored ProceduresStored 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 WizardThe following topics cover the actions necessary to create physical data services from relational stored procedures. Setting Up the Physical Data Service Creation WizardPhysical data services are created using a wizard. Physical Data Service Creation WizardStarting the WizardTo start the physical data service creation wizard:
Creating a New Physical Data ServiceSetting Up the Import Wizard for Relational ObjectsWhen importing a relational object available options include the ability to:
Types of available relational data sources
Selecting Stored Procedure Objects for ImportTo create physical data services based on stored procedures:
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% 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.
Simply check the desired objects or their container, which will select all enclosed stored procedures. Stored Procedure Objects Selected for ImportFiltering SQL Objects Using SearchThe Search option available when creating a physical data service can be especially useful when:
%audit2003% The above search command retrieves all objects that contain the enclosed string. Using JDBC Syntax to Search SQL ObjectsYou can search through available SQL objects using standard JDBC wildcard syntax.
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 ProceduresIf 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 ProceduresWhen 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 DialogWhen 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.
Here are the steps involved in editing a set of stored procedures that will be imported as data services:
Editing Stored Procedure ConfigurationsStored 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 OptionsOnce in stored procedure configuration edit mode, options are available in three general areas:
Stored Procedure Editing Options
Stored Procedure Configuration ReferenceThe following topics provide detailed information regarding various configuration options associated with creating data services based on stored procedures. In Mode, Out Mode, Inout ModeIn, Out, and Inout mode settings determine how a parameter passed to a stored procedure is handled.
Procedure ProfileEach 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 TypesComplex parameter types are supported under only three conditions:
About RowsetsA 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.
The name of the rowset type can be:
Not all databases support rowsets. In addition, JDBC does not report information related to defined rowsets. Using Rowset InformationIn 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.
Stored Procedure Version SupportOnly 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 OperationsEach 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:
The root element, which is read-only, is also displayed.
Setting Properties for New Data Service FunctionsDefault Naming ConventionsThere are several default naming conventions associated with new data services:
Verifying Data Service CompositionOn 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 NamesThe 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.
About Automatic Data Service Name ChangesName 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:
Data services always have the file extension: .ds Adding Operations to an Existing Data ServiceYou can add SQL statement or stored procedure operations based on the same data source to an existing physical data service based a stored procedure.
Adding a Stored Procedure or SQL Statement to a Data ServiceSupport for Stored Procedures in Popular DatabasesEach database vendor approaches stored procedures differently. Oracle Data Service Integrator support limitations generally reflect JDBC driver limitations. General RestrictionsThere are several restrictions that apply to stored procedures generally:
Oracle Stored Procedure SupportThe following table describes data service creation support for Oracle stored procedures.
Sybase Stored Procedure SupportThe following table describes data service creation support for Sybase stored procedures.
IBM DB2 Stored Procedure SupportThe following table describes data service creation support for IBM DB2 stored procedures.
Microsoft SQL Server Stored Procedure SupportThe following table describes data service creation support for Microsoft stored procedures.
|
![]() |
Document generated by Confluence on Jan 13, 2009 15:57 |