Hello again. Today we will show how you can use KeepTool 16 in conjunction with Oracle REST data services (ORDS).
REST is an acronym for Representational State Transfer. It is a stateless, cacheable, client/server communication protocol, mostly based on HTTP(S). ORDS is highly scalable. It uses HTTP(S) methods such as POST, PUT, GET, DELETE to implement CRUD operations (Create, Read, Update, Delete).
ORDS is Oracle’ API that allows you to expose your data as RESTful data services. It is a mid-tier Java application that maps HTTP(S) verbs (GET, PUT, POST, DELETE, …) to database transactions. Results are returned as formatted JSON.
ORDS mostly is used for
An ORDS URL uses a syntax as shown in the following example:
http(s)://hostname:8080/ords/orcl/hr/module/template/:id
It consists of the following elements:
Explore possibilities of KeepTool.
You can download the latest ORDS version from https://download.oracle.com/otn_software/java/ords/ords-latest.zip .
All installation and configuration steps are described in the chapter Installing and Configuring Oracle REST Data Services of the Oracle REST Data Services Installation and Configuration Guide.
In the params directory of your ORDS home you can find a file
ords_params.properties
that allows you to configure parameters prior to installing ORDS. For our demo, we have used the default settings.
ORDS can be deployed on WebLogic, Tomcat or run in standalone mode. This article describes the installation in standalone mode.
Within the installation process, you are prompted to define an ORDS administration user. It depends on your own if you create a new user or use an existing user such as PDBADMIN.
Besides the ORDS application, the installer creates two new database_schemas ORDS_METADATA and ORDS_PUBLIC_USER. The PL/SQL objects, views and tables of the schema ORDS_METADATA provide the logic to setup ORDS. Hora’s new ORDS dialogs build a user interface on top of this.
Make sure, the ORDS\bin directory has been added to the PATH variable. Then you can run the ORDS server by executing
ords --config ${ORDS_CONFIG} serve
You can find Hora’s ORDS page in the “Additional” group. First of all we want to focus on the data grid “ORDS Schema”. You find it in the bottom of the page. The schema grid is visible for most ORDS tabs.
For an ORDS administrator, it allows you to manage ORDS schemas as well as navigate between multiple ORDS schemas.
For a regular user, the data grid shows the current schema only.
Both checkboxes in the right of the ORDS schema header show you whether your current session has ORDS_ADMINISTRATOR_ROLE or ORDS_RUNTIME_ROLE privileges, respectively.
For working with ORDS, a database schema must be enabled for ORDS. Make sure we are connected as a ORDS administrator user, which is PDBADMIN in our case. Right-click the ORDS Schema grid and select the “Enable Schema” menu item. This opens a modal dialog to enable a new schema.
We select the HR demo schema (human resources database) from the combo box and use the default URI mapping pattern that is a lowercase copy of the schema name. After confirming with OK, the HR schema appears in the data grid.
Because we are connected as ORDS admin user, we can see multiple schemas in the data grid. We can navigate to another schema, and all other data grids such as ORDS modules display ORDS objects that belong to the selected schema. If you want to manage ORDS objects, we strictly recommend to connect as the appropriate ORDS user.
We are re-connected now as HR as you can see in the from caption below. BTW, we included the ORDS / REST button in the “favorites” section of the main menu for faster access.
The ORDS schema grid in the bottom shows exactly one record for HR, because HR has been enabled for ORDS and we are no ORDS administrator user any more.
Initially, the ORDS modules grid is empty. Right-click and choose the “Create module” menu item. That opens a modal dialog to create a new module:
Choose a module name and an URI base path. The latter is also called URI prefix and should include both leading and trailing slashes.
Two samples below the text box shows you how the module URI would look like when using either HTTPS or HTTP, assuming it has been published on localhost.
Now the data grid lists the hr_demo module:
Now we change to the next tab. Initially, the data grid does not show templates for our module.
Right-click and choose the “Create template” menu item.
Now a modal dialog comes up. It shows hr_demo as the module we want a new template to be added.
Enter a URI pattern for the template. We chose “emp” because we want to process data of the HR.EMPLOYEES table.
We keep all inputs at its default besides the comment “Employees demo template”.
After clicking OK, the pattern is shown in the data grid.
Below the grid there are two sample URIs for HTTPS and HTTP, assuming ORDS has been installed on localhost.
Please beware of our web service is not yet complete. It consists of
The next step will be creating a handler.
Now we change again to the next tab. Initially, the data grid does not show handlers for our module.
Right-click and choose the “Create Handler” menu item.
Now a modal dialog comes up. It shows hr_demo as the module we want a new handler to be added.
Choose the “emp/” URI pattern from the combo box. That selects the template we created in the previous step.
Select the “GET” method and JSON format for both source and result.
In the code editor we put a Select statement. It queries data from the EMPLOYEES table.
The where condition allows two optional parameters to filter by EMPLOYEE_ID or SALERY.
When no parameter values are bound, the condition doesn’t filter the appropriate column.
Once the handler has been created, we must define both SQL parameters and assign URI parameters for them.
Therefore right-click the parameters grid. You find it right below the handlers grid. Choose the “define parameter” menu item.
This opens the following modal dialog.
Start with defining the “ID” bind variable and complete as you see in the hardcopy. Repeat this for the “SAL” variable.
Now the handlers page looks like that:
A red arrow in the hardcopy points to two sample URIs that have been created automatically, assuming ORDS has been published on localhost. The left URI is valid if you are using HTTPS. The other URI works with HTTP that you are using in a development environment, maybe.
Now we are ready to run the Webservice. Open a Windows command prompt and run
ords serve
That starts the ORDS server.
When the server is running, you can click one of the sample URLs. That copies the URL in your browser. For our demo we keep it simple and use HTTP. As you can see, the browser shows a JSON response that includes all data from the EMPLOYEES table.
Now let us supply parameters. Separated by a question tag, we add an “id” parameter value to the URL
As you can see, the JSON result is a single EMPLOYEE structure and a couple of additional HTTP references. As an alternative, you can query by salary by adding an appropriate “sal” parameter clause.
The result is all employees earning a salary beyond 10K.
Now our ORDS-based Webservice is working. The sample shows, how you can use Hora to configure and test ORDS Webservices. Based on this blueprint, you can create your own more complex Webservices.
In our next blog post we will describe another new feature introduces in KeepTool 16. Stay tuned with us.
