Purpose
This tutorial covers creating a RESTful Web Service and accessing the Web Service through an application in Application Express 5.1. It also covers consuming the Web Service using a REST client.
Time to Complete
Approximately 40 minutes.
Overview
Web Services enable applications to interact with one another over the web in a platform-neutral, language independent environment. In a typical Web Services scenario, a business application sends a request to a service at a given URL by using the protocol over HTTP. The service receives the request, processes it, and returns a response. Web Services are typically based on Simple Object Access Protocol (SOAP) or Representational State Transfer (REST) architectures. RESTful Web Services are result oriented. The scope of the Web Service is found in the URI and the method of the service is described by the HTTP method that is used such as GET, POST, PUT, and DELETE.The RESTful Web Service Wizard is a set of pages in the SQL Workshop area of Oracle Application Express that help you to create a RESTful Web Service declaratively. Once you have defined a RESTful Web Service, you can call it with a unique Uniform Resource Identifier (URI). RESTful Web Services are organized within Oracle APEX through a hierarchy of a module, a resource template and handlers within a template. The resource template includes a prefix for the URI, which is completed with a final portion of the URI defined by the handler.
This tutorial covers creating a RESTful Web Service declaratively using Oracle Application Express's SQL Workshop tool, and then consuming the same service by creating an application and adding a Web Service Reference to the RESTful Web Service. The RESTful Web Service is also consumed using a REST client.
Please keep in mind the following while running this tutorial:
- Logging into your Oracle Application Express workspace: Your Oracle Application Express workspace may reside in an on-premises Oracle Database or in Oracle Database Cloud Services. The login credentials differ depending on where your workspace is located:
- Logging into Oracle Application Express in a Oracle Database Cloud Service: Go to the Oracle Help Center for Cloud, and select Platform and Infrastructure. From here, select your Database Cloud Service and the Get Started page will appear.
- Logging in to Oracle Application Express on-premises: From your browser, go to the location of your on-premises installation of your Oracle Application Express workspace provided by your Workspace Administrator.
- Application ID: Screenshots in this tutorial show a blurred Application ID. Your Application ID can be any value assigned automatically while creating the application.
- Schema: If you are accessing an Oracle Application Express workspace in Database Schema Service, you have one schema assigned to you with a schema name that you cannot change. If you are accessing the workspace in an on-premises Oracle database, you may have more than one schema assigned to your workspace by the Oracle Application Express Instance Administrator.
What Do You Need?
Before starting this tutorial, you should:
- Have access to an Oracle Database 11.2.x.x or later release, including Enterprise Edition and Express Edition (Oracle Database XE), either on-premises or in a Database Cloud Service.
- Install Oracle Application Express Release 5.1 into your Oracle Database with RESTful Services configured in Oracle Application Express (for on-premises only).
- Download and unzip the files.zip into your working directory.
- Execute
Create_Employees.sql
from the extracted files, to create required database objects.
In this topic, you create a RESTful Web Service using RESTful Services tool in SQL Workshop. The RESTful Web Service Wizard is a set of pages in SQL Workshop that help you to create a new RESTful Web Service declaratively. The RESTful Web Service calls a specific SQL statement in your database.
Creating a RESTful Web Service with GET and PUT Resource Handlers
To create a RESTful Web Service on the Employees table with sample GET and PUT service handlers, perform the below steps:
From the Oracle Application Express Home page, select the SQL Workshop tab and select RESTful Services.
From the RESTful Services page, select the Create a New RESTful Service option.
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.
Under Resource Template, enter employees/ for URI Template to identify your Uniform Resource Identifier (URI), and scroll down further.
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
The GET Handler is created under employees/. To edit its properties, click GET under employees/.
Select No for Requires Secure Access, and click Apply Changes.
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.You are prompted to save the file which you can then view using a CSV editor.
The CSV format result set is displayed.
Let us now create a Handler for the POST method in the same Web Service. Click Create Handler under employees/.
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;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.
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.
The OUT parameter is created.
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.
Creating a Resource Handler with Query One Row
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:
Click Create Template.
Enter employees/{id} for URI Template, and click Create.
Click Create Handler under employees/{id}.
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
Scroll down and click Create Parameter.
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.
You want to change the Source Type. Under Parameters, click the id link under Name.
Select URI for Source Type, and click Apply Changes.
Before testing this handler, you have to set a bind variable to pass a value for the input parameter(id). Click Set Bind Variables >.
Enter 103 for :ID, and click Test.
Complete details of the employee with employee_id = 103 is displayed.
Creating a Resource Handler with Employees Feed
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:
Click Create Template.
Enter employeesfeed/ for URI Template, and click Create.
Under employeesfeed/, click Create Handler.
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_nameUnder
employeesfeed/
, click GET to open the Resource Handler Editor.Scroll down the page, and click Test.
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 withemployee_id = 100
ishttps://:
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:<hostname>
:<port>/ords/hr/employeesfeed/100On-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:
where<user>
.db.us2.oraclecloudapps.com/hr/employeesfeed/100https://databasetrial:
is the Service URL for the cloud service subscription, employeesfeed is the Resource Tem<user>
.db.us2.oraclecloudapps.com/hr/