Create Physical Data Services from Relational Tables and Views This page last changed on May 05, 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 Relational Tables and Views The following topics describe how to create physical data services from relational tables and views: Setting Up the Physical Data Service Creation Wizard Setting Up the Import Wizard for Relational Objects Selecting SQL Table and View Objects for Import Setting Properties for New Data Service Operations Verifying Data Service Composition
| Prefer all related topics on a single page? Click here.
## 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 SQL Table and View Objects for Import To create a physical data service based on a relational table or view: Select the Tables and Views option Click Next. A list of available database table and view SQL objects appears. Objects are grouped based on the relational data sources catalog and/or schema. In the example of an RTLCUSTOMER catalog, the ADDRESS and CUSTOMER tables both become physical data services.
| Database-specific Catalog and Schema Considerations
Simply check the desired objects or their container, which will select all enclosed tables or views. ##### Table and View Objects Selected for Import If you click on an individual object such as ADDRESS or CUSTOMER, information describing the database's primary key(s), column name, type and nullability appears. For example the CUSTOMER table contains a CUSTOMER_ID field of type VARCHAR. That column is not nullable, meaning that it must be supplied with any updates. ##### Physical Data Service Properties ### 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. ## 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
# Database-specific Catalog and Schema Considerations Database vendors variously support database catalogs and schemas. ##### Vendor Support for Catalog and Schema Objects
Vendor | Catalog | Schema
--- | --- | ---
Oracle | Does not support catalogs. When specifying database objects, the catalog field should be left blank. | Typically the name of an Oracle user ID.
DB2 | If specifying database objects, the catalog field should be left blank. | Schema name corresponds to the catalog owner of the database, such as db2admin.
Sybase | Catalog name is the database name. | Schema name corresponds to the database owner.
Microsoft SQL Server | Catalog name is the database name. | Schema name corresponds to the catalog owner, such as dbo. The schema name must match the catalog or database owner for the database to which you are connected.
Informix | Does not support catalogs. If specifying database objects, the catalog field should be left blank. | Not needed.
PointBase | PointBase database systems do not support catalogs. If specifying database objects, the catalog field should be left blank. | Schema name corresponds to a database name.
# XML Name Conversion Considerations When a source name is encountered that does not fit within XML naming conventions, default generated names are converted according to rules described by the SQLX standard. Generally speaking, an invalid XML name character is replaced by its hexadecimal escape sequence (having the form xUUUU). For additional details see section 9.1 of the W3C draft version of this standard: http://www.sqlx.org/SQL-XML-documents/5WD-14-XML-2003-12.pdf

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