1 Introduction to Data Services

1.2.1.1 Prerequisites

A prerequisite to configuring the retail dataspace sample application is to have the Oracle Data Service Integrator installed on a supported platform.

Choose the guide for the version you are running:

• Installation Guide for OSDI

1.2.1.2 About WorkSpace Studio, Data Services Studio, and Eclipse

This tutorial uses the version of Eclipse that is installed with Oracle Data Service Integrator.

The Eclipse framework often provides multiple ways of achieving same result. In many cases there is no "correct" or "better" way. In other words, there are often many paths to the same results.

1.2.1.3 Start WorkSpace Studio

Open ODSI from Eclipse by selecting:

Window > Open Perspective > Oracle Data Service Integrator

1.2.1.3.1 Select a Workspace

OSDI Studio projects are called dataspace projects. These Projects in turn are located in a workspace folder.

The first step in creating a dataspace is to select a workspace.

1. Use the default location:

   Install Retail Dataspace

2. Click OK.

Figure 1-2 Oracle Data Service IntegratorSelecting a Workspace

Description of "Figure 1-2 Oracle Data Service IntegratorSelecting a Workspace"

In the Samples section click on:

Install Retail Dataspace Sample

Figure 1-3 Retail Dataspace Server Configuration Dialog

Description of "Figure 1-3 Retail Dataspace Server Configuration Dialog"

1. Click Finish. This imports the RetailDataspace project and finds or creates a server for the Oracle Data Service Integrator sample domain and associates it with the project.

2. Answer Yes to the question about associating your project with the Oracle Data Service Integrator perspective.

Figure 1-4 Initial Oracle Data Service Integrator Perspective

Description of "Figure 1-4 Initial Oracle Data Service Integrator Perspective"

1.2.1.4 Start the Server

An Oracle Data Service Integrator-enabled server is a version of WebLogic Server with additional functionality to support Oracle Data Service Integrator deployment and runtime. The Oracle Data Service Integrator server must be running in order to access sample data and to deploy your project.

To start your server from Studio:

1. Locate the Servers window. If it isn't visible, use the following option command:

   Window > Show View > Servers

2. In the Servers window locate the Oracle Data Service Integrator Samples Server (this may be the only server listed). Notice that its status is Stopped.

3. Right-click on the server name and select Start. (The start-up operation can take several minutes.) Notice the running log of server startup actions in the Console window.

   You can also start the server by selecting the server and clicking the Start icon.

   Figure 1-5 Server Window

   
   Description of "Figure 1-5 Server Window"
   
   

1.2.1.5 Deploy Your Projects

Each project should be deployed to validate the installation.

1. Right-click on the server.

2. Choose Publish from the menu. A message should appear indicating successful deployment.

3. Click OK.

Also deploy the RetailDataspace project.

1.2.1.6 Create the Retail Dataspace Sample Web Application

If Eclipse for WebLogic Platform 10.2 is installed into the same BEA_HOME which contains ALDSP_HOME, you can create the a web-based sample application.

Follow these steps to access the web-based sample application:

1. To make sure the necessary dataspaces are deployed, redeploy (right-click > Deploy Project):

   • ElectronicsWS and

     RetailDataspace projects

2. From the WorkSpace 1.1 menu select:

   Window > Show View > Servers

3. Locate the option:

   Retail Dataspace Sample Web Application (WebLogic Eclipse only)

4. Click Next, then Finish.

5. If asked if you want to open the OSDI Studio Perspective, click Yes.

6. In the Project Explorer view, right-click on the RTLSelfService project, and choose:

   Run As > Run on Server

   This will initially deploy your projects and then open the sample Avitek login page.

   Figure 1-6 Avitek Login Page

   
   Description of "Figure 1-6 Avitek Login Page"
   
   

7. Mouse over one of the names and log in. After a few moments information about the fictitious customer will appear.

   Figure 1-7 Avitek Welcome Page

   
   Description of "Figure 1-7 Avitek Welcome Page"
   
   

1.2.1.7 See Also

• Example: How to Create Your First Data Services

• Retail Database Sample Application Guide

1.2.2.1 Prerequisites

A prerequisite to configuring the retail dataspace sample application is to have the ODSI Data Services Studio installed on a supported platform.

1.2.2.2 About WorkSpace Studio, Data Services Studio, and Eclipse

This tutorial uses the version of Eclipse that is installed with ODSI.

The Eclipse framework often provides multiple ways of achieving same result. In many cases there is no "correct" or "better" way. In other words, there are often many paths to the same results.

1.2.2.3 Start Studio

Open Studio using the following Windows Start menu command:

Start > All Programs > BEA Products > BEA AquaLogic Data Services Platform 3.0 > Data Services Studio

1.2.2.3.1 Select a Workspace

Figure 1-8 Selecting a Workspace

Description of "Figure 1-8 Selecting a Workspace"

In the Install Sample Applications section click on:

Retail Dataspace Sample

Figure 1-9 Retail Dataspace Server Configuration Dialog

Description of "Figure 1-9 Retail Dataspace Server Configuration Dialog"

1. Click Finish. This will import the RetailDataspace project and find or create a server for the OSDI Studio sample domain and associate it with the project.

2. Click Yes if you are asked about associating your project with the OSDI Studio perspective.

Figure 1-10 Initial OSDI Studio Perspective

Description of "Figure 1-10 Initial OSDI Studio Perspective"

1.2.2.4 Start the Server

An OSDI Studio-enabled server is a version of WebLogic Server with additional functionality to support OSDI Studio deployment and runtime. The OSDI Studio server must be running in order to access sample data and to deploy your project.

1. Locate the Servers window. If it isn't visible, use the following option command:

   Window > Show View > Servers

2. In the Servers window locate the OSDI Studio Samples Server (this may be the only server listed). Notice that its status is Stopped.

3. Right-click on the server name and select Start. (The start-up operation can take several minutes.) Notice the running log of server startup actions in the Console window.

   Figure 1-11 Server Window

   
   Description of "Figure 1-11 Server Window"
   
   

