Most applications today store data of some type, most likely that data is stored in a database. There are many ways to get data from the application to the database and back, but one of the most popular methods is using RESTful services. If you’re not familiar with REST think of it as an easy way to let 2 computers talk to each other. For a more detailed explanation check out this Wikipedia page.
If you are familiar with REST you’re probably used to standing up a server and building a server side application that connects to your database and provides a REST API.
Oracle provides a simpler solution called Oracle REST Data Services or ORDS for short. ORDS is a quick way to build a REST API directly to your database. If you’d like a more thorough explanation, check out the ORDS site.
A Short Tutorial
Setup a VM
I’ll be using the Developer Days vm on Virtual Box for the tutorial. This vm has the Oracle 12c Database and ORDS already installed and ready to go.
- Download the Database App Development VM. I’m using the one from June 13, 2017.
- Create a new appliance and start it.
- Inside the appliance, open a terminal and enter the following commands. Provide a password when prompted.
|
|
cd /home/oracle/ords/ java -jar ords.war user ords_dev "SQL Developer" |
Now we have the VM running and we’ve created an ORDS user “ords_dev”.
SQL Developer
For these examples, I’ll be using SQL Developer version 4.2.0.
If you don’t already have SQL Developer installed you can download it here.
Connect to the HR schema
Open SQL Developer and create a connection to the HR schema.
- Connection Name: Anything you’d like. I’m using Hr – VM
- Username: hr
- Password: oracle
- Hostname: localhost
- Port: 1521
- Service name: oracle
(Make sure you select the Service name radio button.)
Test the connection and connect.
Rest Enable The Schema
- Right click on the HR connection.
- Click REST Services.
- Click Enable REST Services…
- Enable schema: checked
- Schema alias: personnel
(Remember this for later.)
- Authorization required: un-checked
For production applications, you will want to use authorization but I’m not going to cover it here.
You can click Finish or if you’d like to see the summary page you can click Next then Finish.
REST Data Services Wizard
From here SQL Developer offers a couple different ways to run the REST Data Services wizard.
One way you can work with the wizard is through the database connection.
This method does not require you to have an ORDS user, but the full ORDS URI won’t be automatically provided in the wizard so you’ll need to get that from the ORDS admin. I’ll cover the URI below.
For this tutorial, I’ll be using the…
REST Development Panel
- Click the View menu item.
- Click REST Data Services.
- Click Development.
The REST Development panel (on the right) should now be in the left panel bar.
Connect to ORDS
- Click the Connect icon.
- Create a new connection.
- Populate the ORDS connection data.
This is an ORDS connection using the ORDS user we created in the VM earlier NOT the HR schema user.
Connection Name: HR-VM
Username: ords_dev
(The username is case sensitive.)
Select: http
Hostname: localhost
Port: 8080
Server Path: /ords
Schema/Workspace: /personnel
(If you used a different value when you rest enabled the schema use that value here ‘/your_alias’)
- Click OK in the New RESTful Services Connection panel.
- Select your new connection and click OK.
- Enter the password we created earlier: oracle
- Click OK.
New Module
A module is a collection of related REST services. How the services are related is up to your imagination. I usually think of a module like a package and the services as functions inside the package.
To create a new module:
- Right click on Modules.
- Click New Module…
The wizard will open and we can populate the data. The purpose of my module is to manage the personnel so I’m going to name my module Manage.
Module Name: Manage
URI Prefix: manage
Check the Publish check box.
Notice that when you enter the URI Prefix the Example URI is expanded to include that value. This is the URI I mentioned above. If you run the wizard through the database connection the URI will include a generic value for the first part that refers to the ORDS server. (http://localhost:8080/ords/personnel/)
Click Next.
Template URI
The template URI identifies a specific REST service endpoint. In this case employees. Notice that when you enter the URI Pattern the Example URI is expanded to also include that value.
Let’s break apart the URI. First, we have the schema alias ‘personnel’ that gives us access to the HR schema. Next, we created a module to ‘manage’ the HR schema records. Finally, we created a specific URI to handle transactions for ’employees’.
Method Handlers
Now that we’ve created the service endpoint to work with employees, we need to ‘Handle’ the different HTTP ‘Methods’ we intend to use.
A quick web search for ‘http rest methods’ will return pages of discussions on the available methods and how to “properly use them” but the short version is:
GET: Retrieve records with or without search criteria.
POST: Create records without providing the primary key.
PUT: Replace a record with a given primary key. This can also be used to create a record if you’ve pre-assigned it a primary key.
DELETE: Remove a record with a given primary key.
We’ll start by creating a simple GET all handler.
- Method: GET
- Source Type: Query
- Data Format: JSON
- Pagination Size: 25
We’ll leave this at the default value of 25. It’s a good idea to define a pagination size, we don’t want to accidentally return a billion records in one call. More on this later.
Click Next, review the summary and click Finish.
Get Query
Our GET method will return the Employee id, Hire Date, First and Last name for all employees.
If the GET employees SQL Worksheet did not automatically open, expand Manage, employees and click on GET.
Enter this query into the SQL Worksheet.
|
|
Select employee_id, first_name, last_name, hire_date from employees |
Push the new module to ORDS
- Right click on the Manage module.
- Click on Upload.
Post
To create new records we’ll want a handler for the POST method.
- Right click the employees URI template.
- Click Add Handler.
- Click Post.
Notice that GET is grayed out since you can only have one method handler of each type per URI template.
We use the MIME Types to define the data format that we’ll accept. Click the green plus to add a new MIME Type and enter application/json. Click Apply.
If the POST employees SQL Worksheet did not automatically open, expand Manage, employees and click on POST.
ORDS uses PL/SQL for methods that change data, POST, PUT and DELETE. PL/SQL gives us a greater amount of control which in turn provides better security.
Enter this PL/SQL into the SQL Worksheet.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
DECLARE new_id employees.employee_id%TYPE; BEGIN INSERT INTO employees ( first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ) VALUES ( :first_name, :last_name, :email, :phone_number, :hire_date, :job_id, :salary, :commission_pct, :manager_id, :department_id ) RETURNING employee_id INTO new_id; :new_id := new_id; :status := 201; END; |
Notice the use of bind variables in the PL/SQL. If the data keys coming into our REST service match our bind variables, ORDS will auto-map the values. However, if the keys do not match or we have additional use cases, we will need to map the bind variables using the Parameters tab. For this service, we will be passing in data values with keys that match the bind variables.
Since we are creating a new record and the primary key is auto-generated, it will be useful to the end user if we return the new id. Above, we’ve defined a new bind variable :newid to pass this value back. There is also another bind variable :status that we’ll use to change the response status from 200 (success) to 201 (success and I created a new record).
Parameters
Click on the Parameters tab and enter the following values.
Colum definitions:
- Name – Used by ORDS.
- newid will be the key in the JSON object that returns the id to the user.
- X-APEX-STATUS-CODE is a built in ORDS parameter used to set the status of the response object.
- Bind Parameter – The bind variable used in our PL/SQL.
- Access Method – Defines the direction in the transaction we intend to use the parameters; IN, OUT or IN/OUT.
- Source Type is where the parameter will be used.
- newid will be in the response body.
- X-APEX-STATUS-CODE will be in the response header.
- Data Type – Data type for the returned value. When all else fails, choose STRING.
Push the modified module to ORDS
- Right click on the Manage module.
- Click on Upload.
At this point, we have created and deployed a fully functional REST API with the ability to GET all employees and POST a new employee.
It’s time to….
Test the Service
Switch to the Details tab for either the GET or the POST method handler. At the bottom, you can copy the URI for the new REST service.
URI: http://localhost:8080/ords/personnel/manage/employees
GET
To test the GET method you could simply enter the URI into a web browser and it will return the records. Using my test tool, I enter the URI and hit send.
I receive back a JSON object with an “items” array that has 25 employee entries in it. Below, I’ve trimmed a few out of the middle to keep it short.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
{ "items": [ { "employee_id": 100, "first_name": "Steven", "last_name": "King", "hire_date": "1987-06-17T04:00:00Z" }, { "employee_id": 101, "first_name": "Neena", "last_name": "Kochhar", "hire_date": "1989-09-21T04:00:00Z" }, { "employee_id": 123, "first_name": "Shanta", "last_name": "Vollman", "hire_date": "1997-10-10T04:00:00Z" }, { "employee_id": 124, "first_name": "Kevin", "last_name": "Mourgos", "hire_date": "1999-11-16T05:00:00Z" } ], "first": { "$ref": "http://localhost:8080/ords/personnel/manage/employees" }, "next": { "$ref": "http://localhost:8080/ords/personnel/manage/employees?page=1" } } |
Remember, I set the Pagination Size to 25 in the GET method, so ORDS returns the first 25 records. Notice at the bottom of the JSON object after the array there is a “first” object. The “$ref” value will take you to the first page of records. This is automatically added to the response by ORDS when pagination is enabled.
There is also a “next” object added by ORDS to indicate that there are more records on the server. When you write your client side application, you would process the returned records and check to see if there is a “next” object. If there is, you could use URI in the “$ref” object to fetch the next set of records. You would loop through this process until the last set of records. When you reach the last set there will not be a “next” object.
After the first page, you would start to see a “prev” object containing a “$ref” object that you can use to reverse through the records.
|
|
"prev": { "$ref": "http://localhost:8080/ords/personnel/manage/employees" } |
If you set Pagination Size to 0 the service will return every record at once and the navigation objects will not be included.
POST
In your REST testing tool:
- Change the method to POST.
- Add a header.
Content-Type: application/json
- Enter the following as the payload.
|
|
{ "first_name": "Blaine", "last_name": "Carter", "email": "blaine.carter", "phone_number": "555.867.5309", "hire_date": "2015-04-01T04:00:00Z", "job_id": "IT_PROG", "salary": 1, "commission_pct": null, "manager_id": 120, "department_id": 60 } |
You should receive a response with a status of “201 Created” and the response body should contain the newly generated id.
Our service is deployed and the tests return the data we expect.
The wizards are a great way to quickly define REST services for your database, but you won’t want to use them when you deploy your application. Instead, we can…
Export SQL
For mass deployment (or for people who just prefer to type everything) a SQL script is a better option.
Another difference between the REST Development panel and REST Data Services in the database connection is that you can export the SQL using the database connection tool.
Open the HR database connection and expand the REST Data Services item. If you do not see your new service, click on the REST Data Services item and click the refresh arrows at the top of the panel.
- Expand Modules.
- Right click on Manage.
- Select Export…
In the window that pops up:
- Check the Enable Schema check box if you want to include the statement.
- Un-Check Privileges.
- Enter a filename and location.
- Click Apply.
- Open the file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
-- Generated by Oracle SQL Developer REST Data Services 4.2.0.17.089.1709 -- Exported REST Definitions from ORDS Schema Version 3.0.9.348.07.16 -- Schema: HR Date: Wed Aug 09 15:29:04 MDT 2017 -- BEGIN ORDS.ENABLE_SCHEMA( p_enabled => TRUE, p_schema => 'HR', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'personnel', p_auto_rest_auth => FALSE); ORDS.DEFINE_MODULE( p_module_name => 'Manage', p_base_path => '/manage/', p_items_per_page => 25, p_status => 'PUBLISHED', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'Manage', p_pattern => 'employees', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'Manage', p_pattern => 'employees', p_method => 'POST', p_source_type => 'plsql/block', p_items_per_page => 0, p_mimes_allowed => 'application/json', p_comments => NULL, p_source => 'DECLARE new_id employees.employee_id%TYPE; BEGIN INSERT INTO employees ( first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ) VALUES ( :first_name, :last_name, :email, :phone_number, :hire_date, :job_id, :salary, :commission_pct, :manager_id, :department_id ) RETURNING employee_id INTO new_id; :new_id := new_id; :status := 201; END;' ); ORDS.DEFINE_PARAMETER( p_module_name => 'Manage', p_pattern => 'employees', p_method => 'POST', p_name => 'X-APEX-STATUS-CODE', p_bind_variable_name => 'status', p_source_type => 'HEADER', p_param_type => 'INT', p_access_method => 'OUT', p_comments => NULL); ORDS.DEFINE_PARAMETER( p_module_name => 'Manage', p_pattern => 'employees', p_method => 'POST', p_name => 'newid', p_bind_variable_name => 'new_id', p_source_type => 'URI', p_param_type => 'INT', p_access_method => 'OUT', p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'Manage', p_pattern => 'employees', p_method => 'GET', p_source_type => 'json/query', p_items_per_page => 25, p_mimes_allowed => '', p_comments => NULL, p_source => 'Select * from employees' ); COMMIT; END; |
You can now include this SQL script in your application build process to deploy the REST services right alongside the rest of your database objects.
When you need a REST API to work with your database, ORDS and the SQL Developer wizards will save you a ton of time and help you create very robust and elegant solutions.
Please leave me a comment if you have trouble or find any bugs.
You must be logged in to post a comment.