Before You Begin
Purpose
The REST Development in SQL Developer 4.1 provides users with the full capabilities of REST. REST endpoints can be defined along with basic operations including create, query, update and delete. More complex SQL and PL/SQL operations can be defined and mapped. The results can be displayed in JSON and other formats.
In this lab you will be going through the following:
- Retrieve information from tables based on a parameter using GET method.
- Insert data into Employees table using PUT method.
- Update data in Departments table using POST method.
- Delete data from Departments table using DELETE method.
Time to Complete
30 min
Background
This Hands-On-Lab series will show you how to use the RESTful Services feature in Oracle SQL Developer 4.1. You will be installing Oracle REST Data Services bundled in SQL Developer. Once installed, you will be creating, editing, validating, uploading and testing the RESTful Service.
The series include the following labs. These labs build on one another so it must be done in the following order.
- Installing and Administering Oracle REST Data Services (ORDS) with SQL Developer
- AutoREST Enable a Schema
- REST Development
- Insert, Update, and Delete Data Using RESTful Services
An Oracle VM Virtual Box virtual machine is set up with the required software and files to execute this Hands-On-Lab
Installed Software :
- Oracle Database 12.1.0.2
- Oracle SQL Developer 4.1.3
- Browser Support :
- Mozilla Firefox with JSONView extension (to view the JSON formatted in the browser)
- RESTful Services Testing Tool :
- WizTools.org REST Client release 3.5
What Do You Need?
Before starting this tutorial, you should have:
- Access to Oracle VM Virtual Box
virtual machine in which all of the
software and relevant files for this Hands-On-Lab
are made available.
- Completed Lab 1: Installing and Administering REST Data Services with SQL Developer in the series.
- Completed Lab 2: AutoREST Enable Database Objects in the series.
- Completed Lab 3: REST Development in the series.
Setting up Environment
If you are continuing from a previous SQL Developer RESTful Services hands-on-lab, close any resource handler editors that are displayed by clicking on the x (close button) located on the resource handler editor tab.
Perform the following steps to set up the environment required before performing insert, update, or delete operations on hr schema:
-
Go to HR worksheet and paste the following code to add a trigger to the Employees table. Then, click Run Script icon. If the HR worksheet tab is not displayed, go to Connections, right-click on hr and select Open SQL worksheet.
create or replace trigger EMPLOYEES_EMPLOYEE_ID_TRG
before insert on employees
for each row
begin
if :new.employee_id is null then
select employees_seq.nextval into :new.employee_id from sys.dual;
end if;
end; -
EMPLOYEES_EMPLOYEE_ID_TRG is compiled successfully. In the next section, you will create RESTful service to retrieve data using parameters.
Retrieving Data Using a Parameter
Perform the following steps to create a RESTful Service which retrieves the employee information based on the parameter id using the HTTP Method GET.
-
In the RESTful Services navigator, right-click demo and select Add Template.
-
The RESTful Services Wizard displays Specify Template. Enter employees/:id for URI Pattern and click Next >.
Note: The parameter :id is the employee number.
-
The RESTful Services Wizard displays Specify Handler. Select Query One Row for Source Type.
-
Click Next >.
-
The RESTful Services Wizard displays RESTful Summary. Click Finish.
-
In the RESTful Services navigator, the resource module demo contains the resource template employees/:id. Expand employees/:id by clicking + symbol beside it.
-
Click GET under employees/:id.
-
The resource handler editor GET employees/:id is displayed on the right side. Locate the connection drop-down menu on the right side of the SQL worksheet toolbar and select hr.
-
Enter the following query in the SQL worksheet and click Run statement icon:
select * from employees where employee_id = :id
-
The Enter Binds dialog displays. Enter 200 for Value, and click Apply.
-
The Query Result tab displays the information for employee id, 200.
-
Click Details tab.
-
Click the red pushpin icon to pin this resource handler editor so that it does not get automatically removed when selecting a different resource handler. View the URI Pattern.
-
You want to add Locations/id: template. Right-click demo and select Add Template.
-
The RESTful Services Wizard displays Specify Template. Enter locations/:id for URI Pattern and click Next >.
Note: The parameter :id is the location id.
-
The RESTful Services Wizard displays Specify Handler. Select Query One Row for Source Type, and click Next >.
-
The RESTful Services Wizard displays RESTful Summary. Click Finish.
-
In the RESTful Services navigator, the resource module demo contains the resource template locations/:id. Expand locations/:id by clicking + symbol beside it. Then, click GET under locations/:id.
-
The resource handler editor GET locations/:id is displayed on the right side. Enter the following query in the SQL worksheet and click Run statement icon:
select * from locations where location_id = :id
-
Select hr for Connection, and click OK.
-
The Enter Binds dialog displays. Enter 3000 for Value, and click Apply.
-
The Query Result tab displays the information for location id, 3000.
-
In the RESTful Services navigator, right-click demo and select Upload.
-
Click OK.
-
You are now ready to test the Restful Service. Open WizTools.org RESTClient (located on the desktop).
-
Enter http://localhost:9090/ords/hr/demo/employees/200 for URL, and select GET for HTTP Method. Then, click Go icon located next to the URL field on the top right side.
-
In the HTTP Response (lower section), select the Body tab. A single row formatted in JSON is displayed in the Body tab.
-
Now you want to test the RESTful service created for locations table. Enter http://localhost:9090/ords/hr/demo/locations/3000 for URL and click Go icon.
-
In the HTTP Response (lower section), select the Body tab. A single row formatted in JSON is displayed in the Body tab.
Inserting Data Using RESTful Services
In this section, you will create a RESTful Service to insert data using the HTTP Method POST. You will use the RESTClient tool to test your RESTful Service and to verify the results.
-
In the RESTful Services navigator, right-click employees/, select Add Handler and then select POST.
-
In the Mime Types, click the + icon to add a row to the Mime Types.
-
Enter application/json and click Apply.
-
In the RESTful Services navigator, the resource template employees/ contains the resource handler POST.
The resource handler editor POST employees/ is displayed on the right-side. Select hr from Connections drop-down menu. -
Enter the following PL/SQL code in the SQL Worksheet tab:
DECLARE
id EMPLOYEES.EMPLOYEE_ID%type;
BEGIN
INSERT INTO EMPLOYEES (LAST_NAME, FIRST_NAME, EMAIL, HIRE_DATE, JOB_ID,
SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES (:LAST_NAME, :FIRST_NAME, :EMAIL, TO_DATE(:HIRE_DATE,'DD-MON-RR'), :JOB_ID,
:SALARY, :MANAGER_ID, :DEPARTMENT_ID)
RETURNING EMPLOYEE_ID INTO id;
:location := id;
:status := 201;
END;
Note: TheRETURNING EMPLOYEE_ID INTO id
returns the value assigned to theEMPLOYEE_ID
(by the trigger) into the variableid
. Theid
variable is defined in theDECLARE
section of the code, and theEMPLOYEES.EMPLOYEE_ID%type
syntax simply assigns the datatype for theEMPLOYEE_ID
column in theEMPLOYEES
table to theid
variable.
Note: The bind variable location is assigned theid
which contains the value of theEMPLOYEE_ID
. Thestatus
is assigned201
to indicate that the resource is created. You will have to define a specific parameter for thelocation
bind variable andstatus
bind variable. -
Select Parameters tab, and click + icon to add new parameter.
-
Enter the following values for the new parameter and click + icon again to add another parameter:
Name X-APEX-FORWARD Bind Parameter location
Access Method OUT
Source Type HTTP HEADER
Data Type STRING -
Enter the following values for the second parameter:
Name X-APEX-STATUS-CODE
Bind Parameter status
Access Method OUT
Source Type HTTP HEADER
Data Type INTEGER
- The X-APEX-FORWARD is a special parameter which is used to return a particular resource as part of the HTTP response to your RESTful Service.
- The X-APEX-STATUS-CODE is used to return the status of the service. The OUT indicates that the value will be returned in the HTTP response.
-
In the RESTful Services navigator, right-click demo and select Upload.
-
Click OK.
-
You are now ready to test the Restful Service. Provide following values in the WizTools.org RESTClient.
URL http://localhost:9090/ords/hr/demo/employees/ HTTP Method POST -
You want to specify that the body of this request is a JSON format using the character set UTF-8. Click Body tab and select String body from the drop-down list.
-
Click Edit Content-type & Charset icon.
-
Select application/json for Content-type and UTF-8 for Charset. Click Ok.
-
Type the body in JSON fomat using key/value pair as given below, and click Go:
{"LAST_NAME":"TESTINSERT", "FIRST_NAME":"TESTFIRST", "EMAIL":"TESTMAIL", "HIRE_DATE":"25-JUN-15", "JOB_ID":"IT_PROG", "SALARY":6000, "MANAGER_ID":103, "DEPARTMENT_ID":60}
Note:- The data is typed in the body using JSON format key/value pair, the key and value is separated by a colon. The key is enclosed in double quotes. The value for string is enclosed in double quotes and the numeric value is not enclosed in double quotes.
- LAST_NAME, FIRST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID and DEPARTMENT_ID will be mapped to the bind variables with the same names that you have specified in the POST handler PL/SQL code.
-
In the HTTP Response (lower section), click the body tab to view the results as:
{"employee_id":207,"first_name":"TESTFIRST","last_name":"TESTINSERT","email":"TESTMAIL","phone_number":null, "hire_date":"2015-06-25T07:00:00Z","job_id":"IT_PROG","salary":6000,"commission_pct":null,
Note:
"manager_id":103,"department_id":60}
- Your
employee_id
may have a different value if you run it multiple times. - By defining special parameter
X-APEX-FORWARD, the results are returned in a
JSON format. The X-APEX-FORWARD
parameter’s bind variable location contained
the
employee_id
value which will list the columns values for that row. - Since you have defined a special parameter
X-APEX-STATUS-CODE, the value is used in the
HTTP Response for
status
.
- Your
-
You can verify the row has been inserted using your browser. Enter http://localhost:9090/ords/hr/demo/employees/207 in the address bar and press enter. The record for
employee_id
, 207 is displayed. -
Similarly, you can verify the row has been inserted using your SQL Developer also. In the GET employees/:id editor, click the Run Statement icon on SQL Worksheet tab.
-
The Enter Binds dialog displays. Enter 207 for Value, and click Apply.
-
The Query Result tab displays the information for employee id, 207.
Updating Data Using RESTful Services
In this section, you will create a RESTful Service to update data using the HTTP Method PUT. You will use the RESTClient tool to test your RESTful Service and to verify the results.
-
In the RESTful Services navigator, right-click employees/:id, select Add Handler and then select PUT.
-
In the Mime Types, click the + icon to add a row to the Mime Types.
-
Enter application/json and click Apply.
-
In the RESTful Services navigator, the resource template employees/:id contains the resource handler PUT.
The resource handler editor PUT employees/:id is displayed on the right-side. Select hr from Connections drop-down menu. -
Enter the following PL/SQL code in the SQL Worksheet tab:
BEGIN
UPDATE EMPLOYEES SET LAST_NAME = :LAST_NAME, SALARY = :SALARY, JOB_ID = :JOB_ID,
DEPARTMENT_ID = :DEPARTMENT_ID
WHERE EMPLOYEE_ID = :id;
:location := :id;
:status := 200;
END;
-
Select Parameters tab, and click + icon to add new parameter.
-
Enter the following values for the new parameter and click + icon again to add another parameter:
Name X-APEX-FORWARD Bind Parameter location
Access Method OUT
Source Type HTTP HEADER
Data Type STRING -
Enter the following values for the second parameter:
Name X-APEX-STATUS-CODE
Bind Parameter status
Access Method OUT
Source Type HTTP HEADER
Data Type INTEGER
-
In the RESTful Services navigator, right-click demo and select Upload.
-
Click OK.
-
You are now ready to test the Restful Service. Provide following values in the WizTools.org RESTClient.
URL http://localhost:9090/ords/hr/demo/employees/207 HTTP Method PUT -
You want to specify that the body of this request is a JSON format using the character set UTF-8. Click Body tab and select String body from the drop-down list.
- Click Edit Content-type & Charset
icon.
-
Select application/json for Content-type, and click Ok.
-
Type the body in JSON fomat using key/value pair as given below, and click Go:
{"LAST_NAME":"TESTUPDATE", "JOB_ID":"SA_REP", "SALARY":8000, "DEPARTMENT_ID":80}
Note:LAST_NAME
,JOB_ID, SALARY and DEPARTMENT_ID
will be mapped to the bind variable with the same name that you have specified in the PUT Handler PL/SQL code. -
In the HTTP Response (lower section), click the body tab to view the results as:
Note:
{"employee_id":207,"first_name":"TESTFIRST","last_name":"TESTINSERT","email":"TESTMAIL","phone_number":null, "hire_date":"2015-06-25T07:00:00Z","job_id":"SA_REP","salary":8000,"commission_pct":null,
"manager_id":103,"department_id":80}- The Status returned is 200 since you have specified the X-APEX-STATUS-CODE which returned the status value. The Body displays the results of the updated row.
- Using X-APEX-FORWARD parameter which
contains the
employee_id
, returns the results for that row in JSON, consisting of its columns and values. - The columns’ values for
LAST_NAME, JOB_ID, SALARY
andDEPARTMENT_ID
are updated.
-
You can verify the row has been updated using your browser. Enter http://localhost:9090/ords/hr/demo/employees/207 in the address bar and press enter. The updated record for employee_id, 207 is displayed.
-
Similarly, you can verify the row has been inserted using your SQL Developer also. In the GET employees/:id editor, click the Run Statement icon on SQL Worksheet tab.
-
The Enter Binds dialog displays. Enter 207 for Value, and click Apply.
-
The Query Result tab displays the updated information for employee id, 207.
Deleting Data Using RESTful Services
In this section, you will create a RESTful Service to delete data using the HTTP Method DELETE. You will use the RESTClient tool to test your RESTful Service and to verify the results.
-
In the RESTful Services navigator, right-click locations/:id, select Add Handler and then select DELETE.
-
Click Apply.
-
In the RESTful Services navigator, the resource template locations/ contains the resource handler DELETE.
The resource handler editor DELETE locations/:id is displayed on the right-side. Select hr from Connections drop-down menu. -
Enter the following PL/SQL code in the SQL Worksheet tab:
BEGIN
DELETE FROM LOCATIONS WHERE location_id = :id;
:status := 204;
END; -
Select Parameters tab, and click + icon to add new parameter.
-
Enter the following values for the new parameter:
Name X-APEX-STATUS-CODE
Bind Parameter status
Access Method OUT
Source Type HTTP HEADER
Data Type INTEGER
-
In the RESTful Services navigator, right-click demo and select Upload...
-
Click OK.
-
You are now ready to test the Restful Service. Provide following values in the WizTools.org RESTClient.
URL http://localhost:9090/ords/hr/demo/locations/3000
Method DELETE
-
Click Body tab and select None from the drop-down list since nothing will be returned in the body.
-
Click Go icon.
-
In the HTTP Response (lower section), the Status returned is 204 No Content since you have specified the
X-APEX-STATUS-CODE which returned the status value indicating that the service fulfilled the delete request. -
Using your browser, you can verify the row has been deleted.. Enter http://localhost:9090/ords/hr/demo/locations/3000 in the address bar and press enter. A 404 Not Found is displayed.
-
Using SQL Developer, you can verify the row has been deleted.In the GET locations/:id editor, click the Run Statement icon on SQL Worksheet tab.
-
The Enter Binds dialog displays. Enter 3000 for Value, and click Apply.
-
The Query Result tab displays empty record.
Summary
Congratulations! You have completed Developing RESTful Services with Oracle SQL Developer series successfully.
Want to Learn More?
Credits
-
Curriculum Developer: Anjani Pothula
-
Other Contributors:Ashley Chen