1.2.2.5 Deploy Your Projects

Each project should be deployed to validate the installation.

1. Right-click on the server.

2. Choose Publish from the menu. A message should appear indicating successful deployment.

3. Click OK.

Also deploy the RetailDataspace project.

1.2.2.6 See Also

• Install the OSDI Studio Sample Retail Application

• Example: How to Create Your First Data Services

• Retail Dataspace Sample Application Guide

1.3.1.1 Requirements

The requirement for the demonstration project are to develop a logical data service from several physical data services. When run by a client, the data service will return a consolidated view of a particular customer's orders, as well as all the items in each order.

1.3.1.2 Before You Begin

Before you can begin the tutorial make sure you:

• Properly install Oracle Data Service Integrator.

  Reference:

  Oracle Data Service Integrator Installation Guide

• Configure the Retail Dataspace Sample Application.

  • Configure the Retail Dataspace Sample Application

  • Configure the Retail Dataspace Sample Application for ODSI

• Have the application open in Eclipse for WebLogic and the Oracle Data Service Integrator-enabled Oracle WebLogic 10.3 server running.

  Also describe in "Configure the Retail Dataspace Sample Application."

Figure 1-12 Oracle Data Service Integrator Default Perspective After Adding myDataspace

Description of "Figure 1-12 Oracle Data Service Integrator Default Perspective After Adding myDataspace"

1.3.2.1 Set Up a Folder for Physical Data Services

Data services are typically created inside project folders. The recommended first step in creating one or several data services is to create containers (folders).

In this tutorial two folders will be created:

• One for physical data services.

• One for logical data services.

1. In the Project Explorer window right-click on myDataspace, choose:

   New > Folder

2. Name your folder:

   logical

3. Click the Finish button.

4. Create another folder under myDataspace named:

   physical

5. Click the Finish button.

   Physical data services represent physical data such as tables in relational databases or web services. Logical data services are build upon existing physical or logical data services.

   • Chapter 3, "Creating and Updating Physical Data Services"

   • Chapter 4, "Designing Logical Data Services"

   Figure 1-14 Creating a New Folder

   
   Description of "Figure 1-14 Creating a New Folder"
   
   

6. Right-click on your new physical folder and choose:

   New > Physical Data Service

1.3.3.1 Select a Data Source

The select a data source dialog initially allows you to select a data source type (such as relational or web service). Once that selection is made, additional options appear. The following table lists the actions required to select the relational data sources that will be used throughout this tutorial.

Format similar to that shown in the table below is used to describes the steps needed to work through multi-page wizards.

Your new data services appear in your physical folder in the Project Explorer.

Figure 1-15 Newly Created Data Services

Description of "Figure 1-15 Newly Created Data Services"

If you expand your new data services you will see that each physical data service has been created with functions corresponding to standard relational operations. For example the CUSTOMER.ds data service contains the following operations:

• createCUSTOMER(CUSTOMER)

• CUSTOMER()

• deleteCUSTOMER(CUSTOMER)

• getADDRESS(CUSTOMER)

• updateCUSTOMER(CUSTOMER)

Some relationship operations (such as getADDRESS(CUSTOMER)) have been created automatically. This operation returns an ADDRESS type when it is passed a CUSTOMER type as a parameter. The operation can be inferred during the data service creation process because ADDRESS contains a foreign key that is a unique custID in the CUSTOMER data service (and underlying source). Relationship functions are described in detail in the Modeling Data Services Relationships section.

1.3.3.2 Schemas Directory

You should find a schemas folder adjacent to the newly created data services. This folders contains schema files created during the metadata import process. For relational sources, schemas are created for both the data source (table or view) and the primary keys found during the introspection of the relational source. For example:

• CUSTOMER.xsd

• CUSTOMER_KEY.xsd

If you look in the schemas directory you will see that for each physical data service created, two schemas were created. One representing the physical data service and the other to describe the primary keys in the data source.

Figure 1-16 Expanded View of Project Explorer

Description of "Figure 1-16 Expanded View of Project Explorer"

When a logical entity data service is created, it is either:

• Associated with an existing schema or

• A return type associated with a function becomes the basis of a generated XML type that is then associated with the data service.

1.3.3.3 Publish Your Projects

Using Eclipse for WebLogic, you can publish your dataspace projects to a server when it is ready for testing and debugging. Publishing is also useful during the project development phase because in its default configuration, when you publish a project in Eclipse for WebLogic, it is automatically built and validated. The validation process identifies error conditions, if any.

A dialog displays the progress and, upon successful completion, the status of the server changes to Synchronized.

1.3.4.1 Attempt To Publish Your Dataspace Project

There are times when attempts to publish your data service under development will not be successful. This is expected since as you create your query in the Query Map, source is created simultaneously. (When a data service is in such a state, you will notice a red x on its associated icon in Project Explorer.)

Figure 1-17 Project After Unsuccessful Publish Effort

Description of "Figure 1-17 Project After Unsuccessful Publish Effort"

Unlike the previously successful publish operation, you will now get a message indicating that your project contains build errors and cannot be published.

In this case your newly created CUST_ORDERS_ITEMS data service is invalid. You can verify this several ways after clicking OK.

• Inspect your code by clicking on the Source tab.

• Double-click on the error reported in the Problems window.

• Inspect the contents of the Error log window.

Figure 1-18 Incomplete Logical Data Service Validation Error

Description of "Figure 1-18 Incomplete Logical Data Service Validation Error"

Although an error condition exists, you can continue creating on your data service.

1.3.4.2 Bottom Up or Top Down

Data services can be designed from the top-down or bottom-up. The following table compares these two approaches.

This tutorial uses a bottom-up design.

1.3.4.3 Add an Operation to CUST_ORDERS_ITEMS

The next step is to add a read function to your new data service that will return a document containing all the orders placed by a particular customer, and all the items in each order.

