About the Example RESTful Service Module (oracle.example.hr)

Your Application Express Workspace by default has an 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".

See Also:

For additional RESTful Service examples, go to the Learning Library. Search for free online training content, including Oracle by Example (OBE), demos, and tutorials. To access the Oracle Learning Library, go to:

http://www.oracle.com/technetwork/tutorials/index.html

Description of the illustration hr_example.gif

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 oracle_ex_temp.gif

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. See "Returning the Result Set in CSV Format (empinfo/)".

• 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. See "Retrieving Data Based on a Parameter (employees/{id})".

• 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. See "Returning the Result Set in JSON Format with Pagination Set (employees/)".

• employeesfeed/ - Employee information from the employees emp table is returned in CVS format. See "Returning the Result Set as a Feed (employeesfeed/)".

• employeesfeed/{id} - Employee information from the employees emp table for a specified id is displayed as a feed. The curly bracket identifies the parameter sent as part of the URI. See also "Retrieving Data Based on a Parameter (employees/{id})" and "Returning the Result Set as a Feed (employeesfeed/)".

• 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. See also "Retrieving Data Based on a Parameter (employees/{id})".

• 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. See "Returning the Result Set Based on a PL/SQL Block (empsecformat/{empname})".

Returning the Result Set in CSV Format (empinfo/)

The oracle.example.hr module includes the empinfo/ resource template to demonstrate a RESTful Service that returns data in CSV format. In this example, all the employee data from the emp table is returned by using a SQL Query.

To 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
     

     
     Description of the illustration emp_info.gif

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.

    Note:

    By default, the Resource Handler specifies that the resource must be accessed over a secure channel, HTTPS. If this is not required, on the Resource Handler page, set the Requires Secure Access option to No and click Apply Changes.

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.

    
    Description of the illustration empinfo_results.gif

    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.

Retrieving Data Based on a Parameter (employees/{id})

The oracle.example.hr module includes the employees/{id} resource template to demonstrate a RESTful Service that returns one row of data in JSON format based on the specified id.

For other example resource templates that return results based on a specified parameter see the employeesfeed/{id}, empsec/{empname} and empsecformat/{empname} resource templates for the oracle.example.hr Service Module.

To 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
     

   
   Description of the illustration employees_id.gif

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.

    
    Description of the illustration employees_id_res.gif

Note:

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

Returning the Result Set in JSON Format with Pagination Set (employees/)

The oracle.example.hr module includes the employees/ resource template to demonstrate a RESTful Service that returns data in JSON format. This example is returning the Result Set in JSON Format with Pagination Set Using Pagination Bind Variables. The Pagination bind variables are :row_offset and :row_count.

For other example resource templates that return results in JSON format see the empsec/{empname} resource template for the oracle.example.hr Service Module.

To 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 
     

   
   Description of the illustration r_emp.gif

8. Click Create.

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

9. Click Test.

   The JSON results display.

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.

Returning the Result Set as a Feed (employeesfeed/)

The oracle.example.hr module includes the employeesfeed/ resource template to demonstrate a RESTful Service that returns empno and ename values from the emp table and displays them as a feed.

A feed resource handler executes a SQL query and transforms the results into a JSON Feed representation. Each item in the feed contains a summary of a resource and a hyperlink to a full representation of the resource. The first column in each row in the result set must be a unique identifier for the row and is used to form a hyperlink of the form: path/to/feed/{id}, with the value of the first column being used as the value for {id}. The other columns in the row are assumed to summarize the resource and are included in the feed. A separate resource template for the full representation of the resource should also be defined.

To 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.

Returning the Result Set Based on a PL/SQL Block (empsecformat/{empname})

The oracle.example.hr module includes the empsecformat/{empname} resource template to demonstrate a RESTful Service that returns the results of an SQL Query formatted by a PL/SQL block.

To 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;
       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('</ul>');
               end if;
               sys.htp.print('Department ' || deptname || ' located in ' || deptloc || '<p/>');
               sys.htp.print('<ul>');
           end if;
      
           sys.htp.print('<li>' || emprecs.ename || ', ' || emprecs.job || ', ' || emprecs.sal || '</li>');
      
           prevdeptno := emprecs.deptno;
      
       end loop;
       sys.htp.print('</ul>');
       sys.htp.bodyclose;
       sys.htp.htmlclose;
     END;
     

   
   Description of the illustration empsecformat.gif

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.

    
    Description of the illustration empsecformat_res.gif

Note:

If your results are not correct, compare your empsecformat/{empname} GET resource handler with the oracle.example.hr empsecformat/{empname} GET resource handler.