Understanding SQL Maps 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 # Understanding SQL Maps This topic describes mapping functions in data services to SQL objects. Overview Publishable Operations General Conditions See Also ## Overview A SQL Map enables you to publish data service functions as SQL objects (which are created when you specify the mapping). Using SQL Maps, you can expose data services modeled in Oracle Data Service Integrator as relational data sources. This enables you to use reporting tools (such as Crystal Reports and Microsoft Access, among others), Java applications, and development tools (such as Data Tools Platform or SQL Explorer) to access information from data services using SQL queries (through a JDBC client). ##### Sample SQL Map As the following figure shows, source data can be consolidated, integrated, and transformed using Oracle Data Service Integrator data services. The source data itself can come from disparate sources throughout the enterprise, including relational databases and web services, among others. Using SQL Maps you can, in turn, expose the data service operations as a relational data source accessible using SQL queries. This enables JDBC clients to access data consolidated through Oracle Data Service Integrator. ##### SQL Mapping Overview You can publish the following types of data service artifacts for SQL access: Data service functions, either with or without parameters. External database functions. These are database-specific functions which are either built into a particular commercial database or which were custom-designed on the database side and then declared to Oracle Data Service Integrator as external database functions. ## Publishable Operations SQL mappable data service functions can be thought of as relationally-compatible XQuery functions. Depending on their signature, you can publish such functions for use as SQL tables or stored procedures. The association between the function and the SQL object is defined at design time when creating a SQL map. The following summarizes the types of data service functions you can publish as SQL tables or stored procedures: You can map non-parameterized data service functions as SQL tables, and parameterized data service functions as stored procedures. You cannot map private or protected functions as part of a SQL Map. You cannot map procedures as part of a SQL Map. You can map library data service database functions to functions, but not to SQL tables and stored procedures.
| See Mapping Rules for details about permitted mappings.
- ## General Conditions The following general conditions apply when exposing data service operations as relational data sources: The exposed data service XQuery function signatures must only involve types that are supported by the relational (JDBC) type system. The structure of the underlying schema (the Return type) must have a relationally-compatible data shape, which means that the data service type cannot include repeating data (data elements with cardinality greater than 1). This is because SQL provides a traditional, two-dimensional approach to data access, as opposed to the multi-level, non-normalized approach defined by XML.
  | For more information about creating data services with flat schemas, see Create a Data Service with a Flat Return Type.
You cannot map data service operations that return scalar (primitive) values as SQL tables or stored procedures. ## See Also ##### How Tos Map Functions and Procedures to SQL Objects ##### Reference Mapping Rules Constraints on Publishing Data Service Objects to SQL

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