To add your new function:

1. Select the Overview tab.

2. Right-click in the CUST_ORDERS_ITEMS data service's work area.

3. Choose Add Operation... from available options.

Figure 1-19 Creating a New Operation

Description of "Figure 1-19 Creating a New Operation"

The next steps will create a publicly available Read function for your new data service.

Figure 1-20 Add Operation Dialog

Description of "Figure 1-20 Add Operation Dialog"

Every artifact and artifact element in Overview has properties. In some cases these properties — such as name and type — are either directly editable or adjustable through dropdown list boxes. The Properties window is, by default, visible in the Eclipse for WebLogic perspective. If the Properties window is not visible you can retrieve it using the command:

Window > Reset Perspective

Figure 1-21 New Data Service Operation and Properties

Description of "Figure 1-21 New Data Service Operation and Properties"

1.3.4.4 Building Your Query

Click on the custOrdersItemsByLastName function name in the work area to enter Query Map mode

Figure 1-22 Initial Query View

Description of "Figure 1-22 Initial Query View"

Changes made in the Query Map editor are immediately reflected in source and vice-versa. When there is an error is source, the Query map may not be available. You can typically correct such a condition using the Undo menu option or Ctrl-Z. Alternatively, click the Source tab and edit as needed.

1.3.4.5 Building Your FLWR Statement Graphically

XQueries are often described as being build upon "FLWR" statements:

• For/Let

• Where

• Return

Changes made in source are immediately rendered graphically in the query map.

1.3.4.5.1 Adding Data Sources to Query View - the For/Let Statements

It is through the Query Map that you can bring together representations of existing data sources and associate their elements with the Return type of a new data service.

In the current example your new data service is to provide a consolidated view drawn from the CUSTOMER, CUSTOMER_ORDER, and CUSTOMER_ORDER_LINE_ITEM data services. The Read functions from these physical data services therefore need to be represented in the work area of the new data service.

Follow these steps to add these representations to your Query map:

1. In the physical folder expand the following data services:

   • CUSTOMER.ds

   • CUSTOMER_ORDER.ds

   • CUSTOMER_ORDER_LINE_ITEM.ds

2. Drag and drop the Read operations of the following data services CUSTOMER, CUSTOMER_ORDER, and CUSTOMER_ORDER_LINE_ITEM into the query work area. Read operations are identified by the a white-arrow-with-green-ball icon as shown below.

   Figure 1-23 Read Function Icon

   
   Description of "Figure 1-23 Read Function Icon"
   
   

Each of these operational building blocks will become for statements in the XQuery description of your new data service.

Figure 1-24 Data Source Representations in Work Area

Description of "Figure 1-24 Data Source Representations in Work Area"

1.3.4.6 Add a Parameter

Parameters can be added when your operation is created or in the Query Map. Parameters can be of simple (primitive) type or complex, such as the XMLtype from another data service.

In this case you create a single xs:string parameter that will allow retrieval of one or more records by a customer's last name.

To add a parameter:

1. In the Query Map work area right-click in a blank area and select:

   Edit Signature...

2. If asked to save modified resources click OK.

3. If asked to save modified resources click

4. Complete the Edit Function Signature... dialog.

Figure 1-25 Add New Parameter Dialog

Description of "Figure 1-25 Add New Parameter Dialog"

The last_name parameter appears in the work area.

xquery version "1.0" encoding "UTF-8";

(:: pragma ... ::)

declare namespace cus2= "ld:physical/CUSTOMER";
declare namespace cus1= "ld:physical/CUSTOMER_ORDER";
declare namespace ust= "custOrdersItems";
declare namespace cus= "ld:physical/CUSTOMER_ORDER_LINE_ITEM";
declare namespace tns="ld:logical/CUST_ORDERS_ITEMS";

(:: pragma ... ::)

declare function tns:custOrdersItemsByLastName(){
for $CUSTOMER in cus:CUSTOMER()
for $CUSTOMER_ORDER in cus1:CUSTOMER_ORDER()
for $CUSTOMER_ORDER_LINE_ITEM in cus2:CUSTOMER_ORDER_LINE_ITEM()
return
    ()
};

1.3.4.7 Map Elements to the Return Type

Three icons associated with projecting elements to the Return type appear above the Query Map work area. (You may need to widen your window to see all three icons.)

Table 1-6 Mapping Mode Icons

Icon	Mapping Mode	Keyboard Equivalent	Description
	Value	None	Maps simple or complex elements to identical values in the Return type. For example, a simple element can be projected to a comparable simple element in the Return type.
	Overwrite	Ctrl-Drag object	Overwrites simple or complex element in the Return type with the selected simple or complex element.
	Append	Ctrl-Shift-Drag object	Maps simple or complex object as a child to the Return type element it is associated with.

You will use these options to map representations of source data to the Return type of your new data service.

1.3.4.8 Populating the Return Clause

From the three mapping icons in the Select operation line at the top of the query map, select the second of the three icons, Overwrite mapping:

Drag the CUSTOMER complex element

CUSTOMER*

over the global element placeholder labeled "empty" in the Return type.

Figure 1-26 Mapping Complex Element to Return Type

Description of "Figure 1-26 Mapping Complex Element to Return Type"

Right-click on the new CUSTOMER element in the Return type and select:

Expand Complex Mapping

Figure 1-27 Expanding Complex Mapping

Description of "Figure 1-27 Expanding Complex Mapping"

This gesture is a shortcut for drawing lines from each element for the statement into the Return type. This gesture is also necessary if you want to add a complex child element to the type. Notice that individual mapping lines now connect each element in the For: node with an element in the Return type. Individual mappings can be added or deleted using drag-and-drop or the Delete key, respectively. The next steps will add elements from the CUSTOMER_ORDER data service to your Return type.

1. Select Append Mapping mode.

2. Drag the CUSTOMER_ORDER complex element CUSTOMER_ORDER* over the CUSTOMER element in the Return type. Notice that the CUSTOMER_ORDER global element and the names of its children now appear after the CUSTOMER elements.

