5 Using RESTful Services

RESTful Services enable the declarative specification of RESTful services used to access the database. These services work with the Oracle Application Express Listener to enable the consumption of these services. This section describes how to create, edit and delete RESTful Services.

Note:

If the instance administrator has disabled RESTful Services for this Application Express instance, RESTful Services are not available for this instance and the RESTful Services icon does not display. See "Configuring SQL Workshop" in Oracle Application Express Administration Guide.

Topics:

5.1 What Is REST?

Representational State Transfer (REST) is a style of software architecture for distributed hypermedia systems such as the World Wide Web. A service is described as RESTful when it conforms to the tenets of REST. Although a full discussion of REST is outside the scope of this document, a RESTful Service has the following characteristics:

  • Data is modelled as a set of resources. Resources are identified by URLs.

  • A small set of operations are used to manipulate resources (for example, PUT, POST, GET, DELETE).

  • A resource can have multiple representations (for example, a blog might have a HTML representation and a RSS representation).

  • Services are stateless and since it is likely that the client wants to access related resources, these should be identified in the representation returned, typically by providing hypertext links.

5.2 About RESTful Services

RESTful Services enable an application to publish Web services that are accessed by other applications to exchange data. For example, you can configure a RESTful Service to return all employee names for a particular department. The data exchange for a RESTful Service follows the Representational State Transfer (REST) architectural style.

For each Web service you want your application to provide, you must configure a RESTful Service Module. This module is a grouping of common templates, known as resource templates under a common Universal Resource Identifier (URI) prefix. For each method associated with the same resource template prefix that you want to expose through this Web service, you must define a resource handler. For instance, if you want to provide a method to return data and another to store data, you must define a resource handler for each operation.

When you create a RESTful Service, you supply the necessary information about the structure of the request, response and operation to be performed including:

  • A URI Template identifying the set of resources the service provides

  • One or more HTTP methods that the service supports along with the SQL query or PL/SQL block to be performed. There are four HTTP methods including GET, DELETE, POST and PUT. Only one hadler per HTTP method is permitted. For example, the empinfo resource handler can only have one resrouce handler using GET, and only one using PUT.

  • HTTP Header values that should be bound to parameters of the query block

  • Parameters expected by the Web service

  • Type of input expected by the Web service

  • The format of the response

See Also:

For an example RESTful Web Service Module, see "About the Example RESTful Service Module (oracle.example.hr)"

This section describes how to configure RESTful Services. If you want your application to use a Web service, you must configure RESTful Web References. See Implementing Web Services in Oracle Application Express Application Builder User's Guide.

5.3 RESTful Service Requirements

In order for the RESTful Service utility to be available and function properly, the following requirements must be met:

  • The instance administrator must enable RESTful Services for this Application Express instance. See Oracle Application Express Administration Guide.

  • The Oracle Application Express Listener version 2.0 or higher must be used. The Oracle XML DB HTTP Server with the embedded PL/SQL gateway and Oracle HTTP Server with mod_plsql do not support this functionality. See Oracle Application Express Listener Installation and Developer Guide.

5.4 Accessing RESTful Services

To access RESTful Services:

  1. Log in to the Workspace home page.

  2. Click SQL Workshop.

  3. Click RESTful Services.

    Description of r_service.gif follows
    Description of the illustration r_service.gif

    The RESTful Services home page displays.

5.5 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 hr_example.gif follows
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 oracle_ex_temp.gif follows
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:

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 emp_info.gif follows
      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 empinfo_results.gif follows
    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 employees_id.gif follows
    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 employees_id_res.gif follows
    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.

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 r_emp.gif follows
    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 employees_rslt.gif follows
    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 employeesfeed.gif follows
    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 employeesfeed_res.gif follows
    Description of the illustration employeesfeed_res.gif

Note:

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

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 empsecformat.gif follows
    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 empsecformat_res.gif follows
    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.

5.6 Managing RESTful Service Modules

