Create Physical Data Services Based on SQL Statements This page last changed on Nov 27, 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 Based on SQL Statements The following topics cover the actions necessary to create physical data services from SQL statements: Setting Up the Physical Data Service Creation Wizard Setting Up the Import Wizard for Relational Objects Entering a SQL Statement Setting Properties for New Library Functions Verifying Data Service Composition ## 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.
## Entering a SQL Statement You can build library data service functions based on SQL statements. The XQuery engine uses the statement to retrieve metadata which is, in turn, formulated into a function that can be used by other data services or made public. After selecting the SQL Statement option the next page of the wizard allows you to enter a SELECT statement and any necessary parameters. ##### SQL Statement Entry Dialog You can type or paste your SELECT statement into the SELECT statement box, indicating parameters with a question-mark symbol.
    ?
2. Using one of the Oracle Data Service Integrator samples, the following SELECT statement can be used: SELECT * FROM RTLCUSTOMER.CUSTOMER WHERE CUSTOMER_ID = ? For the parameter field, you would need to select a data type. In this case, CHAR or VARCHAR. Click Add to insert a new row into the parameter table, which indicates a parameter for the SQL statement. Select Parameter Type from the drop-down combo box.
   | Notes: When you run your query under Test View, you will need to supply the parameter in order for the query to run successfully. Oracle Data Service Integrator needs to be able to refer to the columns of the result of your SQL statement by name. To ensure that this is possible, you should use aliases as needed to ensure that computed columns indeed have usable names. The position of the parameter is significant.
In Test view run your query, supplying a parameter such as CUSTOMER3. ### 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 SQL statement.
| Add an External Function to an Existing Physical Data Service
##### Adding a Stored Procedure or SQL Statement to a Data Service ## Setting Properties for New Library Functions
| This general topic applies to setting properties for all types of library data service functions.
- Use the Review New Data Service Operations page to: Change the function name. Set the Public option (check if you want your function to be available to client applications). Set the kind of function (in some cases only one option 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. Set the target namespace. The root element, which is read only, is also displayed. ## 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

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