5 Using RESTful Services

5.5.1 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, enter demo/. This is the base of the Uniform Resrouce 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.

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.

5.5.2 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 - Enter 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
    
    

5.5.3 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 demonstrates pagination by setting the pagination to a value smaller than the number of rows returned in the result.

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. Notice the uri:{$ref} for each item invokes the employees/{id} RESTful Service for the id specified in the link.

   
   Description of the illustration employees_rslt.gif
   
   

   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.

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

For other example resource templates that return results as a feed, see the employeesfeed/{id} resource template for the oracle.example.hr Service Module.

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
     

   
   Description of the illustration employeesfeed.gif
   
   

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.

   
   Description of the illustration employeesfeed_res.gif
   
   

5.5.5 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 - Enter 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
    
    

5.6.1 Creating a RESTful Service Module

To create a RESTful Service Module:

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

   The RESTful Services home page appears.

2. Click Create.

3. Fill in the appropriate fields and select the appropriate options. Mandatory fields are marked with a red asterisk (*). To learn more about a specific option, see the item Help for that option.

4. Specify the following RESTful Service Module settings:

   • Name (required) - Specify the RESTful Service Module name.

   • URI Prefix - Specify the URI template prefix.

   • Origins Allowed - Specify a comma-separated list of origins that are permitted to access the resource template.

   • Status - Select Published to make this service available for use, otherwise select Not Published.

   • Pagination Size - Specify the size of the pagination window. For database queries, this is the number of rows to return.

   • Required Privilege - Select the user privilege required to access this service module.

5. To add a resource template to this module, specify the following under Add a Resource Template:

   • URI Template - Enter the URI template to identify your Uniform Resource Identifiers. A URI template is simple syntax for describing URIs, for example:

     example/{id}
     

   • Priority - Specify the resource template's evaluation order. Higher numbers are evaluated first.

   • Entity Tag - Specify how the ETag HTTP Header for the resource is generated. See item Help for further details.

   • Entity Tag Query - Specify the SQL SELECT statement that returns one column and one row that is to be used to generate a custom entity tag. This option is only visible when the user selects Query for the Entity Tag.

6. If you want to add a resource handler to the resource template, specify the following under Add a Resource Handler:

   • Method - Specify the HTTP method to be used for the resource handler. Options include: GET, DELETE, POST, PUT. See item Help for further details.

   • Source Type - Select the source implementation for the selected HTTP method. Options include: Query, Query One Row, PL/SQL, Feed, Media Resource.

   • Format - Define how results are returned. Options include: JSON, CSV.

   • Source - Specify the SQL query or PL/SQL block responsible for handling the selected HTTP method.

   Note:

   To define additional resource handlers, complete the Resource Handler Creation wizard and then edit the created resource handler.

7. Click Create.

   The RESTful Service Module page appears.

5.6.2 Editing a RESTful Service Module

A RESTful Service Module can have associated resource templates and resource handlers. During the edit process for a particular RESTful Service Module, you can make changes to any of the associated resource templates and resource handlers.

To edit a RESTful Service Module:

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

   The RESTful Services home page appears.

2. Click the name of the module you want to edit.

   The RESTful Service Module edit page appears.

   
   Description of the illustration r_edit.gif
   
   

   Note:

   Use the RESTful Service tree on the left pane to view, edit and create resource templates and resource handlers.

3. To edit RESTful Service Module settings, make changes to the fields on the right pane under RESTful Services Module. See "Creating a RESTful Service Module" for further details.

4. To edit a resource template:

   1. Select the resource template you want to edit on the RESTful Service tree on the left panel. The resource template options appear on the right panel.

   2. Make your changes. See"Adding a Resource Template" for further details.

   3. Click Apply Changes.

5. To edit a resource handler:

   1. Select the resource handler you want to edit on the RESTful Service tree. The resource handler options appear on the right panel.

   2. Make your changes. See "Adding a Resource Handler" for further details.

   3. Click Apply Changes.

6. Click Apply Changes

5.6.3 Deleting a RESTful Service Module

Deleting a RESTful service module removes the module along with all resource templates and resource handlers associated with this module.

To delete a RESTful Service Module:

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

   The RESTful Services home page appears.

2. Click the name of the module you want to delete.

   The RESTful Service Module page appears.

3. Click Delete.

4. Click OK to confirm.

   The RESTful Services home page appears.

5.6.4 Managing RESTful Service Privileges

Privileges to access RESTful Web Service Modules can be restricted to Application Express users that belong to an Application Express user group.

To protect RESTful Service Modules by assigning RESTful Service Privileges:

1. Define an Application Express user group using Application Express Administration. See Using Groups to Manage Application Express Users in Oracle Application Express Administration Guide.

2. Create a RESTful Service Privilege based on one or more user groups. See "Creating a RESTful Service Privilege".

3. Specify which RESTful Web Service Modules are protected by this privilege. See "Assigning Privileges to RESTful Web Service Modules".

Topics:

• Assigning Privileges to RESTful Web Service Modules

• Creating a RESTful Service Privilege

• Editing a RESTful Service Privilege

• Deleting a RESTful Service Privilege

5.6.5 Exporting a RESTful Service Module

RESTful Service Module meta data can be exported to a file in SQL script format. The exported file can be imported on the same instance or another compatible instance of Application Express. See "Importing a RESTful Service Module".

To export a RESTful Service module:

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

   The RESTful Services home page appears.

2. Under Tasks, click the Export link.

   The Export RESTful Services page appears.

3. For RESTful Service, perform one of the following:

   • To export one module, select the module name.

   • To export all modules, select All Services.

4. Click Export.

   The Opening dialog appears.

5. Select export options.

6. Click OK.

