Create RESTful API using Oracle Database [ORDS]
Oracle database is still the most powerful database in the market used by most of the big giants due to its scalability and performance. One of the most notable thing in Oracle database is that it is equipped with Pl/SQL that adds up an advantage to the database and it also makes flexible to compute with the data. It is because of the availability of Pl/SQL that Oracle is able to develop powerful web development platforms such as Application Express, the company likes to call “Apex”. In modern web development, REST APIs are often used to show up data on the front-end. Also, the modern databases now retrieve data from the database in the form of JSON. Oracle provides RESTful Data Services to the Oracle Database that helps you to retrieve data in the form of JSON.
I have setup Oracle 11g XE on my Virtual Box and I will show you how to create RESTful API using Oracle Database without server-side language. You do not need to write a separate NodeJS module to create REST APIs. With ORDS (Oracle RESTful Data Services), REST API is at your doorstep.
I have installed Oracle Linux 7.4 on Virtual Box. As a prerequisite, you should know how to install Oracle Database 11g XE that comes with Application Express 4.0. Keep them installed and on your host machine, make sure you have SQL Developer installed as we will be creating REST endpoints using this tool.
On the virtual machine where you have installed Oracle Database, navigate to Oracle REST Data Services website and download ORDS so you can find a ords.war file on the archive. Extract the archive to a new folder created on your home directory.
unzip ords.18.104.22.168.1251.zip -d ~/ords/
Now add a user with “SQL Developer” as a role. Let me name this user as ords_api.
sudo java -jar ./ords/ords.war user ords_api "SQL Developer"
Once you execute this command, if it will ask you for the root password, please do enter it. It also prompts you a path to store configuration details, namely the credentials. I recommend you to provide in the Oracle base directory.
Provide the following for configuration data:
Once you have provided the path for configutation data, it asks for a new password for the user ords_api. Provide a password and remember that you need to use everytime when you use it on SQL Developer.
Now we are done setting up a user for ORDS and we need to install ORDS to your database. Execute the below command to install.
sudo java -jar ./ords/ords.war install advanced
You will be asked several questions about the installation. The ORDS does not know where you have installed your database so, it asks for your database server, port, SYS user’s password. You will also be prompted to enter the password for ORDS_PUBLIC_USER. Enter a new password when it prompts. Let default and temporary tablespace names for ORDS_METADATA and ORDS _PUBLIC_USER shall be same.
Specify a password for APEX_PUBLIC_USER, APEX_LISTENER and APEX_REST_PUBLIC_USER. The installation will start installing ORDS.
After the installation, you will be prompted on how to start ORDS. Choose Standalone mode. It prompts for images directory of Oracle Application Express. But in our case, we are installing it on Oracle Database 11g XE and it does not have a static images folder, hence you shall provide a static folder location of Oracle Application Express or leave it empty.
Specify to use whether HTTP or HTTPS. In case of HTTPS, you need to provide the SSL Certificate.
Specify Port for ORDS – Oracle Application Express, by default, runs on port 8080. The ORDS’ default port is also 8080. Hence, specify a different port number, say 8081. Once you have provided the port, the ORDS starts running and you can connect to your database from SQL Developer and start creating REST APIs right from your Oracle Database.
Now navigate to http://<YOUR_VM_IP_ADDRESS>:8081/ords and you should be getting a 404 error page. This means that ORDS is running on your virtual machine. My Virtual Machine’s IP address is 172.20.10.5.
We will now see how to create REST endpoints using SQL Developer.
- Create a new connection to your Virtual Machine’s Oracle Database from SQL Developer.
- Once the connection is successful, right click on your connection name and go to REST Services > Enable REST Services.
Note that you will get this option only when ORDS is running properly on your Virtual Machine.
- Now it prompts for the schema to be used. As in our case, I have a schema called api_user already in my database and I am going to utilize them. I also have a table called, “local_storage” in it. I will show you making REST endpoints using this schema.
- Uncheck on Authorization Required and check on Enable Schema. Enter the schema name, in my case, it is api_user.
- Click on Next, and the Finish. The SQL Developer will execute an SQL command.
- Now head on to REST Development window. Right click on REST Data Services and click on Connect. In case, you do not have REST Development window, you can enable it by going to View > REST Data Services > Development.
It is now time to ORDS that is running on the port 8081. Edit the connection and provide appropriate details. Username will be the user that we created before installing ORDS. Enter the Virtual Machine’s IP address and 8081 (or whatever you have set to during installation) as ORDS port number.
- Connect and enter the password for the ords_api user. Once it gets connected, you will see that you would have got Modules under REST Data Services. Right-click on the Modules and click on New Module to create a new REST endpoint.
- Provide a URL Prefix and check on Publish.
- Provide a URL pattern. Here you can also provide variables such as :id so you can use :id in your SQL to execute queries. For example, if you want to fetch a row with id = 5, then you can execute the following query on a local_storage table.1SELECT * FROM local_storage WHERE id = :id;
Every time, you pass a number in the place of :id, the value gets replaced in the SQL Query and fetches rows for you.
- Choose an HTTP method in which you want to fetch data. You can also use POST in case you want to insert data to the database using ORDS. In this case, you can send data in JSON format and write an INSERT query to insert data into the database.
- Click on Next and then Finish to create a new module.
- Now double click or right click and Open on HTTP method of your newly created module to add some SQL Query.
- SQL Worksheet opens up for you to write queries.
- Let me quickly fetch all rows from the table local_storage and so:1SELECT * FROM local_storage;
- Now right click your module and choose Upload.
- Once your upload is successful, you can test it out.
- Navigate tot he URL for which you have created a REST endpoint on your favourite web browser. In my case, it is: http://172.20.10.5:8081/ords/api_user/api/get
- And, voila! we have created a successful REST endpoint.
Do not worry by considering this article’s length as setting up ORDS is only once and you just need to create new modules for setting up a REST endpoint URLs. Next time, when you want to run ORDS in standalone mode, you need to execute the following.
sudo java -jar ~/ords/ords.war standalone
It does not prompt you for any data and will start the ORDS using the settings you have provided during the installation. I believe this is the most convenient way to set up a REST endpoint instead of writing a NodeJS module, installing an NPM package to connect to Oracle database and executing queries. What is your opinion regarding this? If you have any queries, I am open to questions that you ask in the comments below.