Creating and Using RESTful Web Service in Application Express 5.0

Last updated: 04/17/15 01:30 pm EDT

 

Overview

Purpose

This tutorial covers creating a RESTful Web Service and accessing the Web Service through an application in Application Express 5.0. This tutorial also covers consuming the Web Service using a Java client.

Time to Complete

Approximately 1 hour

Introduction

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 by using the protocol over HTTP. The service receives the request, processes it, and returns a response. Web Services are typically based on Simple Object Access Protocol (SOAP) or Representational State Transfer (REST) architectures. RESTful Web Services are result oriented. The scope of the Web Service is found in the URI and the method of the service is described by the HTTP method that is used such as GET, POST, PUT, and DELETE.

This tutorial covers creating a RESTful Web Service declaratively using Oracle Application Express's SQL Workshop tool, and then consuming the same service by creating an application and adding a Web Service Reference to the RESTful Web Service. The RESTful Web Service is also consumed using a Java client.

Prerequisites

Before starting this tutorial, you should have:

  • Access to an Oracle Database 11g database or above that has the sample schema installed.
  • Installed Application Express Release 5.0 into your Oracle Database.
  • Access to the OEHR tables. If necessary, download and install OEHR Sample Objects from here
  • Installed Java Development Kit 6 or later to consume the RESTful Web Service using a Java client.
  • Downloaded and unzipped the files.zip into your working directory.
 

Creating a RESTful Web Service

In this topic, you create a RESTful Web Service using RESTful Services tool in SQL Workshop. The RESTful Web Service Wizard is a set of pages in SQL Workshop that help you to create a RESTful Web Service declaratively. The RESTful Web Service calls a specific SQL statement in your database.

  1. Log into the Application Express Login page using your login credentials. Enter the your credentials and click Sign In.

    alt description here

  2. Click SQL Workshop.

    alt description here

  3. Select RESTful Services.

    alt description here

  4. Click Create to create a RESTful Service.

    alt description here

  5. Enter Employees as the Name for the RESTful Service module. Enter the URI Template as employees/ to identify your Uniform Resource Identifier (URI). Select GET as the method. Select CSV for Format. This identifies the HTTP method to be used for the Resource Handler. For Source, enter the following SQL query. This is responsible for handling the selected HTTP method. Click Create Module. 

    select * from oehr_employees
    alt description here

  6. Click the GET method Resource Handler.

    alt description here

  7. Change the Require Secure Access option from Yes to No. Click Apply Changes.

    alt description here

  8. Click Test to test the behavior of the RESTful Service Handler.

    alt description here

  9. You are prompted to save the file which you can then view using a CSV editor. The CSV format resultset is displayed. Make a note of the URI which is used later while creating a client service.

    alt description here
    alt description here

  10. Click Create Handler to create a Resource Handler for the POST method.

    alt description here

  11. Select POST for Method, Source Type as PL/SQL, Mime Types Allowed as application/json. Select No for Requires Secure Access. In the Source section enter the following PL/SQL code to create a row by inserting values into the employees table. Click Create.


                                    declare id
                                    oehr_employees.employee_id%TYPE; begin id :=
                                    oehr_employees_seq.nextval; insert into
                                    oehr_employees (employee_id,first_name,
                                    last_name, email, hire_date, job_id) values (id,
                                    :first_name, :last_name, :email,
                                    to_date(:hire_date, 'DD-MM-YYYY'), :job_id);
                                    :employee_id := id; end;
    alt description here

  12. Scroll down the page and click Create Parameter to add an OUT parameter to the handler that will return the newly created employee’s ID.

    alt description here

  13. Enter employee_id for Name and employee_id for Bind Variable Name. Select OUT for Access Method, HTTP Header for Source Type, and String for Parameter Type. Click Create.

    alt description here

  14. You will next create a new template to retrieve JSON result set based on Query One Row with a Bind Variable. Create a new template by clicking Create Template.

    alt description here

  15. Enter employees/{id} for URI template. Click Create.

    alt description here

  16. Click Create Handler.

    alt description here

  17. Select Query One Row for Source Type and select No for Requires Secure Access. Enter the following SQL statement for Source and click Create.

    select * from oehr_employees where employee_id = :id
    alt description here

  18. Click Create Parameter.

    alt description here

  19. Enter id for Name and Bind Variable Name. Select Source Type as HTTP Header. Click Create.

    alt description here

  20. The Source Type URI is not being displayed and hence you want to edit the Resource Handler Parameter that you just created. Select id from the Parameter list and select URI for Source Type. Click Apply Changes.

    alt description here
    alt description here

  21. Click Set Bind Variables.

    alt description here

  22. Enter 103 or any Employee ID for the Value of the :ID Bind Variable and click Test.

    alt description here

  23. Open the file. All the information for employee_id is displayed, which is the variable passed as the final portion of the URI. Close this window.

    alt description here

  24. You create a new template for Retrieving JSON Result Set Based on a Feed. Here, you create the employeesfeed/ RESTful service, which selects the employee_id and the first_name values in the employees table and displays them as a feed. Create a new template by clicking Create Template.

    alt description here

  25. Enter employeesfeed/ for URI template. Click Create.

    alt description here

  26. Click Create Handler.

    alt description here

  27. Select Feed for Source Type and select No for Requires Secure Access. Enter the following SQL statement for Source and click Create.

    select employee_id, first_name from oehr_employees order by employee_id, first_name
    alt description here

  28. Click Create Template.

    alt description here

  29. Enter employeesfeed/{id} for URI template. Click Create.

    alt description here

  30. Click Create Handler.

    alt description here

  31. Select Feed for Source Type and select No for Requires Secure Access. Enter the following SQL statement for Source and click Create.

    select employee_id, first_name from oehr_employees where employee_id = :id
    alt description here

  32. Click GET method of the employeesfeed/ URI template type.

    alt description here

  33. Click Test .

    alt description here

  34. The data returned is displayed for all employees in the OEHR_EMPLOYEES table. The URI contains the link to the individual record. This link is actually using the employeesfeed/{id} Resource Template.

    alt description here

    Copy the link and execute it in the browser. This will display the details of the individual employee's record.

    alt description here

 

