Creating a RESTful Web Service

Overview

    Purpose

    This tutorial covers how to use RESTful Web Services in your Oracle Database Cloud Service.

    Time to Complete

    Approximately 30 minutes

    Introduction

    A RESTful Web Service is a service that calls a specific SQL statement or PL/SQL block in your Oracle Database. The RESTful Web Service Wizard is a set of pages in the SQL Workshop area of Oracle Application Express that help you to create a RESTful Web Service declaratively.

    Once you have defined a RESTful Web Service, you can call it with a unique Uniform Resource Identifier (URI).

    RESTful Web Services are organized within Oracle APEX through a hierarchy of a module, a resource template and handlers within a template. The resource template includes a prefix for the URI, which is completed with a final portion of the URI defined by the handler. The first part of the URI is the address of your specific Database Cloud Service.

    Note:  This tutorial provides examples of querying data in the Oracle Database Cloud Service using a RESTful Web Service. To learn about inserting and updating data via the POST and PUT methods, refer to the Oracle white paper, RESTful Web Services for the Oracle Database Cloud.

    Prerequisites

    Before starting this tutorial, you should:

    • Perform the Signing Up for a Database Cloud Service tutorial.
    • Install the JSON plugin for your browser. To get the appropriate link you may need to search the internet, however, the link for firefox is located here.

Accessing RESTful Web Services

    A RESTful Service Module is a grouping of templates under a common URI prefix. To access RESTful Web Services in your Database Cloud Service, perform the following steps:

    Open a browser and enter the following URL:

    http://cloud.oracle.com

    Click Sign In.

    myhomepage01

    Make sure the correct Data Center for your service is selected and click Sign In to My Services.

    login02

    Enter your username, password, and the identity domain you have defined and click Sign In.

    access03

    Your Database Cloud Service is displayed. Click the database service.

    access04

    A page opens with overview information about the database service. Click Open Service Console to open the database service.

    access05

    The Oracle Application Express Home page is displayed. To access the RESTful Web Services page, select the SQL Workshop tab and select RESTful Services.

    The RESTful Services page is displayed. Select the default oracle.example.hr RESTful Service. In the next topic, you begin to examine the RESTful Services supplied by default.

    access07

Retrieving JSON Result Set Based on Query One Row With a Bind Variable

    In this topic, you review and test the employees/{id} RESTful Service which selects all employee information for a specified id. The curly brackets {id} is used to identify the bind variable that will be sent as part of the URI. Perform the following steps:

    Select the GET handler for the employees/{id} service.

    Review the query in the source field. Because the source type is set to Query One Row, only one row will be displayed when the handler is executed. You want to specify the empno for which you want to test this RESTful Service. Click Set Bind Variables.

    Enter 7876 for the Value of the :ID Bind Variable and click Test.

    All the information for empno 7876 is displayed which is the the variable passed as the final portion of the URI. Close this window.

Retrieving JSON Result Set Based on a Feed

    In this topic, you review and test the employeesfeed/ RESTful Service which selects the empno and ename values in the employees table and displays them as a feed. Perform the following steps:

    Click the RESTful Service Module breadcrumb at the top of the screen to navigate back to the default oracle.example.hr RESTful Service.


    Select the GET handler for the employeesfeed/{id} service.

    employeesfeed_id_handler

    To retrieve JSON-formatted results instead of CSV, in the Format field, select JSON. Click Apply Changes.

    select JSON format

    Click the RESTful Service Module breadcrumb at the top of the screen to navigate back to the default oracle.example.hr RESTful Service.

    Select the GET handler for the employeesfeed/ service.

    Review the query in the source field. Because the source type is set to feed, the JSON returned includes a link to a RESTful Web Service call with the same URI and the first column included as a parameter.  Click Test.

    The data returned is displayed for all employees in the EMP table. The URI contains the link to the individual record.  This link is actually using the employeesfeed/{id} Resource Template: note the employeesfeed/7782 in the URL.  So when you click on the URL, it will take you to the individual employee record.

    t1_03_b

    When you click on the URI link, the employeesfeed/{id} RESTful web service is executed for the URI you clicked and the individual employee record is displayed.  Click the Back button in your Browser 2 times to return to Application Express. 

    t1_03

Retrieving Data in CSV Format

    In this topic, you review and test the empinfo/ RESTful Service which selects all employee information and returns it in CSV format. You are prompted to save the file which you can then view using an CSV editor such as Microsoft Excel or Wordpad. Perform the following steps:

    Select the GET handler for the empinfo/ service.

    Notice that the Source Type is set to Query with a Format of CSV. The query selects all records from the EMP table. Click Test.

    A dialog appears to open or save a file. Create a .csv file to save to your computer, by entering a file name, giving it an extension of .csv, and clicking Save.

    Note:  In Firefox, you get an Opening dialog to save the file.

    Open the file in Microsoft Excel or another editor to view the contents of the CSV file. Notice that all the data from the emp table is displayed.

