REST and ORDS are available already a long time in the Oracle database.  Now I have the opportunity to dig deeper into it. In my current project, which involves a on-premises 12.1 multitenant database to 18c in the cloud. Including the Apex upgrade from 4.2 to the default apex 5 which comes with the 18c installation.

When the POC period is over, I might blog more over this project, but this is the POC phase in which we’ll see what is possible and not.

One thing the application must handle are POST requests over REST. I mentioned it earlier. I never played around with it until now.

The current apex 4.2 has no rest enabled yet. So that was one thing we had to do. The apex upgrade itself went very smooth apart from one minor issue which I’m currently investigating but is not important for the rest of the blog-post. As said, first thing we had to do is to enable REST in the pdb as it was not the case and it is mandatory in Apex 5.

Take into account, that you might run into issues with static files. The note which helped me was:Receive “There are Issues with the Configuration of The Static Files…” After Upgrading to APEX 5.0 (Doc ID 2004672.1)

All done? You would think so. When I ran

and I came at the option to configure ORDS, it threw to me

Either my friend google nor MOS came up with a decent solution. We are using an apache webserver which passes to a tomcat server with ords and then talks to the database. So even running it locally on the database side, threw this. But you can extract the setup scripts. This is what I did to find out what’s wrong, but they just ran fine.

Extracting the scripts from Ords is not difficult.

And it produces the scripts for you:

This directory I copied over to the database as a service installation and connected to the “problem pdb” as the sys user.

In the ords_manual_install.sql file there is listed how it can be used:

After this, it was pretty easy to use REST.
I created a test user for myself in that pdb and created a simple table.

On the app-server I have to configure the ORDS to map to this pdb:

this should never return any error. If it does, fix that first. Then you can create an url-mapping

At this point your url is now: http://your.appserver.yourdomain:port/vanpupi

In my case (I have to anonimize it, sorry for that) it is: http://testhost.ddns.net/ords/vanpupi/

As you can see, we cannot do a lot yet with that, so then connect to the user with the test table (in my case vanpupi) and execute following pl/sql blocks:

This should not return errors, if it says following error:

Then something is still wrong with your installation and it must be corrected first.

The parameters used are pretty self-explanatory. You can see that the p_url_mapping_pattern does not match my schema name. Often it will match, but it can be different as I chose here.

When you get pl/sql procedure completed successfully, you can move on and enable the test table for REST:

Again, the alias must not necessarily match with the real table name, so I changed it and the table will give you some nice json over rest on following url:

http://your.appserver.yourdomain:port/your-url-mapping-from-appserver/your-p_url_mapping_pattern/your-p_object_alias

in my example it is http://testhost.ddns.net/ords/vanpupi/vanpupirest/testtable/

in my database I have one record in the table:

And we want to see this using curl as well of course:

So it does return us the one record which is listed in the table.

POST requests are also not too difficult. I Created a small json file containing the fields of the table with the value

and we send that one using a curl post request to the database:

The proof of the pudding is the eating, so get it again:

and it is there in the database as well:

In the meantime, I am already a bit further and you can really play along nicely with templates/handlers/… and more of the cool stuff which is in the database. But this is a nice start to explore REST.

 

As always, questions, remarks? find me on twitter @vanpupi