6.5 How to Create the RESTful Service Module Example

Every workspace includes the example RESTful Service Module, oracle.example.hr.

This module provides example resource templates and resource handlers that implement several different methods to retrieve and display employee information from the emp table. To access the oracle.example.hr module, see “ Accessing RESTful Services”.

Description of the illustration GUID-4E0A9D3D-BB12-4347-A6B0-CD4AC88377CD-default.png

The oracle.example.hr module consists of several resource templates. Each resource template demonstrates a different way of retrieving information and formatting the returned results.

Description of the illustration GUID-CC0A5BEB-C69E-46E1-91DD-7FEDAAA28C1E-default.png

The following results are expected for each oracle.example.hr resource template when the respective GET resource handler is invoked:

• empinfo/ - Employee information from the employees emp table is returned in CSV format.

• employees/ - Employee information from the employees emp table is displayed in JSON format with Pagination Size set to 7. A first, previous and next link is provided to page through the results.

• employees/{id} - Employee information from the employees emp table for a specified id is returned. The curly bracket identifies the parameter sent as part of the URI.

• employeesfeed/ - Employee information from the employees emp table is returned as a JSON Feed.

• employeesfeed/{id} - Employee information from the employees emp table for a specified id is displayed in JSON format. The curly bracket identifies the parameter sent as part of the URI.

• empsec/{empname} - Employee information for a specified employee name, empname, is returned and displayed in JSON. The curly bracket identifies the parameter sent as part of the URI.

• empsecformat/{empname} - Employee information for a specified employee name, empname, is returned and formatted based on the execution of a PL/SQL block. The curly bracket identifies the parameter sent as part of the URI.

The following result is expected for the employees/[id] resource template when the PUT resource handler is invoked:

• employees/{id} - Employee information from the employees emp table for a specified id is updated. The curly bracket identifies the parameter sent as part of the URI. A status code is returned, which can be use to verify the success of the request.

You can build and test your own example RESTful Service Module that retrieves data in CSV format.

1. On the Workspace home page, click SQL Workshop and then RESTful Services.

   The RESTful Services home page appears.

2. Click Create.
3. For Name, enter my.example.demo.
4. For URI Prefix, enter demo/. This is the base of the Uniform Resource Identifier (URI) that is used to access this RESTful Service.
5. Under Add a Resource Template, for URI Template enter empinfo/.
6. Under Add a Resource Handler, for Method select GET.

   The Resource Handler settings appear.

7. Make the following selections and entries:

   • Source Type - Select Query. Executes a SQL Query and transforms the result set into either a JavaScript Object Notation (JSON) or CSV representation, depending on the format selected. This option is only available when the HTTP method GET has been selected

   • Format - Select CSV.

   • Source - Enter the following:

     select * from emp
     

8. Click Create Module.

   The RESTful Service Module page displays.

9. Under empinfo/, click the GET resource handler.
10. Click Test.

    An Opening dialog appears, allowing you to open or save the CSV file containing the results of executing the resource handler source SQL query.

11. Select Save File and click OK.
12. Open the saved file in Microsoft Excel or another editor to view the contents of the CSV file.

    All data from the emp table is included in the CSV file.

    Note:

    If your results are not correct, compare your empinfo GET resource handler with the oracle.example.hr empinfo GET resource handler.

13. Click Apply Changes.

You can add and test your own resource template that retrieves employee data based on a parameter.

1. On the Workspace home page, click SQL Workshop and then RESTful Services.

   The RESTful Services home page appears.

2. Click the RESTful Service Module you want to add the resource template to.

   The RESTful Service Module page appears.

3. At the bottom of the left panel, click Create Template.

   The Resource Template options appear on the right.

4. For URI Template, enter employees/{id}.
5. Click Create.

   The employees/{id} resource template displays on the left panel.

6. Under the employees/{id} resource template, click Create Handler.

   The Resource Handler options appear.

7. Make the following selections and entries:

   • Method - select GET.

   • Source Type - select Query One Row. Executes a SQL Query returning one row of data into a JSON representation. This option is only available when the HTTP method GET has been selected

   • Requires Secure Access - select No.

   • Pagination Size - Leave blank. In this case, there's no need to set pagination since only one record is retrieved.

   • Source - enter the following:

     select * from emp  where empno = :id
     

8. Click Create.

   The GET Handler appears under the employees/{id} resource template on the left panel.