3. Expand complex mapping for the CUSTOMER_ORDER global element.

4. From the work area drag the CUSTOMER_ORDER_LINE_ITEM complex element over the CUSTOMER_ORDER element in the Return type.

5. Expand complex mapping for these elements.

Figure 1-28 Adding Child Elements to Return Type

Description of "Figure 1-28 Adding Child Elements to Return Type"

1.3.4.9 Set Statement Scoping

Click the Source tab to inspect your generated code. Notice that the Return type contains all three For: statements.

declare function tns:custOrdersItemsByLastName($last_name as xs:string) {
for $CUSTOMER in cus:CUSTOMER()
for $CUSTOMER_ORDER in cus1:CUSTOMER_ORDER()
for $CUSTOMER_ORDER_LINE_ITEM in cus2:CUSTOMER_ORDER_LINE_ITEM()
return
    <ust:CUSTOMER>
        <CUSTOMER_ID>{fn:data($CUSTOMER/CUSTOMER_ID)}</CUSTOMER_ID>
        <FIRST_NAME>{fn:data($CUSTOMER/FIRST_NAME)}</FIRST_NAME>
        <LAST_NAME>{fn:data($CUSTOMER/LAST_NAME)}</LAST_NAME>
        <CUSTOMER_SINCE>{fn:data($CUSTOMER/CUSTOMER_SINCE)}</CUSTOMER_SINCE>
        <EMAIL_ADDRESS>{fn:data($CUSTOMER/EMAIL_ADDRESS)}</EMAIL_ADDRESS>
        <TELEPHONE_NUMBER>{fn:data($CUSTOMER/TELEPHONE_NUMBER)}</TELEPHONE_NUMBER>
        <SSN?>{fn:data($CUSTOMER/SSN)}</SSN>
        <BIRTH_DAY?>{fn:data($CUSTOMER/BIRTH_DAY)}</BIRTH_DAY>
        <DEFAULT_SHIP_METHOD?>{fn:data($CUSTOMER/DEFAULT_SHIP_METHOD)}</DEFAULT_SHIP_METHOD>
        <EMAIL_NOTIFICATION?>{fn:data($CUSTOMER/EMAIL_NOTIFICATION)}</EMAIL_NOTIFICATION>
        <NEWS_LETTTER?>{fn:data($CUSTOMER/NEWS_LETTTER)}</NEWS_LETTTER>
        <ONLINE_STATEMENT?>{fn:data($CUSTOMER/ONLINE_STATEMENT)}</ONLINE_STATEMENT>
        <LOGIN_ID?>{fn:data($CUSTOMER/LOGIN_ID)}</LOGIN_ID>
        {
                <ust:CUSTOMER_ORDER>
                    <ORDER_ID>{fn:data($CUSTOMER_ORDER/ORDER_ID)}</ORDER_ID>
                    <C_ID>{fn:data($CUSTOMER_ORDER/C_ID)}</C_ID>
                    <ORDER_DT>{fn:data($CUSTOMER_ORDER/ORDER_DT)}</ORDER_DT>
                    <SHIP_METHOD_DSC>{fn:data($CUSTOMER_ORDER/SHIP_METHOD_DSC)}</SHIP_METHOD_DSC>
                    <HANDLING_CHRG_AMT>{fn:data($CUSTOMER_ORDER/HANDLING_CHRG_AMT)} 
                         </HANDLING_CHRG_AMT>
                    <SUBTOTAL_AMT>{fn:data($CUSTOMER_ORDER/SUBTOTAL_AMT)}</SUBTOTAL_AMT>
                    <TOTAL_ORDER_AMT>{fn:data($CUSTOMER_ORDER/TOTAL_ORDER_AMT)}</TOTAL_ORDER_AMT>
                    <SALE_TAX_AMT>{fn:data($CUSTOMER_ORDER/SALE_TAX_AMT)}</SALE_TAX_AMT>
                    <SHIP_TO_ID>{fn:data($CUSTOMER_ORDER/SHIP_TO_ID)}</SHIP_TO_ID>
                    <SHIP_TO_NM>{fn:data($CUSTOMER_ORDER/SHIP_TO_NM)}</SHIP_TO_NM>
                    <BILL_TO_ID>{fn:data($CUSTOMER_ORDER/BILL_TO_ID)}</BILL_TO_ID>
                    <ESTIMATED_SHIP_DT>{fn:data($CUSTOMER_ORDER/ESTIMATED_SHIP_DT)} 
                         </ESTIMATED_SHIP_DT>
                    <STATUS>{fn:data($CUSTOMER_ORDER/STATUS)}</STATUS>
                    <TRACKING_NO?>{fn:data($CUSTOMER_ORDER/TRACKING_NO)}</TRACKING_NO>
                    <DATE_INT?>{fn:data($CUSTOMER_ORDER/DATE_INT)}</DATE_INT>
                    {
                            <ust:CUSTOMER_ORDER_LINE_ITEM>
                                <LINE_ID>{fn:data($CUSTOMER_ORDER_LINE_ITEM/LINE_ID)}</LINE_ID>
                                <ORDER_ID>{fn:data($CUSTOMER_ORDER_LINE_ITEM/ORDER_ID)}</ORDER_ID>
                                <PROD_ID>{fn:data($CUSTOMER_ORDER_LINE_ITEM/PROD_ID)}</PROD_ID>
                                <PROD_DSC>{fn:data($CUSTOMER_ORDER_LINE_ITEM/PROD_DSC)}</PROD_DSC>
                                <QUANTITY>{fn:data($CUSTOMER_ORDER_LINE_ITEM/QUANTITY)}</QUANTITY>
                                <PRICE>{fn:data($CUSTOMER_ORDER_LINE_ITEM/PRICE)}</PRICE>
                                <STATUS>{fn:data($CUSTOMER_ORDER_LINE_ITEM/STATUS)}</STATUS>
                            </ust:CUSTOMER_ORDER_LINE_ITEM>
                    }
                </ust:CUSTOMER_ORDER>
        }
    </ust:CUSTOMER>
};