5.6.6 Importing a RESTful Service Module

RESTful Service Module meta data that has been exported to a file in SQL script format can be imported. The imported module must have been exported on the same instance or another compatible instance of Application Express. See "Exporting a RESTful Service Module".

To import a RESTful Service module:

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

   The RESTful Services home page appears.

2. Under Tasks, click the Import link.

   The Import RESTful Services page appears.

3. For Import File, browse to and select the import file.

4. For File Character Set, select the character set used by the import file.

5. Click Import.

   The RESTful Services page appears.

5.7.1 Adding a Resource Template

To add a resource template to a RESTful Service Module:

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

   The RESTful Services home page appears.

2. Click the RESTful Service for which you want to create a resource template.

3. On the RESTful Service tree, click Create Template.

   The resource template settings appear on the right panel.

4. Fill in the appropriate fields and select the appropriate options. Mandatory fields are marked with a red asterisk (*). To learn more about a specific option, see the item Help for that option.

5. Specify the following:

   • URI Template - Enter the URI template to identify your Uniform Resource Identifiers. A URI template is simple syntax for describing URIs, for example:

     example/{id}
     

   • Priority - Specify the template's evaluation order. Higher numbers are evaluated first.

   • Entity Tag - Specify how the ETag HTTP Header for the resource is generated. See item Help for further details.

   • Entity Tag Query - Specify the SQL SELECT statement that returns one column and one row that is to be used to generate a custom entity tag. This option is only visible when the user selects Query for the Entity Tag.

6. Click Create.

5.7.2 Editing a Resource Template

To edit a resource template:

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

   The RESTful Services home page appears.

2. Click the name of the module the resource template you want to edit belongs to.

   The RESTful Service Module page appears.

3. On the RESTful Service tree, click the resource template you want to edit.

   The selected resource template is highlighted on the RESTful Service tree and the corresponding resource template settings appear on the right panel.

4. Make the changes you want. See "Adding a Resource Template" for further details.

5. Click Apply Changes.

5.7.3 Deleting a Resource Template

Deleting a resource template removes the template along with all resource handlers associated with this template.

To delete a resource template:

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

   The RESTful Services home page appears.

2. Click the name of the module you want to update.

   The RESTful Service Module page appears.

3. On the RESTful Service tree, click the resource template you want to delete.

   The resource template settings appear on the right panel.

4. Click Delete.

5. Click OK to confirm.

   The RESTful Service Module appears.

5.8.1 Adding a Resource Handler

To add a resource handler to a resource template:

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

   The RESTful Services home page appears.

2. Click the RESTful Service you want to add a resource handler to.

   The RESTful Service Module page appears.

3. On the left panel, under the associated resource template, click Create Handler.

   The resource handler settings appear on the right panel.

4. Fill in the appropriate fields and select the appropriate options. Mandatory fields are marked with a red asterisk (*). To learn more about a specific option, see the item Help for that option.

5. Under Resource Handler, specify the following:

   • Method - Specify the HTTP method to be used for the resource handler. See item Help for further details.

   • Source Type - Identify the strategy type used to generate the resource. See item help for further information.

   • Requires Secure Access - Identify whether the resource should be accessed over a secure channel, for example, HTTPS. Set to Yes if secure access is required

   • Pagination Size - Identify the size of the pagination window. For database queries, this is the number of rows to return.

6. Under Source, enter the SQL query or PL/SQL block responsible for handling the selected HTTP method. Click Example at the bottom of the page for further details.

7. To test the behavior of the resource handler:

   1. Click Apply Changes.

   2. If you have bind variables in your SQL query or PL/SQL block, click Set Bind Variables and define the bind variables before testing.

   3. Click Test.

      A new window appears displaying the JSON result of executing the RESTful Service. The URL used in the test is composed of the value of the URI prefix and URI Template, along with any parameters.

      Note:

      To make the test results easier to read, the installation of a JSON Viewer in your browser is recommended.

8. Click Create.

   The RESTful Services Module page appears.

9. To add parameters to the resource handler:

   1. Click Create Parameter.

      The parameter settings appear on the Resource Handler Parameter panel.

   2. For Name, enter the identifying parameter name.

   3. For Bind Variable Name, enter the parameter bind variable name used within the SQL query or PL/SQL block.

   4. For Access Method, select the HTTP method. Options include IN, IN/OUT, or OUT. URI Template parameters can only be IN. A value of IN for a header parameter implies the header will be present in the HTTP request. A value of IN/OUT indicates the value will be present in both the HTTP request and response. A value of OUT indicates the value will only be present in the HTTP response.

   5. For Source Type, select the parameter source type.

   6. For Parameter Type, select the data type of the parameter.

   7. Click Create.

      The resource handler page appears with the added parameter included under Parameters at the bottom of the right panel.

   8. For each parameter you want to add, repeat the above steps in this subsection.

5.8.2 Editing a Resource Handler

To edit a resource handler:

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

   The RESTful Services home page appears.

2. Click the name of the Resource Service Module the resource handler belongs to.

   The RESTful Service Module page appears.

3. On the RESTful Service tree, click the resource handler you want to edit.

   The resource handler settings appears on the right panel.

4. Make the changes you want. See "Adding a Resource Handler" for further details.

5. Click Apply Changes.

   The RESTful Service Module page appears.

5.8.3 Deleting a Resource Handler

To delete a resource handler:

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

   The RESTful Services home page appears.

2. Click the name of the module the resource handler belongs to.

   The RESTful Service Module page appears.

3. On the RESTful Service tree, click the resource handler you want to delete.

   The resource handler settings appear on the right panel.

4. Click Delete.

5. Click OK to confirm.

   The RESTful Service Module appears.