Creating and Using a RESTful Web Service in Application Express 5.1

To create a RESTful Web Service on the Employees table with sample GET and PUT service handlers, perform the below steps:

1. In the Application Express login page, enter the following login credentials and click Sign In:

   • Workspace: obe

   • Username: obe

   • Password: oracle

   

2.  From the Oracle Application Express Home page, select the SQL Workshop tab and select RESTful Services.

   

3. From the RESTful Services page, select the Create a New RESTful Service option.

   

4. A page loads with entries grouped under three different categories named RESTful Services Module, Resource Template, and Resource Handler. Under RESTful Services Module, enter employees for Name, and scroll down further.

   

5. Under Resource Template, enter employees/ for URI Template to identify your Uniform Resource Identifier (URI), and scroll down further.

   

6. Under Resource Handler, select GET for Method, Query for Source Type, CSV for Format. This identifies the HTTP method to be used for the Resource Handler.
   Enter the following SQL query for Source, and click Create Module.
   
   select * from employees

   

7. The GET Handler is created under employees/. To edit its properties, click GET under employees/.

   

8. Select No for Requires Secure Access, and click Apply Changes.

   

9. To test the behavior of the RESTful Service Handler, click Test.
   
   Note: If your screen does not show a Test button, please ensure that RESTful Services are configured in your Oracle Application Express installation properly.

   

10. You are prompted to save the file which you can then view using a CSV editor.

    

11. The CSV format result set is displayed.

    

12. Let us now create a Handler for the POST method in the same Web Service. Click Create Handler under employees/.

    

13. Select POST for Method and PL/SQL for Source Type. Enter application/json for MIME Types Allowed. Select No for Requires Secure Access. Enter the following PL/SQL code for Source, and click Create.
    
    declare
  id employees.employee_id%TYPE;
begin
  id := employees_seq.nextval;

  insert into employees
  (employee_id,first_name, last_name, email, hire_date, job_id)
  values
  (id, :first_name, :last_name, :email, to_date(:hire_date, 'DD-MM-YYYY'),
  :job_id);

  :employee_id := id;
end;
    

    

14. Scroll down the page, and click Create Parameter to add an OUT parameter to the handler that will return the newly created employee’s ID.

    

15. Enter employee_id for Name and Bind Variable Name. Select OUT for Access Method, HTTP Header for Source Type, String for Parameter Type, and click Create.

    

16. The OUT parameter is created.

    

17. Click Create Parameter again to add the following IN parameters to the handler.

    Name	Bind Variable Name	Access Method	Parameter Type
    first_name	first_name	IN	String
    email	email	IN	String
    last_name	last_name	IN	String
    hire_date	hire_date	IN	String
    job_id	job_id	IN	String

    

    In the next section, you create a new template to retrieve JSON result set based on Query One Row with a bind variable.

In this section, you will create a RESTful Service that provides detailed information of an employee, given the employee id. The result is returned in JSON format. Perform the following steps:

1. Click Create Template.

   

2. Enter employees/{id} for URI Template, and click Create.

   

3. Click Create Handler under employees/{id}.

   

4. Select GET for Method, Query One Row for Source Type, and No for Requires Secure Access.
   Enter the following SQL Query for Source, and click Create.
   
   select * from employees where employee_id = :id

   

5. Scroll down and click Create Parameter.

   

6. Enter id for Name and Bind Variable Name. Select IN for Access Method, HTTP Header for Source Type, String for Parameter Type, and click Create.

   

7. You want to change the Source Type. Under Parameters, click the id link under Name.

   

8. Select URI for Source Type, and click Apply Changes.

   

9. Before testing this handler, you have to set a bind variable to pass a value for the input parameter(id). Click Set Bind Variables >.

   

10. Enter 103 for :ID, and click Test.

    

11. Complete details of the employee with employee_id = 103 is displayed.

    

In this section, you will be creating a RESTful service of a feed source type. The feed results are rendered in JSON format. Each item in the feed contains a summary of a resource and a hyperlink to a full representation of the resource. Perform the below steps:

1. Click Create Template.

   

2. Enter employeesfeed/ for URI Template, and click Create.

   

3. Under employeesfeed/, click Create Handler.

   

4. Select GET for Method, Feed for Source Type, and No for Requires Secure Access. Under Source, enter the following SQL query, and click Create.
   
   select employee_id, first_name