Using the Query Map you can adjust this quite easily by changing the scoping of the subordinate data services in the Return type, as shown in the following steps.

Figure 1-29 Adjusting Scoping Rules in the Return Type

Description of "Figure 1-29 Adjusting Scoping Rules in the Return Type"

1. Return to Query Map mode.

2. With your mouse select the zone icon in the node:

   For: $CUSTOMER_ORDER()

3. Drag the zone icon over the corresponding CUSTOMER_ORDER element in the Return type.

   Notice that the zone line from the CUSTOMER_ORDER node moves to the subordinate complex type (CUSTOMER_ORDER).

4. Drag the zone icon of CUSTOMER_ORDER_LINE_ITEM to its corresponding element in the Return type.

Figure 1-30 Nested Zoning in the Return Type

Description of "Figure 1-30 Nested Zoning in the Return Type"

Switch to Source view to verify that the for statements are nested in the Return clause. Now, when a parameter is passed with the operation, all the customers with a particular last name will be returned which contains orders and order line items associated with that customer.

declare function tns:custOrdersItemsByLastName($last_name as xs:string) {
for $CUSTOMER in cus:CUSTOMER()

return
    <ust:CUSTOMER>
        <CUSTOMER_ID>{fn:data($CUSTOMER/CUSTOMER_ID)}</CUSTOMER_ID>
        <FIRST_NAME>{fn:data($CUSTOMER/FIRST_NAME)}</FIRST_NAME>
        <LAST_NAME>{fn:data($CUSTOMER/LAST_NAME)}</LAST_NAME>
        <CUSTOMER_SINCE>{fn:data($CUSTOMER/CUSTOMER_SINCE)}</CUSTOMER_SINCE>
        <EMAIL_ADDRESS>{fn:data($CUSTOMER/EMAIL_ADDRESS)}</EMAIL_ADDRESS>
        <TELEPHONE_NUMBER>{fn:data($CUSTOMER/TELEPHONE_NUMBER)}</TELEPHONE_NUMBER>
        <SSN?>{fn:data($CUSTOMER/SSN)}</SSN>
        <BIRTH_DAY?>{fn:data($CUSTOMER/BIRTH_DAY)}</BIRTH_DAY>
        <DEFAULT_SHIP_METHOD?>{fn:data($CUSTOMER/DEFAULT_SHIP_METHOD)}</DEFAULT_SHIP_METHOD>
        <EMAIL_NOTIFICATION?>{fn:data($CUSTOMER/EMAIL_NOTIFICATION)}</EMAIL_NOTIFICATION>
        <NEWS_LETTTER?>{fn:data($CUSTOMER/NEWS_LETTTER)}</NEWS_LETTTER>
        <ONLINE_STATEMENT?>{fn:data($CUSTOMER/ONLINE_STATEMENT)}</ONLINE_STATEMENT>
        <LOGIN_ID?>{fn:data($CUSTOMER/LOGIN_ID)}</LOGIN_ID>
        {
                for $CUSTOMER_ORDER in cus1:CUSTOMER_ORDER()
                return
                <ust:CUSTOMER_ORDER>
                    <ORDER_ID>{fn:data($CUSTOMER_ORDER/ORDER_ID)}</ORDER_ID>
                    <C_ID>{fn:data($CUSTOMER_ORDER/C_ID)}</C_ID>
                    <ORDER_DT>{fn:data($CUSTOMER_ORDER/ORDER_DT)}</ORDER_DT>
                    <SHIP_METHOD_DSC>{fn:data($CUSTOMER_ORDER/SHIP_METHOD_DSC)}</SHIP_METHOD_DSC>
                    <HANDLING_CHRG_AMT>{fn:data($CUSTOMER_ORDER/HANDLING_CHRG_AMT)} 
                         </HANDLING_CHRG_AMT>
                    <SUBTOTAL_AMT>{fn:data($CUSTOMER_ORDER/SUBTOTAL_AMT)}</SUBTOTAL_AMT>
                    <TOTAL_ORDER_AMT>{fn:data($CUSTOMER_ORDER/TOTAL_ORDER_AMT)}</TOTAL_ORDER_AMT>
                    <SALE_TAX_AMT>{fn:data($CUSTOMER_ORDER/SALE_TAX_AMT)}</SALE_TAX_AMT>
                    <SHIP_TO_ID>{fn:data($CUSTOMER_ORDER/SHIP_TO_ID)}</SHIP_TO_ID>
                    <SHIP_TO_NM>{fn:data($CUSTOMER_ORDER/SHIP_TO_NM)}</SHIP_TO_NM>
                    <BILL_TO_ID>{fn:data($CUSTOMER_ORDER/BILL_TO_ID)}</BILL_TO_ID>
                    <ESTIMATED_SHIP_DT>{fn:data($CUSTOMER_ORDER/ESTIMATED_SHIP_DT)} 
                         </ESTIMATED_SHIP_DT>
                    <STATUS>{fn:data($CUSTOMER_ORDER/STATUS)}</STATUS>
                    <TRACKING_NO?>{fn:data($CUSTOMER_ORDER/TRACKING_NO)}</TRACKING_NO>
                    <DATE_INT?>{fn:data($CUSTOMER_ORDER/DATE_INT)}</DATE_INT>
                    {
                           for $CUSTOMER_ORDER_LINE_ITEM in cus2:CUSTOMER_ORDER_LINE_ITEM()
                           return
                           <ust:CUSTOMER_ORDER_LINE_ITEM>
                                <LINE_ID>{fn:data($CUSTOMER_ORDER_LINE_ITEM/LINE_ID)}</LINE_ID>
                                <ORDER_ID>{fn:data($CUSTOMER_ORDER_LINE_ITEM/ORDER_ID)}</ORDER_ID>
                                <PROD_ID>{fn:data($CUSTOMER_ORDER_LINE_ITEM/PROD_ID)}</PROD_ID>
                                <PROD_DSC>{fn:data($CUSTOMER_ORDER_LINE_ITEM/PROD_DSC)}</PROD_DSC>
                                <QUANTITY>{fn:data($CUSTOMER_ORDER_LINE_ITEM/QUANTITY)}</QUANTITY>
                                <PRICE>{fn:data($CUSTOMER_ORDER_LINE_ITEM/PRICE)}</PRICE>
                                <STATUS>{fn:data($CUSTOMER_ORDER_LINE_ITEM/STATUS)}</STATUS>
                            </ust:CUSTOMER_ORDER_LINE_ITEM>
                    }
                </ust:CUSTOMER_ORDER>
        }
    </ust:CUSTOMER>

};

