Overview
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 blogs.
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:
https://docs.oracle.com/cloud/latest/
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 Inception.
Just like following a recipe, the prerequisites would be:
· Oracle Public Cloud credentials:
o identity domain
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
Get the Certificate chain
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 (PEM)(*.crt,*.pem). Also chose a location and a file name suitable for you.
Create a wallet and import the certificate
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 : Version 12.1.0.2
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.
Create a PL/SQL procedure to call Rest services.
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:
BEGIN
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);
COMMIT;
END;
BEGIN
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);
COMMIT;
END;
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 GET
· p_content – The content for a POST or PUT method
· 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) := 'https://dbcs.emea.oraclecloud.com/paas/service/dbcs/api/v1.1/instances/'||p_identitydomain||'/'||p_instancename;
v_buffer varchar2(4000);
begin
--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, p_method);
UTL_HTTP.set_authentication(req, p_user, p_pass);
utl_http.set_header(req, 'content-type', 'application/json');
utl_http.set_header(req, 'Content-Length', length(p_content));
utl_http.set_header(req, 'X-ID-TENANT-NAME', p_identitydomain);
utl_http.write_text(req, p_content);
res := utl_http.get_response(req);
DBMS_OUTPUT.PUT_LINE('HTTP response status code: ' || res.status_code);
--Display the response
begin
loop
utl_http.read_line(res, v_buffer);
dbms_output.put_line(v_buffer);
end loop;
utl_http.end_response(res);
exception
when utl_http.end_of_body
then
utl_http.end_response(res);
end;
end database_service_call;
--The list of endpoints and methods could be found here:
http://docs.oracle.com/cloud/latest/dbcs_dbaas/CSDBR/toc.htm
--To call it run the following pl/sql block:
SET serveroutput on
begin
database_service_call(p_identitydomain=> 'myIdentityDomain',
p_instancename=> 'Db12c' ,
p_method =>'GET',
p_content => null,
p_user =>'cloud.admin',
p_pass => 'My_pass#1'
);
end;
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": "12.1.0.2",
"status": "Running",
"description": "Test Db12c",
"identity_domain": "myIdentityDomain",
"creation_time": "Tue May 17 8:46:9 UTC 2016",
"last_modified_time": "Tue May 17 8:46:9 UTC 2016",
"service_uri": "https:\/\/dbcs.emea.oraclecloud.com:443\/paas\/service\/dbcs\/api\/v1.1\/instances\/myIdentityDomain\/Db12c",
"num_nodes": 1,
"level": "PAAS",
"edition": "EE",
"shape": "oc4",
"failover_database": false,
"rac_database": false,
"sid": "ORCL",
"pdbName": "PDB1",
"listenerPort": 1521,
"em_url": "https:\/\/140.86.2.XXX:5500\/em",
"connect_descriptor": "Db12c:1521\/PDB1.myIdentityDomain.oraclecloud.internal",
"connect_descriptor_with_public_ip": "140.86.2.XXX:1521\/PDB1.myIdentityDomain.oraclecloud.internal",
"apex_url": "https:\/\/140.86.2.XXX\/apex\/pdb1\/",
"dbaasmonitor_url": "https:\/\/140.86.2.XXX\/dbaas_monitor",
"charset": "AL32UTF8",
"ncharset": "AL16UTF16",
"compute_site_name": "EM002_Z12",
}
Let the fun begin
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 time.
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 instance.
You could try this procedure call:
SET serveroutput on
begin
database_service_call(p_identitydomain=> 'myIdentityDomain',
p_instancename=> 'Db12c' ,
p_method =>'PUT',
p_content => '{ "shape" : "oc5" }',
p_user =>'cloud.admin',
p_pass => 'My_pass#1'
);
End;
The views expressed in this post are my own and do not necessarily reflect the views of Oracle.