from employees
order by employee_id, first_name

   

5. Under employeesfeed/, click GET to open the Resource Handler Editor.

   

6. Scroll down the page, and click Test.

   

7. The results are rendered in JSON format. Each item consists of a URI which contains the base URI from this RESTful Service, and the value of employee_id used as the parameter. For the Feed source type, the first column must be a unique identifier. It will be transformed into a hyperlink when this RESTful Service is called.
   In this example, employee_id is the first column and will turn into a hyperlink.
   
   For example, in the screenshot shown below, the URI for an employee with employee_id = 100 is https://:<hostname>:<port>/ords/hr/employeesfeed/100

Note: The URI shown in this example is specific to the database On-Premise subscription used for executing this tutorial, and it might be different for you. The value of the URI also depends on whether you are performing this tutorial On-Premises or on a Cloud Service. In general, the URI formats are as follows:

   On-Premises	http://localhost:<Port where ords is set up>/ords/<schema name>/<Resource Template Name>/<employee_id> For example: http://localhost:9090/ords/hr/employeesfeed/100 where ords is set up on port 9090 during Oracle APEX installation, hr is the schema name, employeesfeed is the Resource Template name, and 100 is the employee_id.
   On a Cloud Service	<Service URL as received in the Welcome email from Oracle Cloud>/<Resource Template Name>/<employee_id> For example: https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/employeesfeed/100 where https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/ is the Service URL for the cloud service subscription, employeesfeed is the Resource Template name, and 100 is the employee_id.

   

8. Select the URI for one of the employee_ids, and copy it to clipboard.

   

9. Open a browser, paste the copied URI, and press Enter. Notice that the details of that particular employee are returned as a JSON result set.

   

In this section, you will be creating a RESTful service of a feed source type given the Department ID. The feed results are rendered as JSON. Perform the below steps:

1. Click Create Template.

   

2. Enter employeesfeed/{id}, and click Create.

   

3. Under employeesfeed/{id}, click Create Handler.

   

4. Select Get for Method, Feed for Source Type, and No for Requires Secure Access. Under Source, enter the following SQL query, and click Create.
   
   select employee_id, first_name
from employees
where department_id = :id

   

5. Scroll down further, and click Create Parameter > to add an IN parameter to the handler that will receive the department_id.

   

6. Enter id for Name and Bind Variable Name. Select IN for Access Method, HTTP Header for Source Type, String for Parameter Type, and click Create.

   

7. You want to change the Source Type. Under Parameters, click the id link under Name.

   

8. Select URI for Source Type, and click Apply Changes.

   

9. Before testing this handler, you have to set bind variable to pass a value for the input parameter, id. Click Set Bind Variables >.

   

10. Enter 60 for :ID, and click Test.

    

11. The results are rendered in JSON format. Each item consists of a URI which contains the base URI from this RESTful Service, and the value of department_id used as the parameter. For the Feed source type, the first column must be unique identifier and will be transformed into a hyperlink when this RESTful Service is called. 
    In this example, department_id is the first column and will turn into a hyperlink. 
    
    For example, in the screenshot shown below, the URI for an employee with department_id = 60 is http://<hostname>:<port>/ords/hr/employeesfeed/60?page=1 where page=1 indicates that these results are part of page 1. If there are many records in the result set, the results can span across page 2 and so on.

    Note: The URI shown in this example is specific to the database On-Premise service subscription used for executing this tutorial, and it might be different for you. The value of the URI also depends on whether you are performing this tutorial On-Premises or on a Cloud Service. In general, the URI formats are as follows:

    On-Premises

    http://localhost:<Port where ords is set up>/ords/<schema name>/<Resource Template Name>/<department_id>?page=1 For example: http://localhost:9090/ords/hr/employeesfeed/60?page=1 where ords is set up on port 9090 during Oracle APEX installation, hr is the schema name, employeesfeed is the Resource Template name and 60 is the department_id.

    On a Cloud Service

    <Service URL as received in the Welcome email from Oracle Cloud>/<Resource Template Name>/<department_id>?page=1 For example: https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/employeesfeed/60?page=1 where https://databasetrial:<user>.db.us2.oraclecloudapps.com/hr/ is the Service URL for the cloud service subscription, employeesfeed is the Resource Template name, and 60 is the department_id.