Creating a RESTful Web Service Reference in Application Express

In this topic, you consume the RESTful Web Service in Apex by creating a database application and by creating a Web Service Reference in the application. You create a form and report page that uses the web service.

Note: Make sure you have granted the connect privileges by executing the APEX_ACL.sql script from the files.zip that you have unzipped in the Prerequisites section of this tutorial.

  1. Navigate to your workspace home page and create a new application by selecting Application Builder (tab) menu > Click Create.

    alt description here
    alt description here

  2. Select Desktop.

    alt description here

  3. Enter the Name of the application as RESTful Web Services App and select Productivity Applications theme. Click Create Application.

    alt description here

  4. Click Create Application.

    alt description here

  5. On the application home page, click Shared Components.

    alt description here

  6. Under Data References, click Web Service References.

    alt description here

  7. Click Create >.

    alt description here

  8. Select REST for Web Reference and click Next.

    alt description here

  9. Enter Employees for Name, enter the URL for URL. In this case enter http://apexcentral.us.oracle.com:7778/apex/ou/employees . Since the REST Web Service does not require an HTTP Header parameter, click the Delete Header icon. Click Next >.

    alt description here

  10. Delete the input parameter by clicking the Delete Parameter icon. Click Next >.

    alt description here

  11. Select Text for Output Format. Enter the names of the parameters like Employee ID, Name, Hire Date, and Job ID by mapping them to the response. Click Create.

    alt description here

  12. Select View Report icon.

    alt description here

  13. Click the Test icon.

    alt description here

  14. Click Test.

    alt description here

  15. Scroll down to view the response. Click Cancel.

    alt description here
    alt description here

  16. Click the Application <n> breadcrumb.

    alt description here

  17. Click Create Page >.

    alt description here

  18. Click Form Page Type.

    alt description here

  19. Click Form & Report on Web Service.

    alt description here

  20. Select Web Service Reference as Employees. Click Next >.

    alt description here

  21. Accept the default values and click Next >.

    alt description here

  22. Click Next >.

    alt description here

  23. Select the check box next to Name to select all the parameters and click Next >.

    alt description here

  24. Click Next >.

    alt description here

  25. Click Create.

    alt description here

  26. Click Save and Run Page.

    alt description here

  27. Enter your login credentials and click Log In.

    alt description here

  28. Click Submit.

    alt description here

  29. The result set is displayed.

    alt description here
 

Consuming the RESTful Web Service Created in Application Express Using a Java Client

In this topic, you consume the Web Service that you have created in Application Express using a Java Client.

  1. Open Command Prompt.

    alt description here

  2. Execute the following command to navigate to the directory where you extracted the files available in the Prerequisites of this tutorial. Navigate to where the RESTemp java code resides. If you have extracted it in the Downloads directory then, you can find the RESTemp file in the C:\Downloads\files\RESTemp directory.

    cd Downloads
    cd files\RESTemp
    alt description here

  3. To fetch the details of an employee, enter the following command. Pass a value for employee_id. Note: Here, the command line parameter 'S' indicates that you want to do a select. Also, you have to edit the run.cmd file that you have downloaded to replace the URL for your RESTful Web Service.

    run.cmd S [employee_id]
    alt description here

  4. The values for the employee are displayed.

    alt description here

  5. To insert new values into the employees table, enter the following command to invoke the POST Method which will insert a new row into the table. Note: Here, the command line parameter 'I' indicates that you want to insert a new row.

    run.cmd I
    alt description here

    Enter the first name, last name, email address, hire date, and job ID. You will notice that a new row is created with a new employee ID.

    alt description here

    You can verify if this new row entry was created in the table by navigating to SQL Workshop to check for the new row values.

 

Summary

In this tutorial, you have learned how to:

  • Create a RESTful Web Service in Application Express
  • Create a RESTful Web Service Reference in Application Express
  • Consume the Web Service created in Application Express using a Java Client

Resources

 

Credits

  • Lead Curriculum Developer: Dimpi Sarmah