9. Click Set Bind Variables.
10. For Bind Variable Value, enter 7876.
11. Click Test.

    A dialog appears showing the results for the employee record with an employee id of 7876 displays.

You can add and test your own resource template that retrieves data in JSON format and demonstrates pagination.

1. On the Workspace home page, click SQL Workshop and then RESTful Services.

   The RESTful Services home page appears.

2. Click the RESTful Service Module you want to add the resource template to.

   The RESTful Service Module page appears.

3. At the bottom of the left panel, click Create Template.

   The Resource Template options appear on the right.

4. For URI Template, enter employees/.
5. Click Create.

   The employees/ resource template displays on the left panel.

6. Under the employees/ resource template, click Create Handler.

   The Resource Handler options appear.

7. Make the following selections and entries:

   • Method - Select GET.

   • Source Type - Select Query.

   • Format - Select JSON.

   • Requires Secure Access - Select No.

   • Pagination Size - Enter 7.

   • Source - Enter the following:

     select empno "$uri", empno, ename
       from (
            select emp.*
                 , row_number() over (order by empno) rn
              from emp
            ) tmp
      where rn between :row_offset and :row_count 
     

8. Click Create.

   The GET Handler appears under the employees/ resource template on the left panel.

9. Click Test.

   The JSON results display. Notice the uri:{$ref} for each item invokes the employees/{id} RESTful Service for the id specified in the link.

   Note:

   If your results are not correct, compare your employees/ GET resource handler with the oracle.example.hr employees GET resource handler.

10. Click the first uri:{$ref} link in the link for empno 7369.

    The individual employee record for 7369 displays.

11. Click the Back button in your browser.

    The original JSON results appear listing 7 employees.

12. Click the next{$ref} link to navigate to the next page of data.

    The next 7 rows display with links to the first, previous and the next page.

You can add and test your own resource template that displays return data as a feed.

1. On the Workspace home page, click SQL Workshop and then RESTful Services.

   The RESTful Services home page appears.

2. Click the RESTful Service Module you want to add the resource template to.

   The RESTful Service Module page appears.

3. At the bottom of the left panel, click Create Template.

   The Resource Template options appear on the right.

4. For URI Template, enter employeesfeed/.
5. Click Create.

   The employeesfeed/ resource template displays on the left panel.

6. Under the employees resource template, click Create Handler.

   The Resource Handler options appear.

7. Make the following selections and entries:

   • Method - select GET.

   • Source Type - select Feed.

   • Requires Secure Access - select No.

   • Pagination Size - enter 25.

   • Source - enter the following:

     select empno, ename from emp  order by deptno, ename
     

8. Click Create.

   The GET resource handler appears under the employeesfeed/ resource template on the left panel.

9. Click Test.

   The results display a feed for each employee in the emp table.

You can add and test your own resource template that uses a PL/SQL block to format results.

1. On the Workspace home page, click SQL Workshop and then RESTful Services.

   The RESTful Services home page appears.

2. Click the RESTful Service Module you want to add the resource template to.

   The RESTful Service Module page appears.

3. At the bottom of the left panel, click Create Template.

   The Resource Template options appear on the right.

4. For URI Template, enter empsecformat/{empname}.
5. Click Create.

   The empsecformat/{empname} resource template displays on the left panel.

6. Under the empsecformat/{empname} resource template, click Create Handler.

   The Resource Handler options appear.

7. Make the following selections and entries:

   • Method - Select GET.

   • Source Type - Select PL/SQL.

   • Requires Secure Access - Select No.

   • Pagination Size - Leave blank.

   • Source - enter the following:

     DECLARE
       prevdeptno     number;  
       total_rows     number;
       deptloc        varchar2(20);
       deptname       varchar2(20);
       CURSOR         getemps is select * from emp
                                  start with ename = :empname
                                 connect by prior empno = mgr
                                  order siblings by deptno, ename;
     BEGIN
       sys.htp.htmlopen;
       sys.htp.headopen;
       sys.htp.title('Hierarchical Department Report for Employee '||:empname);
       sys.htp.headclose;
       sys.htp.bodyopen;
      
       for emprecs in getemps
       loop
      
           if l_employee.deptno != prevdeptno or prevdeptno is null then
               select dname, loc
                into deptname, deptloc
                from dept
               where deptno = l_employee.deptno;
     
              if prevdeptno is not null then
                  sys.htp.print('</ul>');
              end if; 
             sys.htp.print('Department ' || apex_escape.html(deptname) || ' located in ' || apex_escape.html(deptloc) || '<p/>');
             sys.htp.print('<ul>');
           end if;
      
           sys.htp.print('<li>' || apex_escape.html(l_employee.ename) || ', '  || apex_escape.html(l_employee.empno) || ', ' ||
                             apex_escape.html(l_employee.job) || ', ' || apex_escape.html(l_employee.sal) || '</li>'); 
     
           prevdeptno := l_employee.deptno;
           total_rows := getemps%ROWCOUNT;
     
        end loop; 
        if total_rows > 0 then
            sys.htp.print('</ul>');
        end if; 
        sys.htp.bodyclose;
        sys.htp.htmlclose;
     END;
     

