Application Integration Workshop

Consuming Web services with Oracle Application Express

By David Peake

November/December 2007


Just about every leading Web site, from Amazon and eBay to Facebook and Google, has a Web services offering these days. Web services enable applications to interact with one another over the Web in a platform-neutral, language-independent environment. In a typical Web services scenario, a business application sends a request to a service at a given URL, using HyperText Transfer Protocol (HTTP). The service receives the request, processes it, and returns a response.

Oracle Application Express has supported Web services integration for several releases now, but with Oracle Application Express 3.0, that support has been enhanced—for example, document-style Web services are now supported. Oracle Application Express 3.0 also enables manual creation of Web service references. This column shows you how to create Web services references manually, using one of the YouTube Web services APIs.

Note that you must create this example on your own instance of Oracle Application Express—the site does not support external network callouts, so it cannot be used for this project.


Oracle Application Express 3.0 provides two approaches to adding Web services to an application:

  • Generate Web services references by using a wizard to automatically create valid Simple Object Access Protocol (SOAP) request messages with input parameters, output parameters, and operations specified, based on the Web Services Description Language (WSDL).

  • Create Web services references manually. First, examine the WSDL, and then create the SOAP envelope for the request. Typically, you will need a SOAP tool to help you determine the SOAP envelope, but in this sample application, the details for the XML-RPC APIs are provided by the YouTube developer site.

Example YouTube XML-RPC-Style Web Service