Retrieving JSON Result Sets Differentiated By Bind Variables

    In this topic, you retrieve different JSON data sets based on the bind variable passed. Perform the following steps:

    Select the GET handler for the empsec/{empname} service.

    Notice the Query contains a compound WHERE clause that checks if the job for the empname passed through the URL is PRESIDENT or MANAGER, a list of employees from all departments is displayed. If the empname is not a PRESIDENT or MANAGER, then only show a list of employees in the department that that employee is in. You need to set the bind variable, click Set Bind Variables.

    Enter ADAMS for EMPNAME Value and click Test.

    Notice that because ADAMS is not a manager, only the employees in Department 20 are in the list. Close the window.

    This time enter JONES for the EMPNAME Value and click Test.

    Because JONES is a manager, you see all employees from all departments in the list. Close this window.

    This time enter KING for the EMPNAME Value and click Test.

    Notice that because KING is the president, you see all employees from all departments. Close this window.

    This time enter MILLER for the EMPNAME Value and click Test.

    Notice this time, only the employees in department 10 are displayed because Miller is not a manager. Close this window.

Formatting the Result Set Based on a PL/SQL Block

    In this topic, you format a result set, based on the execution of a PL/SQL block. Perform the following steps:

    Select the RESTful Service Module breadcrumb.

    Select the GET handler for the empsecformat/{empname} service.

    Instead of just executing a SQL Query, you execute a PL/SQL block that formats the results of a SQL Query. You want to pass a bind variable, click Set Bind Variables.

    DECLARE
  prevdeptno   number;
  deptloc      varchar2(30);
  deptname     varchar2(30);
  CURSOR getemps IS select * from emp 
                     where ((select job from emp where ename = :empname)  IN ('PRESIDENT', 'MANAGER')) 
                        or deptno = (select deptno from emp where ename = :empname) 
                     order by deptno, ename;
BEGIN
  sys.htp.htmlopen;
  sys.htp.headopen;
  sys.htp.title('Departments');
  sys.htp.headclose;
  sys.htp.bodyopen; 
  for emprecs in getemps
  loop
      if emprecs.deptno != prevdeptno or prevdeptno is null then
          select dname, loc into deptname, deptloc 
            from dept where deptno = (select deptno from emp where ename = emprecs.ename);
          if prevdeptno is not null then
              sys.htp.print('
         ');
          end if;
          sys.htp.print('Department ' || deptname || ' located in ' || deptloc || '');
          sys.htp.print('
      ');
      end if;
      sys.htp.print('
    • ' || emprecs.ename || ', ' || emprecs.job || ', ' || emprecs.sal || '
      • ');
      prevdeptno := emprecs.deptno;
  end loop;
  sys.htp.print('
    ');
  sys.htp.bodyclose;
  sys.htp.htmlclose;
END;

    This time enter ADAMS for the EMPNAME Value and click Test.

    Notice the same results are displayed as the previous topic but this time the results are formatted. Close the window.

    This time enter JONES for the EMPNAME Value and click Test.

    Because JONES is a manager, you see all employees from all departments in the list. Close this window.

Setting Pagination on JSON Result Set

    In this topic, you review and test the employees/ RESTful Service that contains a ‘Pagination Size’ of 7 set on the Resource Handler page which controls the number of rows of data displayed in the JSON result set. The SQL Query contains the necessary SQL to create the next and previous URI links to show additional pages of results. In addition, the URI contains the URL to invoke the employees/{id} RESTful Web Service that you examined earlier to show an individual employee record. Perform the following steps:

    Select the RESTful Service Module breadcrumb.

    Select the GET handler for the employees/ service.

    Notice the Source Type is set to Query with a Format of JSON and the Pagination Size is set to 7. The SQL specified will result in providing a link to ’first’, ‘previous’ and ‘next’ when paginating through the results. Click Test.

    The JSON results are displayed. Notice that the uri:{$ref} for each item invokes the employees/{id} RESTful web service for the id specified in the link. Click the first uri:{$ref} link in the list, in this case, for empno 7369.

    Notice that the individual employee record for empno 7369 is displayed. Click the Back button in your Browser to return to the previous page.

    Seven rows are displayed. Select the next:{$ref} link to navigate to the next page of data.

    The next 7 rows are displayed with links to the first, previous (which in this case is also the first page) and the next page.

Summary

    In this tutorial, you have learned how to:

    • Review and test the default RESTful web service module
    • Add resource templates and handlers to the web service module

    Resources

To help navigate this Oracle by Example, note the following:

Hiding Header Buttons:
Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
Topic List Button:
A list of all the topics. Click one of the topics to navigate to that section.
Expand/Collapse All Topics:
To show/hide all the detail for all the sections. By default, all topics are collapsed
Show/Hide All Images:
To show/hide all the screenshots. By default, all images are displayed.
Print:
To print the content. The content currently displayed or hidden will be printed.

To navigate to a particular section in this tutorial, select the topic from the list.