8. Click Create.

   The GET resource handler appears under the empsecformat/{empname} resource template on the left panel.

9. Click Set Bind Variables.
10. For the empname parameter, enter ADAMS.
11. Click Test.

    The results display formatted results for Adams.

You can add and test your own resource template that updates employee data based on a parameter.

1. On the Workspace home page, click SQL Workshop and then RESTful Services.

   The RESTful Services home page appears.

2. Click the RESTful Service Module you want to add the resource template to.

   The RESTful Service Module page appears.

3. At the bottom of the left panel, click Create Template.

   The Resource Template options appear on the right.

4. For URI Template, enter employees/{id}.
5. Click Create.

   The employees/{id} resource template displays on the left panel.

6. Under the employees/{id} resource template, click Create Handler.

   The Resource Handler options appear.

7. Make the following selections and entries:

   • Method - select PUT.

   • Source Type - select PL/SQL. Executes an anonymous PL/SQL block and transforms any OUT or IN/OUT parameters into a JSON representation. This option is only available when the HTTP method selected is one of the following: DELETE, PUT, POST.

   • MIME Types Allowed - Leave blank. In this case, there is no need to set a list of MIME types allowed in the HTTP request.

   • Requires Secure Access - select No.

   • Source - enter the following:

     begin
          update emp set ename = :ename,
                 job = :job,
                 hiredate = :hiredate,
                 mgr = :mgr,
                 sal = :sal,
                 comm = :comm,
                 deptno = :deptno
          where empno = :id;
          :status := 200;
          :location := :id;
      exception
          when others then
              :status := 400;
      end;
     

8. Click Create.

   The PUT Handler appears under the employees/{id} resource template on the left panel.

9. Click Create Parameter.
10. Make the following selections and entries:

    • Name - Enter ID

    • Bind Variable Name - Enter ID

    • Access Method - Select IN

    • Source Type - Select HTTP Header

    • Parameter Type - Select String

11. Click Create.
12. Click Create Parameter.
13. Make the following selections and entries:

    • Name - Enter X-APEX-FORWARD

    • Bind Variable Name - Enter Location

    • Access Method - Select OUT

    • Source Type - Select HTTP Header

    • Parameter Type - Select String

14. Click Create.
15. Click Create Parameter.
16. Make the following selections and entries:

    • Name - Enter X-APEX-STATUS-CODE

    • Bind Variable Name - Enter Status

    • Access Method - Select OUT

    • Source Type - Select HTTP Header

    • Parameter Type - Select Integer

17. Click Create.

    Note:

    The Test and Set Bind Variables buttons are only available for testing GET resource handlers. Install the Sample REST Services application, to test the behaviour of the PUT resource handler.

18. Click Packaged Apps tab.
19. Click Sample REST Services sample application.
20. Click Install Packaged App, and complete the install wizard to install the sample application in your workspace.
21. Run the Sample REST Services application, logging in using your APEX Authentication login credentials.
22. Click Simple Report menu item from the navigation menu to the left.
23. Edit the information for employee BLAKE by clicking on the edit pencil icon for that employee.

    A dialog appears showing the employee record for employee BLAKE. Using the dialog, the data can be changed and saved back to the REST service using a PUT request. The API apex_web_service.make_rest_request is used to call the PUT request.

24. In the dialog box, make the following update:

    • Salary - 3000

25. Click Apply Changes.

    The dialog should close, and the row of data for employee BLAKE should be updated to set salary to 3000 on the Simple Report, with a success message “REST PUT request sent – changes applied.” displayed at the top of the page.