My first REST adventures
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
|
1 |
java -jar ords.war setup --database testpdb |
and I came at the option to configure ORDS, it threw to me
|
1 |
ords ORA-24964: ALTER SESSION SET CONTAINER error |
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.
|
1 2 3 4 5 6 7 8 |
[root@testhost webapps]# pwd /var/lib/tomcat/webapps [root@testhost webapps]# [root@testhost webapps]# java -jar ords.war ords-scripts --scriptdir /tmp/ordsscripts IMPORTANT NOTICE: The ords-scripts command is deprecated. Use the install, validate or uninstall command. Completed extracting Oracle REST Data Services scripts. Files are located at /tmp/ordsscripts/ [root@testhost webapps]# |
And it produces the scripts for you:
|
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 |
[root@testhost webapps]# tree -L 3 /tmp/ordsscripts/ /tmp/ordsscripts/ └── scripts ├── images │ ├── oracle.ico │ └── oracle_logo.gif ├── install │ ├── core │ ├── datamodel │ └── install_order.xml ├── migrate │ ├── core │ └── migrate_order.xml ├── params │ └── ords_params.properties ├── uninstall │ ├── core │ └── uninstall_order.xml ├── upgrade │ ├── 3.0.0.117 │ ├── 3.0.1.135 │ ├── 3.0.2.223 │ ├── 3.0.3.349 │ ├── 3.0.4.15 │ ├── 3.0.4.19 │ ├── 3.0.4.25 │ ├── 3.0.4.4 │ ├── 3.0.4.41 │ ├── 3.0.4.54 │ ├── 3.0.5.78 │ ├── 3.0.5.89 │ ├── 3.0.6.131 │ ├── 3.0.6.132 │ ├── 3.0.6.133 │ ├── 3.0.6.137 │ ├── 3.0.6.141 │ ├── 3.0.6.159 │ ├── 3.0.6.166 │ ├── 3.0.6.168 │ ├── ords_completed_upgrade.sql │ ├── ords_init_upgrade.sql │ ├── ords_manual_upgrade.sql │ ├── ords_no_upgrade.sql │ ├── ords_schema_version.sql │ ├── ords_upgrade_300_117.sql │ ├── ords_upgrade_301_135.sql │ ├── ords_upgrade_302_223.sql │ ├── ords_upgrade_303_349.sql │ ├── ords_upgrade_304_15.sql │ ├── ords_upgrade_304_19.sql │ ├── ords_upgrade_304_25.sql │ ├── ords_upgrade_304_41.sql │ ├── ords_upgrade_304_4.sql │ ├── ords_upgrade_304_54.sql │ ├── ords_upgrade_305_78.sql │ ├── ords_upgrade_305_89.sql │ ├── ords_upgrade_306_131.sql │ ├── ords_upgrade_306_132.sql │ ├── ords_upgrade_306_133.sql │ ├── ords_upgrade_306_137.sql │ ├── ords_upgrade_306_141.sql │ ├── ords_upgrade_306_159.sql │ ├── ords_upgrade_306_166.sql │ ├── ords_upgrade_306_168.sql │ ├── ords_upgrade_cdb.sql │ ├── ords_upgrade.sql │ └── upgrade_versions.xml └── validate ├── core └── validate_order.xml 33 directories, 35 files [root@testhost webapps]# |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
... Rem Arguments: Rem 1 : Log folder (must include the forward slash at the end) Rem 2 : Default tablespace for ORDS_METADATA Rem 3 : Temporary tablespace for ORDS_METADATA Rem 4 : Default tablespace for ORDS_PUBLIC_USER Rem 5 : Temporary tablespace for ORDS_PUBLIC_USER Rem Rem Example: Rem sqlplus "sys as sysdba" @ords_manual_install /logs/ SYSAUX TEMP SYSAUX TEMP ... |
After this, it was pretty easy to use REST.
I created a test user for myself in that pdb and created a simple table.
|
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 |
SQL*Plus: Release 18.0.0.0.0 Production on Mon May 7 12:45:32 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2017, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 18c EE High Perf Release 18.0.0.0.0 - Production Version 18.1.0.0.0 SQL> show con_name CON_NAME ------------------------------ PDBTEST SQL> show user USER is "VANPUPI" SQL> create table test(id number); Table created. SQL> insert into test (id ) values (1); 1 row created. SQL> commit; Commit complete. SQL> |
On the app-server I have to configure the ORDS to map to this pdb:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[root@testhost webapps]# java -jar ords.war setup --database testpdb Enter the name of the database server [*********************.oraclecloud.internal]: Enter the database listen port [1521]: Enter 1 to specify the database service name, or 2 to specify the database SID [1]: Enter the database service name [***********.oraclecloud.internal]: Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]: Enter the database password for ORDS_PUBLIC_USER: Confirm password: Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step. If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]: Enter the PL/SQL Gateway database user name [apex_public_user]: Enter the database password for apex_public_user: Confirm password: Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]: Enter the database password for APEX_LISTENER: Confirm password: Enter the database password for APEX_REST_PUBLIC_USER: Confirm password: May 07, 2018 11:36:15 AM oracle.dbtools.rt.config.setup.SchemaSetup install INFO: Oracle REST Data Services schema version 3.0.9.348.07.16 is installed. [root@testhost webapps]# |
this should never return any error. If it does, fix that first. Then you can create an url-mapping
|
1 |
java -jar ords.war map-url --type base-path /vanpupi testpdb |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
BEGIN ORDS.enable_schema( p_enabled => TRUE, p_schema => 'VANPUPI', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'vanpupirest', p_auto_rest_auth => TRUE ); COMMIT; END; / |
This should not return errors, if it says following error:
|
1 |
pls-00201: identifier 'ords.enable_schema' must be declared |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
BEGIN ORDS.enable_object ( p_enabled => TRUE, p_schema => 'VANPUPI', p_object => 'TEST', p_object_type => 'TABLE', p_object_alias => 'testtable' ); COMMIT; END; / |
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:
|
1 2 3 4 5 6 7 |
SQL> select * from test; ID ---------- 1 SQL> |
And we want to see this using curl as well of course:
|
1 2 3 4 5 6 7 |
mbp-vanpupi:~ pieter$ curl http://testhost.ddns.net/ords/vanpupi/vanpupirest/testtable/ |jq '.items[]'.id % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 553 0 553 0 0 229 0 --:--:-- 0:00:02 --:--:-- 229 1 mbp-vanpupi:~ pieter$ |
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
|
1 2 3 |
mbp-vanpupi:~ pieter$ cat resttest.json { "id": 999 } mbp-vanpupi:~ pieter$ |
and we send that one using a curl post request to the database:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mbp-vanpupi:~ pieter$ curl -i -X POST --data-binary @resttest.json -H "Content-Type: application/json" http://testhost.ddns.net/ords/vanpupi/vanpupirest/testtable/ HTTP/1.1 201 Created Date: Mon, 07 May 2018 11:54:49 GMT Server: Apache/2.4.6 () OpenSSL/1.0.2k-fips Content-Location: http://testhost.ddns.net/ords/vanpupi/vanpupirest/testtable/AA******JAAB ETag: "XPwu1vQEYvxBv***********************DmE9pOAHnm7EGMHp16LQ==" Location: http://testhost.ddns.net/ords/vanpupi/vanpupirest/testtable/AA********JAAB Content-Type: application/json Transfer-Encoding: chunked {"id":999,"links":[{"rel":"self","href":"http://testhost.ddns.net/ords/vanpupi/vanpupirest/testtable/AA********JAAB"},{"rel":"edit","href":"http://testhost.ddns.net/ords/vanpupi/vanpupirest/testtable/AA*******JAAB"},{"rel":"describedby","href":"http://testhost.ddns.net/ords/vanpupi/vanpupirest/metadata-catalog/testtable/item"},{"rel":"collection","href":"http://testhost.ddns.net/ords/vanpupi/vanpupirest/testtable/"}]} mbp-vanpupi:~ pieter$ |
The proof of the pudding is the eating, so get it again:
|
1 2 3 4 5 6 7 |
mbp-vanpupi:~ pieter$ curl http://testhost.ddns.net/ords/vanpupi/vanpupirest/testtable/ |jq '.items[]'.id % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 678 0 678 0 0 287 0 --:--:-- 0:00:02 --:--:-- 287 1 999 mbp-vanpupi:~ pieter$ |
and it is there in the database as well:
|
1 2 3 4 5 6 7 8 |
SQL> select * from test; ID ---------- 1 999 SQL> |
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