1.3.4.10 Creating Joins - the Where Clauses

Where clauses satisfy either specific conditions (such as where $i=5) or join conditions such as:

where $CUSTOMER_ORDER/ORDER_ID eq $CUSTOMER_ORDER_LINE_ITEM/ORDER_ID

1. Return to Query Map mode.

2. To establish join conditions among your data sources, drag the specified element in one For: statement to the specified element in the target For statement:

   Source and element	Target and element
   $CUSTOMER/CUSTOMER_ID	$CUSTOMER_ORDER/C_ID
   $CUSTOMER_ORDER/ORDER_ID	$CUSTOMER_ORDER_LINE_ITEM/ORDER_ID

   
   

Figure 1-31 Setting Up a Join Condition

Description of "Figure 1-31 Setting Up a Join Condition"

You can verify your first join clause by clicking on target (CUSTOMER_ORDER) object. Alternatively, you can look in Source view to verify that the new where clause is modifying the CUSTOMER_ORDER_LINE_ITEM type.

1.3.4.11 Associate a Parameter with a For Node

An additional necessity where the condition that directs the query results to a particular customer can be created by adding a parameter to an element in a node. Parameters can be simple or complex.

This project requires use of a single parameter: last_name.

In the Query Map drag the element:

string string

in the $last_name parameter over the LAST_NAME element in the CUSTOMER node.

Figure 1-32 Mapped Parameter and Where Clause

Description of "Figure 1-32 Mapped Parameter and Where Clause"

The results of this operation can also be viewed in the Source tab.

declare function tns:custOrdersItemsByLastName($last_name as xs:string) / 
as element(ust1:CUST_ORDERS_ITEMS)* {
for $CUSTOMER in cus:CUSTOMER() where $last_name eq $CUSTOMER/LAST_NAME
return...

In Source you will also notice that the for statements now contain where clauses based on your graphical gestures.

for $CUSTOMER in cus:CUSTOMER()
where $last_name eq $CUSTOMER/LAST_NAME
return
...
for $CUSTOMER_ORDER in cus1:CUSTOMER_ORDER()
where $CUSTOMER/CUSTOMER_ID eq $CUSTOMER_ORDER/C_ID
return
...
for $CUSTOMER_ORDER_LINE_ITEM in cus2:CUSTOMER_ORDER_LINE_ITEM()
where $CUSTOMER_ORDER/ORDER_ID eq $CUSTOMER_ORDER_LINE_ITEM/ORDER_ID
return
...

1.3.5.1 Modifying the XML Type

When an XML Type is generated, complex elements by default return a single instance of their type (for example, one CUSTOMER_ORDER will be returned even if there are many).

In order to return all customer orders and all of each orders' line items minor changes to the data service's XML type are needed. The XML markup for this is:

maxOccurs="unbounded"

In other words, the element returns n, any number of document fragments that meet the criteria.

To modify your new CUST_ORDERS_ITEMS XML Type:

1. Click on the Overview tab, if it is not already selected.

2. Right-click on the topmost element in the XML type: CUST_ORDER_ITEMS.

3. Select Edit Schema. The Eclipse schema editor opens.

4. Click the schema editor's Source tab (below the editor's work area).

5. Locate the first qualified element: CUSTOMER_ORDER.

6. Place your cursor where you want to add the statement (just between the double-quote and the closing angle bracket ( > ) at the end of the line)

7. Enter a space.

8. Activate the code assistant with the combination:

   Ctrl + spacebar

   You will get a code completion dialog.

9. Perform the Ctrl+space operation twice, once for the max_occurs, and again to add the unbounded statement. The line now appears as:

   <xs:element form="qualified" name="CUSTOMER_ORDER" maxOccurs="unbounded">
   

10. Follow Steps 5-9 for the second qualified element, CUSTOMER_ORDER_LINE_ITEM.

