6.5 How to Create the ORDS Based RESTful Services Module Example

In ORDS Based RESTful Services, you can choose to install the sample service oracle.example.hr, when registering schema with ORDS.

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

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:

• empsec/:empname - Employee information for a specified employee name, empname, is returned and displayed in JSON.

• empsecformat/:empname - Employee information for a specified employee name, empname, is returned and formatted based on the execution of a PL/SQL block.

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

• version/- Returns the version of the RESTful service module.

• employees/:id - Employee information from the employees emp table for a specified id is returned.

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

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

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 ORDS Based RESTful Services.

   The ORDS RESTful services page appears.

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

   The Module Definition page appears.

4. Under Resource Templates block on the left panel, click Create Template.

   The ORDS Template Definition page appears.

5. For URI Template, enter empsecformat/:empname.
6. Click Create Template.
7. Under the empsecformat/:empname resource template, click Create Handler.

   The ORDS Handler Definition page appears.

8. Make the following selections and entries:

   • Method - Select GET.

   • Source Type - Select PL/SQL.

   • 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;
     

9. Click Create Handler.

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

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 ORDS Based RESTful Services.

   The ORDS RESTful services page appears.

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

   The Module Definition page appears.

4. Under Resource Templates block on the left panel, click Create Template.

   The ORDS Template Definition page appear.

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

   The employees/ resource template displays on the left panel.

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

   The ORDS Handler Definition page appear.

8. Make the following selections and entries:

   • Method - Select GET.

   • Source Type - Select Query.

   • Format - Select JSON.

   • Source - Enter the following:

     select empno "$uri", rn, empno, ename, job, hiredate, mgr, sal, comm, deptno
       from (
            select emp.*
                 , row_number() over (order by empno) rn
              from emp
            ) tmp
     

9. Click Create Handler.

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

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 ORDS Based RESTful Services.

   The ORDS RESTful services page appear.

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

   The Module Definition page appears.

4. Under Resource Templates block on the left panel, click Create Template.

   The ORDS Template Definition page appear.

5. For URI Template, enter employees/:id.
6. Click Create Template.

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

7. Under the employees/:id resource template, click Create Handler.

   The ORDS Handler Definition page appear.

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

   • 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
     

9. Click Create Handler.

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 ORDS Based RESTful services.

   The ORDS RESTful services page appear.

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

   The Module Definition page appears.

4. Under Resource Templates block on the left panel, Click Create Template.

   The ORDS Template Definition page appear.

5. For URI Template, enter empinfo/.
6. Click Create Template.

   The empinfo/ resource template displays on the left panel.

7. Under the empinfo/ resource template, click Create Handler.
8. Make the following selections and entries:

   • Method - select GET.

   • 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
     

9. Click Create Handler.

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 ORDS Based RESTful services.

   The ORDS RESTful services page appear.

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

   The Module Definition page appears.

4. Under Resource Templates block on the left panel, click Create Template.

   The ORDS Template Definition page appear.

5. For URI Template, enter employeesfeed/.
6. Click Create Template.
7. Under the employeesfeed/ resource template, click Create Handler.

   The ORDS Handler Definition page appear.

8. Make the following selections and entries:

   • Method - select GET.

   • Source Type - select Feed.

   • Pagination Size - enter 25.

   • Source - enter the following:

     select empno, ename from emp  order by deptno, ename
     

9. Click Create Handler.