A RESTful Service Module is a grouping of common templates, known as resource templates, under a common Uniform Resource Identifiers (URI) prefix. The specification of a URI prefix is optional. If defined for the RESTful Service Module, it is prepended to all resource templates. A priority value is used to choose between resource templates when their patterns both match a single request URI. In such cases, the resource template with the highest priority value is chosen.

Topics:

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 r_edit.gif follows
    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:

5.6.4.1 Assigning Privileges to RESTful Web Service Modules

There are a couple of ways to assign privileges to RESTful Web Service Modules:

Note:

After a RESTful Service Privilege is deleted, all RESTful Service Modules protected by that privilege are unprotected. See "Deleting a RESTful Service Privilege".

5.6.4.2 Creating a RESTful Service Privilege

You can create a RESTful Service Privilege to restrict access to specified users for one or more RESTful Web Service modules. A RESTful Service Privilege can include users belonging to one or more user groups. RESTful Web Service modules protected by this privilege display the privilege name for Required Privilege on the RESTful Service Module page.

To create a RESTful Service Privilege:

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

    The RESTful Services home page appears.

  2. Under Tasks, click the RESTful Services Privileges link.

    The RESTful Service Privileges page appears.

  3. Click Create.

    The RESTful Service Privileges Details page appears.

  4. For Name, enter a name to identify the privilege.

  5. For Label, enter a label value to identify the privilege.

  6. For Assigned Groups, select the user groups this privilege is assigned to.

  7. For Description, enter a description of this privilege.

  8. For Protected Modules, use the arrows to select the modules this privilege is assigned to.

  9. Click Create.

    The RESTful Service Privileges page appears.

To verify the privilege has been assigned to the appropriate modules:

  1. Click RESTful Services in the breadcrumb.

    The RESTful Services page appears.

  2. Click the protected RESTful Service Module.

    The RESTful Service Module page appears.

  3. Make sure the Required Privilege displays the created RESTful Service Privilege.

  4. Repeat these verification steps to ensure all modules assigned to this privilege are protected.

5.6.4.3 Editing a RESTful Service Privilege

You can modify a RESTful Service Privilege. Changes to the specified Protected Modules will be reflected in the Required Privilege setting on the RESTful Service Module page for affected modules.

To edit a RESTful Service Privilege:

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

    The RESTful Services home page appears.

  2. Under Tasks, click the RESTful Services Privileges link.

    The RESTful Service Privileges page appears.

  3. Click a RESTful Service Privilege.

    The RESTful Service Privileges Details page appears.

  4. Make modifications.

  5. Click Apply Changes.

    The RESTful Service Privileges page appears.

5.6.4.4 Deleting a RESTful Service Privilege

You can remove a RESTful Service Privilege. After deleting the privilege, any RESTful Service Modules that were protected by this privilege will be unprotected until reassigned another privilege.

To delete a RESTful Service Privilege:

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

    The RESTful Services home page appears.

  2. Under Tasks, click the RESTful Services Privileges link.

    The RESTful Service Privileges page appears.

  3. Click a RESTful Service Privilege.

    The RESTful Service Privileges Details page appears.

  4. Click Delete.

    The RESTful Service Privileges page appears.

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

Note:

If the imported module contains a RESTful Service name that exists in this workspace, the existing RESTful Service is deleted and replaced with the definition from the imported 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 Managing Resource Templates

Resource templates are a means of grouping URIs and associating them with a particular RESTful Service Module. Resource templates can have one or more resource handlers. Only one resource handler per HTTP method is permitted. For example, a resource template can have only one GET method, only one DELETE method and so on.

Topics:

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 Managing Resource Handlers

A resource handler is a query or an anonymous PL/SQL block responsible for handling a particular HTTP method. Multiple resource handlers can be defined for a resource template, however only one resource handler can be defined for a HTTP method.

Parameters to a resource handler can be manually defined to bind HTTP headers to the resource handler, or to cast a URI template parameter to a specific data type. Parameters declared in the URI template are implicitly passed to the resource handler. For example, a resource handler might need to know the value of the HTTP Accept-Language header in order to localize the generated representation.

Topics:

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.