11. Save the CUST_ORDERS_ITEMS.xsd file.

    File > Close

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema targetNamespace="custOrdersItems" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="CUST_ORDERS_ITEMS">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="CUSTOMER_ID" type="xs:string"/>
        <xs:element name="FIRST_NAME" type="xs:string"/>
        <xs:element name="LAST_NAME" type="xs:string"/>
        <xs:element name="CUSTOMER_SINCE" type="xs:date"/>
        <xs:element name="EMAIL_ADDRESS" type="xs:string"/>
       <xs:element name="TELEPHONE_NUMBER" type="xs:string"/>
        <xs:element name="SSN" maxOccurs="1" minOccurs="0" type="xs:string"/>
        <xs:element name="BIRTH_DAY" maxOccurs="1" minOccurs="0" type="xs:date"/>
        <xs:element name="DEFAULT_SHIP_METHOD" maxOccurs="1" minOccurs="0" type="xs:string"/>
        <xs:element name="EMAIL_NOTIFICATION" maxOccurs="1" minOccurs="0" type="xs:short"/>
        <xs:element name="NEWS_LETTTER" maxOccurs="1" minOccurs="0" type="xs:short"/>
        <xs:element name="ONLINE_STATEMENT" maxOccurs="1" minOccurs="0" type="xs:short"/>
        <xs:element name="LOGIN_ID" maxOccurs="1" minOccurs="0" type="xs:string"/>
        <xs:element form="qualified" name="CUSTOMER_ORDER" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
             <xs:element name="ORDER_ID" type="xs:string"/>
              <xs:element name="C_ID" type="xs:string"/>
              <xs:element name="ORDER_DT" type="xs:date"/>
              <xs:element name="SHIP_METHOD_DSC" type="xs:string"/>
              <xs:element name="HANDLING_CHRG_AMT" type="xs:decimal"/>
              <xs:element name="SUBTOTAL_AMT" type="xs:decimal"/>
              <xs:element name="TOTAL_ORDER_AMT" type="xs:decimal"/>
              <xs:element name="SALE_TAX_AMT" type="xs:decimal"/>
              <xs:element name="SHIP_TO_ID" type="xs:string"/>
              <xs:element name="SHIP_TO_NM" type="xs:string"/>
              <xs:element name="BILL_TO_ID" type="xs:string"/>
              <xs:element name="ESTIMATED_SHIP_DT" type="xs:date"/>
              <xs:element name="STATUS" type="xs:string"/>
              <xs:element name="TRACKING_NO" maxOccurs="1" minOccurs="0" type="xs:string"/>
              <xs:element name="DATE_INT" maxOccurs="1" minOccurs="0" type="xs:long"/>
              <xs:element form="qualified" name="CUSTOMER_ORDER_LINE_ITEM" maxOccurs="unbounded">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="LINE_ID" type="xs:string"/>
                    <xs:element name="ORDER_ID" type="xs:string"/>
                    <xs:element name="PROD_ID" type="xs:string"/>
                    <xs:element name="PROD_DSC" type="xs:string"/>
                    <xs:element name="QUANTITY" type="xs:integer"/>
                    <xs:element name="PRICE" type="xs:decimal"/>
                    <xs:element name="STATUS" type="xs:string"/>
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

1.3.6.1 View Test Run Results

Test results from this function can be viewed in two ways:

• Tree

• Text

  Note:

  The Tabular option is only available for flat (non-nested) results.

Figure 1-36 Test Run Results in Tree Style Format

Description of "Figure 1-36 Test Run Results in Tree Style Format"

1.3.6.1.1 Query Statistics in the Console Window

The Console window will always contain information on a successfully executed query. Access the Console with:

Window > Show View > Other... > General > Console

Click OK.

Sample console output is shown below.

Figure 1-37 Query Details in the Console Window

Description of "Figure 1-37 Query Details in the Console Window"

1.3.11.1 Saving Project to a ZIP File

1. In the Archive file dialog the myDataspace project is pre-selected. Browse to the location where you want to put your archive file.

2. Name your file:

   myDataspace

   Leave all other options unchanged.

3. Click Save.

   Figure 1-43 Creating the Archive File

   
   Description of "Figure 1-43 Creating the Archive File"
   
   

4. Click Finish.

A file myDataspace.zip will be created in the directory you specified.

1.4.1.1 Oracle Data Service Integrator Start Menu

The following table describes the menu options available from the main Oracle WebLogic menu.

1.5.1.1 Data Services Eclipse for WebLogic

Eclipse for WebLogic run inside the Eclipse framework.

The Windows > Show View menu option can be used to add additional windows to the perspective.

In addition, several Oracle Data Service Integrator Perspective menu options are provided under:

File > New

These allow you to, first, create an Oracle Data Service Integrator dataspace project and then to add various types of data services, models and web service mapper.

1.5.2.1 Overview

A return type can be non-nested, or flat, even if it joins two relational tables, where one table has a one-to-many relationship with the other table. An example is one customer in a CUSTOMER table with many Orders in an ORDERS table. One approach to the return type is to nest an Orders element of multiple cardinality beneath the Customer element.

Figure 1-45 A Nested Customer-and-Orders Schema

Description of "Figure 1-45 A Nested Customer-and-Orders Schema"

Because you can design a logical data service with any structure, regardless of the underlying data sources, it is just as valid to define a flat return type to model the relationship between Customers and Orders.

Figure 1-46 A Flat Customer-and-Orders Schema

Description of "Figure 1-46 A Flat Customer-and-Orders Schema"

1.5.2.2 Create a Dataspace Project

First, create a new dataspace project to contain your physical and logical data services:

1. In Eclipse for WebLogic, choose File > New > Dataspace Project.

2. Enter a project name such as FlatReturnType, then click Finish.

3. Right-click the new dataspace project name, and choose New > Folder.

4. Create folders named physical and logical. Within logical, create a folder named schemas.

   Using separate folders for physical and logical services helps separate the physical and logical integration layers.

Figure 1-47 Adding a New Dataspace Project

Description of "Figure 1-47 Adding a New Dataspace Project"

1.5.2.3 Create the Return Type

The return type the logical data service uses combines data from the CUSTOMER table and the ORDERS table. It has a non-nested XML structure, even though the data shows that customers and orders have a one-to-many relationship.

You can define the return type by creating an XML schema (XSD) file. In an XML editor, create a schema file like this one:

<?xml version="1.0" encoding="UTF-8" ?>
<xs:schema targetNamespace="ld:logical/FlatReturnType" xmlns:xs="http://www.w3.org/2001/XMLSchema">
   <xs:element name="CUSTOMERS_AND_ORDERS">
      <xs:complexType>
         <xs:sequence>
            <xs:element name="CUSTOMER_ID" type="xs:string"/>
            <xs:element name="FIRST_NAME" type="xs:string"/>
            <xs:element name="LAST_NAME" type="xs:string"/>
            <xs:element name="EMAIL_ADDRESS" type="xs:string"/>
            <xs:element name="ORDER_ID" type="xs:string"/>
            <xs:element name="ORDER_DT" type="xs:date"/>
            <xs:element name="TOTAL_ORDER_AMT" type="xs:decimal"/>
         </xs:sequence>
      </xs:complexType>
   </xs:element>