YouTube ( offers access to several areas of its video repository via an open API interface that lets you easily integrate YouTube content into an application. The YouTube APIs enable you to obtain information about videos and obtain videos by tag name or username. For this sample application project, you'll obtain just music videos and display them in a report-style format. YouTube's Web services APIs are in both representational state transfer (REST) and XML-RPC format. This example describes how to create an Oracle Application Express application that interacts with the YouTube XML-RPC Web service music video API.

To use the YouTube APIs, you must have a YouTube account (it's free) and you must obtain a developer ID (also free). Your developer ID is embedded in the XML-RPC request document and submitted with every request to the API. To get your account and developer ID, visit

The sample application project consists of the following steps:

Step 1: Create an Oracle Application Express application
Step 2: Create a manual Web reference
Step 3: Test the Web reference
Step 4: Create a process to associate with the Web service
Step 5: Extract the embedded XML document
Step 6: Create a report on the manual Web reference
Step 7: Refine the report

Step 1: Create an application. Prior to creating the manual Web reference to the YouTube music video XML-RPC Web service, you must create an application in Oracle Application Express.

1. Log on to your Oracle Application Express workspace.
2. Select Create Application from the Application Builder menu to launch the Create Application wizard.
3. Enter a name for the application, such as SimpleVideoService.
4. Click Next to continue. The Add Page option appears.
5. Select Blank as the page style, and add one blank page to the application. Leave the defaults, and click Next to step through the wizard and finish creating the application.

If your Oracle Application Express instance requires a proxy server to reach pages on the internet, you must define the proxy server in Application Definition ( Shared Components -> Application -> Definition ).

Step 2: Create a manual Web reference. To create a manual Web reference, enter the URL to the Web service and define the document to send to it. The results will be stored in a collection, which you define in the steps below. To create a manual Web reference,

1. Click Shared Components.
2. Click Web Service References.
3. Click Create. The Create Web Service Reference wizard begins, displaying the message "Do you want to search a UDDI registry to find the WSDL?"
4. Select No , and click Next . Entry fields for WSDL location and authentication credentials appear. Disregard them.
5. In the Tasks region in the right-hand section of the page, click Create Web Service Reference Manually to open a Create/Edit Web Service properties page, with regions for Web service name, service description, SOAP envelope definition, and SOAP response. Enter the details shown in Listing 1.

Code Listing 1: Web services properties

Name:   YouTube Music Videos
SOAP Envelope:
<?xml version='1.0'?>
Store Response in Collection: YOUTUBE_MUSIC_VIDEOS

6. Click Create . The YouTube Music Videos component appears on the Web Services References page.
7. Click the YouTube Music Videos component to select it for testing (in the next step).

Step 3: Test the Web reference. To test the manual Web reference you created for the YouTube music videos list, you must have the component selected on the Web Service References page:

1. Select Details from the View list, and then click Go . The component appears in a table just below the selection row.
2. Click the Test icon next to the YouTube Music Videos reference. A SOAP Envelope and Response page appears.
3. Click Test .
4. View the response in the Result Text area.

The response section displays the XML-RPC response, a string that contains an escaped, standalone XML document. The embedded XML document contains the information about the music videos. (In step 5, you'll see how to unescape the content so that it displays properly in a report.)

Step 4: Create a process to associate with the Web service. Now that you've created the Web service reference, you must invoke it from your application. To create the process that invokes the Web reference,

1. Navigate to the definition of page 1.
2. Click the plus icon in the Processes section under Page Rendering.
3. Select Web Services for the process category, and click Next.
4. Enter Call YouTube Music Video Service in the Name field, and click Next.
5. Select YouTube Music Videos from the Web Service Reference list, and click Next.
6. Leave the message text areas blank, and click Next.
7. Click Create Process.

Step 5: Extract the embedded XML document. The YouTube Music Videos API returns a string (an XML-RPC response) that contains an escaped standalone XML document comprising the result of the API call: the list of music videos. (When the embedded XML is unescaped, the result is the same as the response to a REST call.) It's this result that you want the application to report on, so now create a process that extracts the document and unescapes the XML. The process also updates the collection used to store the response with the value of the extracted document.

To create a process to extract the embedded XML document,

1. Click the plus ( + ) icon in the Processes region under Page Rendering.
2. Select PL/SQL from the Process Category list, and click Next.
3. Enter Extract Embedded Document in the Name field.
4. Enter 20 in the Sequence field, and click Next.
5. Enter the code from Listing 2 in the Enter PL/SQL Page Process text area.

Code Listing 2: PL/SQL code that processes the embedded XML

    l_clob clob;
    l_xml xmltype;
    l_val clob;
    for c1 in (select clob001
                  from apex_collections
                  where collection_name = 'YOUTUBE_MUSIC_VIDEOS'
                 ) loop
        l_clob := c1.clob001;
    end loop;
    l_xml := xmltype.createxml(l_clob);l_val := dbms_xmlgen.convert(l_xml.extract('/methodResponse/params/param/value/
       p_collection_name  => 'YOUTUBE_MUSIC_VIDEOS',
       p_seq                    => '1',
       p_clob_number       => '1',
       p_clob_value          => l_val );

6. Leave the message text areas blank, and click Next.
7. Select PL/SQL from the Condition Type list.
8. Click Create Process.

Step 6: Create a report on the manual Web reference. To display the output of the Web services reference, build a report by using the "Create Report on a Manual Web Service" wizard. For this step, you must provide information about the structure of the XML response document.

To create a report on a manual Web service,

1. Click the plus ( + ) icon in the Regions area under Page Rendering.
2. Select Report from the Region Type list, and click Next.
3. Select Report on collection containing Web service result , and click Next.
4. Enter Music Videos in the Title field, and click Next.
5. Select Manually Created from the Web Reference and click Next. Several entry fields appear for defining the SOAP style and message format. Complete the fields as follows:

  • From the Web Service Reference list, select YouTube Music Videos.

  • For SOAP style , select RPC.

  • For Message Format , select Encoded.

  • Leave Message Namespace blank, and click Next.

  • For Result Node Path , enter /video_list/video.

  • For Parameter Names , enter the following names:

6. Click Create SQL Report. The Success message appears.

The basic application is now complete. Click the Run Page icon to view the application.

Step 7: Refine the report. You can fine-tune the report layout, displaying thumbnails of the video and adding hyperlinks from the thumbnail to the video on YouTube. To make refinements to the report,

1. Click the Report link next to the Music Videos region on the page definition of page 1.
2. Click the Edit icon next to the thumbnail_url column.
3. Enter the following in the HTML Expression text area:

<a href="#url#">
<img src="#thumbnail_url#" />

4. Click Apply Changes.
5. Uncheck the Show check box in the url column row.
6. Click the Move-Up icon in the thumbnail_url column row until it appears directly after the title column.
7. Click Apply Changes.

Run the page to view your refinements. Figure 1 shows a sample result.

figure 1
Figure 1: Sample application result


Oracle Application Express lets you build applications that integrate with other applications on other platforms, by supporting the consumption of Web services. It makes it easy to build applications based on XML-RPC-style Web services such as the YouTube Music Video API.

Next Steps

 READ more Browser-based

 READ more about Oracle Application Express

 DOWNLOAD Oracle Application Express

 VISIT the Oracle Application Express Forum


Photography byScott Webb,Unsplash