Previous Next JavaScript must be enabled to correctly display this content

## 7.5 How to Create the ORDS Based RESTful Services Module Example

Learn how to create the ORDS Based RESTful Services module example.

Oracle APEX workspace by default has an example RESTful Service Module, `oracle.example.hr`. You can install the sample service while registering the schema with ORDS.

- About the Example RESTful Service Module (oracle.example.hr)
  In ORDS Based RESTful Services, you can choose to install the sample service `oracle.example.hr`, when registering schema with ORDS.
- Returning the Result Set Based on a PL/SQL Block (empsecformat/:empname)
  You can add and test your own resource template that uses a PL/SQL block to format results.
- Returning the Result Set in JSON Format with Pagination Set (employees/)
  You can add and test your own resource template that retrieves data in JSON format and demonstrates pagination.
- Retrieving Data Based on a Parameter (employees/:id)
  You can add and test your own resource template that retrieves employee data based on a parameter.
- Returning the Result Set in CSV Format (empinfo/)
  You can build and test your own example RESTful Service Module that retrieves data in CSV format.
- Returning the Result Set as a Feed (employeesfeed/)
  You can add and test your own resource template that displays return data as a feed.

Parent topic: Enabling Data Exchange with RESTful Services

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

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

See Also:

Accessing RESTful Services

Parent topic: How to Create the ORDS Based RESTful Services Module Example

### 7.5.2 Returning the Result Set Based on a PL/SQL Block (`empsecformat/:empname`)

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

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

Parent topic: How to Create the ORDS Based RESTful Services Module Example

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

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

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

Parent topic: How to Create the ORDS Based RESTful Services Module Example

### 7.5.4 Retrieving Data Based on a Parameter (`employees/:id`)

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

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

Parent topic: How to Create the ORDS Based RESTful Services Module Example

### 7.5.5 Returning the Result Set in CSV Format (`empinfo/`)

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

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

Parent topic: How to Create the ORDS Based RESTful Services Module Example

### 7.5.6 Returning the Result Set as a Feed (`employeesfeed`/)

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

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

Parent topic: How to Create the ORDS Based RESTful Services Module Example