</xs:schema>

Be sure to:

• Define targetNamespace to make sense for your dataspace project.

  Make sure you have only one top-level element of the name you choose (here, CUSTOMERORDER) in your target namespace. You can give the targetNamespace the same name as the dataspace project, but you are not required to.

• Save the schema file in the logical/schemas folder within your dataspace project.

Note that the cardinality of all elements uses the default values, minOccurs="1" and maxOccurs="1". Each customer has many orders, but there is only one combination of customer and order, so the cardinality of the order elements (ORDER_ID, ORDER_DT, and TOTAL_ORDER_AMT) is still 1.

1.5.2.4 Create Physical Data Services

Now, create physical data services based on the sample database or your own physical data sources.

1. In Project Explorer, right-click the physical folder in your dataspace project.

2. Choose New > Physical Data Service.

3. Choose Relational for Data source type and dspSamplesDataSource for Data source, then click Next.

4. Expand RTLCUSTOMER and select CUSTOMER.

5. Select Public for both CUSTOMER and CUSTOMER_ORDER, then click Next.

6. Click Finish.

7. When asked if you want to open the new data services, click No.

Figure 1-48 Adding Physical Data Services

Description of "Figure 1-48 Adding Physical Data Services"

1.5.2.5 Create a Logical Data Service

Now that you have physical data services and a schema for the return type, you can create the logical data service.

1. Right-click the logical folder, then choose New > Logical Data Service.

2. Enter a name for the service, such as FlatCustomersAndOrders.

3. Make sure Entity Data Service is selected, then click Finish.

Now associate a return type with the service:

1. Right-click in the Overview tab and choose Associate XML Type.

2. Select the schema and click OK.

Figure 1-49 A New Logical Data Service with a Return Type

Description of "Figure 1-49 A New Logical Data Service with a Return Type"

You also need to define a primary Read function, in order to create both the query map and update map.

1. Right-click in the service name bar at the top, and choose Add Operation.

2. Make sure Kind is set to read, then enter a function name, such as read.

3. Make sure Primary is selected, then click OK.

Figure 1-50 Creating a Primary Read Function

Description of "Figure 1-50 Creating a Primary Read Function"

1.5.2.6 Create the Query Map

Now you need to create the query map visually in Eclipse for WebLogic, which in turn generates an update map.

1. Click the Query Map tab.

2. In Project Explorer, expand the physical data services CUSTOMER.ds and CUSTOMER_ORDER.ds.

3. Drag the Read function from each physical service to the mapping area.

   Notice that you cannot scope the CUSTOMER_ORDER block to a subtype in the return type, because the return type has no subtypes.

4. Drag mappings from the CUSTOMER block on the left to the return type for CUSTOMER_ID, FIRST_NAME, LAST_NAME, and EMAIL_ADDRESS.

5. Drag mappings from the CUSTOMER_ORDER block on the left to the return type for ORDER_ID, ORDER_DT, and TOTAL_ORDER_AMT.

6. In the For blocks, drag from CUSTOMER/CUSTOMER_ID to CUSTOMER_ORDER/CUSTOMER_ID.

   This creates a join between the two data sources.

At this point, the query map looks like this.

Figure 1-51 A Query Map with Mappings and a Join

Description of "Figure 1-51 A Query Map with Mappings and a Join"

If you click the Source tab and expand the Read function, you see XQuery code like this:

declare function tns:read() as element(fla:CUSTOMERS_AND_ORDERS)*{
for $CUSTOMER_ORDER in cus1:CUSTOMER_ORDER()
for $CUSTOMER in cus:CUSTOMER()
where $CUSTOMER/CUSTOMER_ID eq $CUSTOMER_ORDER/C_ID
return
        <fla:CUSTOMERS_AND_ORDERS>
            <CUSTOMER_ID>{fn:data($CUSTOMER/CUSTOMER_ID)}</CUSTOMER_ID>
            <FIRST_NAME>{fn:data($CUSTOMER/FIRST_NAME)}</FIRST_NAME>
            <LAST_NAME>{fn:data($CUSTOMER/LAST_NAME)}</LAST_NAME>
            <EMAIL_ADDRESS>{fn:data($CUSTOMER/EMAIL_ADDRESS)}</EMAIL_ADDRESS>
            <ORDER_ID>{fn:data($CUSTOMER_ORDER/ORDER_ID)}</ORDER_ID>
            <ORDER_DT>{fn:data($CUSTOMER_ORDER/ORDER_DT)}</ORDER_DT>
            <TOTAL_ORDER_AMT>{fn:data($CUSTOMER_ORDER/TOTAL_ORDER_AMT)}</TOTAL_ORDER_AMT>
        </fla:CUSTOMERS_AND_ORDERS>
};

Notice that the XQuery code has a for statement nested directly within another for statement. This creates an inner join between the two tables in SQL. To confirm the SQL that is created:

1. Click the Test tab.

2. At Select operation, make sure the primary Read function is selected.

3. Click Run (saving your data service as necessary).

You should see an XQuery FLWOR statement node. If you expand it, you should see a SQL query like this, showing an inner join:

SELECT t1."ORDER_DT" AS c1, t1."ORDER_ID" AS c2, t1."TOTAL_ORDER_AMT" AS c3,
  t2."CUSTOMER_ID" AS c4, t2."EMAIL_ADDRESS" AS c5, t2."FIRST_NAME" AS c6, t2."LAST_NAME" AS c7
FROM "RTLAPPLOMS"."CUSTOMER_ORDER" t1
JOIN "RTLCUSTOMER"."CUSTOMER" t2
ON (t2."CUSTOMER_ID" = t1."C_ID"

The inner join is created because the logical data service has a flat return type. When you mouse over the SQL query, you see this message:

Generated SQL query does not have a WHERE clause. This may cause the query to take longer to finish 
and use excessive memory resources.

1.5.2.7 See Also

Example: How to Create Your First Data Services