Break New Ground

Oracle Cloud Inception and the REST

REST services are everywhere, and
almost every button click is a GET or a POST somewhere.
It is not a new feature that the Oracle
Database can consume REST services in PL/SQL and you could find this on many

Also not new is the fact that you
could access the Oracle Public Cloud using REST services. There is a very good
documentation on this here:


And here
comes the question, if you could manage the cloud from within the cloud using
REST services. So let’s put the two on two together and create an Oracle Cloud

Just like following a recipe, the prerequisites would be:

·  Oracle Public Cloud credentials:

o identity

o username

o password

· Oracle Database Cloud Service activated 

· SSh connection and tunnel to the cloud instance. 

· Sql Developer connection to the pluggable database.

The plan for this exercise is:

1. Get
the Certificate chain that authorizes https://dbcs.emea.oraclecloud.com/

2. Create
a wallet to import the certificates.

3. Create
a PL/SQL procedure to call Rest services

4. Let
the fun begin

On the first part,
open Firefox and go to https://dbcs.emea.oraclecloud.com/

Click on the small little lock at the beginning of the URL:

This should show information about the site and about the
authority that certificates it. Click on More Information button:

This opens the Security page on the Firefox Options. Click
on View Certificate:

You can see the public key of the dbcs.emea.oraclecloud.com. Click on Details tab to see the
certificate chain:

Observe that VeriSign is the root certificate authority that
authorizes Symantec and this on forward authorizes emea.oraclecloud.com.

Export the Symantec certificate chain and chose Save as type:
X.509 Certificate with chain
. Also chose a location and a file name suitable for you.

To import the above certificate
connect to the Database Cloud Instance using a ssh client like putty.

First, create a wallet directory.

[oracle@Db12c ~]$
mkdir wallet

Create a wallet using the orapki tool:

[oracle@Db12c ~]$ orapki
wallet create -wallet /home/oracle/wallet -pwd Welcome#1

Oracle PKI Tool :

Copyright (c) 2004,
2014, Oracle and/or its affiliates. All rights reserved.

[oracle@Db12c ~]$

Copy the SymantecClass3SecureServerCA-G4.crt
file saved earlier in the wallet location: /home/oracle/wallet.

After that you should add the certificates using the same
orapki tool. Issue the following:

orapki wallet add
-wallet /home/oracle/wallet -trusted_cert -cert
SymantecClass3SecureServerCA-G4.crt -pwd Welcome#1

Now you have the wallet location: /home/oracle/wallet to use in a pl/sql procedure.

Open the Sql Developer and connect to a pluggable database
from the Oracle Database Cloud Service. To verify the network Access Control List (ACL) for the
current user, which in our case is SYS run the following query.

SELECT * FROM dba_network_acls;

You can see that SYS is the ACL_OWNER for the NETWORK_ACL so
you could use SYS as the procedure owner.

In case you want to use another user you should give him
privileges to the above ACL or create a new ACL. To give a user privileges on the network ACL
run the following:


DBMS_NETWORK_ACL_ADMIN.add_privilege (

acl => 'NETWORK_ACL_FD9ACFECC55448A9E043B6A9E80AFB6F', --the ACL name

principal => 'CLOUD_ADMIN', --The database user name

is_grant => true,

privilege => 'resolve',

position => NULL,

start_date => NULL,

end_date => NULL);




DBMS_NETWORK_ACL_ADMIN.add_privilege (

acl => 'NETWORK_ACL_FD9ACFECC55448A9E043B6A9E80AFB6F', --the ACL name

principal => 'CLOUD_ADMIN', --The database user name

is_grant => true,

privilege => 'connect',

position => NULL,

start_date => NULL,

end_date => NULL);



The procedure below calls the REST endpoint with GET,
POST and PUT methods and displays the results. The procedure takes the following parameters:

· p_identitydomain
The identity domain

· p_instancename -- The cloud instance name

· p_method default 'GET' --The method called, by default is

· p_content – The content for a POST or PUT

· p_user –The username for Oracle Public
Cloud connection

· p_pass –The password for Oracle Public
Cloud connection

The end point URL used is: https://dbcs.emea.oraclecloud.com/paas/service/dbcs/api/v1.1/instances/'||p_identitydomain||'/'||p_instancename; It has the Identity Domain and Instance Name concatenated
at the end.

create or replace
procedure database_service_call

( p_identitydomain
in varchar2,

p_instancename in varchar2,

p_method in varchar2 default 'GET',

p_content in varchar2 default null,

p_user in varchar2,

p_pass in varchar2,

) is

req utl_http.req;

res utl_http.resp;

v_url varchar2(4000) :=

v_buffer varchar2(4000);


--Setting the wallet that we created earlier

UTL_HTTP.set_wallet('file:/home/oracle/wallet', 'Welcome#1');

--Sending the request

req := utl_http.begin_request(v_url,

UTL_HTTP.set_authentication(req, p_user,

utl_http.set_header(req, 'content-type',

utl_http.set_header(req, 'Content-Length',

utl_http.set_header(req, 'X-ID-TENANT-NAME',

utl_http.write_text(req, p_content);

res := utl_http.get_response(req);

DBMS_OUTPUT.PUT_LINE('HTTP response status
code: ' || res.status_code);

the response



utl_http.read_line(res, v_buffer);


end loop;



when utl_http.end_of_body





 The list of endpoints and methods could be found here:


To call it run the following pl/sql block:

SET serveroutput on



p_instancename=> 'Db12c' ,

p_method =>'GET',

p_content => null,

p_user =>'cloud.admin',

p_pass => 'My_pass#1'



The first output is that the procedure ran successfully.
Then the second line gives us the 200 code which is 200 OK which means that the
request was successfully completed. A 200 status is returned for a
successful GET or POST method.

The entire response printed is a JSON like this:


"service_name": "Db12c",

"version": "",

"status": "Running",

"description": "Test


"creation_time": "Tue May 17
8:46:9 UTC 2016",

"last_modified_time": "Tue May
17 8:46:9 UTC 2016",


"num_nodes": 1,

"level": "PAAS",

"edition": "EE",

"shape": "oc4",

"failover_database": false,

"rac_database": false,

"sid": "ORCL",

"pdbName": "PDB1",

"listenerPort": 1521,






"charset": "AL32UTF8",

"ncharset": "AL16UTF16",



One of the features of the Oracle
Database is that it can store a JSON object in a single column and has the
ability to parse it and to show the results in a query. So let’s think how
these two could be used and create a report with the status of the instance in

The fun part is that you can
control the Oracle Database Cloud Service instance from within the instance. In
other words you could give more RAM and CPU to the instance from within the

You could try this procedure call:

SET serveroutput on



p_instancename=> 'Db12c' ,

p_method =>'PUT',

p_content => '{ "shape" :
"oc5" }',

p_user =>'cloud.admin',

p_pass => 'My_pass#1'



Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.