Oracle Integration Cloud - Certificate Error

Recently, I was working on an POC where I had to integrate Database cloud service using Oracle Integration Cloud.

I was using using REST adapter where I was trying to connect Database using ORDS/REST.

On creating an Connection using OIC/ICS, I was getting the below error during Test Connection

CA SDK-0003 : Unable to parse the resource, https://IP. Verify that URL is reachable, can be parsed and credentials if required are accurate java.security.cert.CertificateException: No subject alternative names present No subject alternative names present

 Resolution.

On close examination, I saw the property subject alternative name not present in the certificate which I downloaded from the ORDS rest service.

To overcome this I created an self signed certificate and made sure that this property is created as part of the certificate. The next few commands was using on Oracle Database cloud cloud service machine.

cd /u01/app/oracle/product/ords/conf/ords/standalone
keytool -genkey -keyalg RSA -alias selfsigned -keystore keystore.jks -storepass password -validity 360 -keysize 2048 -ext san=ip:<ORDS_IP_Address>
keytool -importkeystore -srckeystore keystore.jks -destkeystore keystore.p12 -deststoretype PKCS12
openssl pkcs12 -in keystore.p12 -nokeys -out cert.pem
openssl pkcs12 -in keystore.p12  -nodes -nocerts -out key.pem
openssl pkcs8 -topk8 -inform PEM -outform DER -in key.pem -out server.key -nocrypt
keytool -export -alias selfsigned -keystore keystore.jks -rfc > server.cer


Once you run the above commands
modify standalone.properties file at /u01/app/oracle/product/ords/conf/ords/standalone

Edit the following lines:

ssl.cert=/u01/app/oracle/product/ords/conf/ords/standalone/server.crt
ssl.cert.key=/u01/app/oracle/product/ords/conf/ords/standalone/server.key

/etc/init.d/ords restart

Once done, upload your certificate into OIC store and you should be good to go.

Creating and Using RESTful Web Service

Oracle database gives an option to access its table using REST interfaces. This blog walks you thru the steps how one could configure it.

Before you configure the REST create a new schema. If you already have an schema, you can proceed with step #2

1. Create New Schema

[oracle@PersonifyDB opc]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 13 01:22:23 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> alter session set container = PDB1;

Session altered.

SQL> create user demo identified by password;

User created.

SQL> grant connect, resource to demo;

Grant succeeded.

SQL> grant unlimited tablespace to demo;

Grant succeeded.

sqlplus demo/password@//PersonifyDB.compute-596091264.oraclecloud.internal/pdb1.596091264.oraclecloud.internal
sqlplus demo/password@//host/fullyqualifiedpdbname


CREATE TABLE customers
( customer_id number(10) NOT NULL,
  customer_name varchar2(50) NOT NULL,
  city varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Table Created;

2.  In next few steps you would be creating an new workspace using apex

a. Log on to Apex

b. Create on "Create Workspace" from "Managed Workspace tab.

c.  Give the require details as per your project

d. Select the schema that you created in step#1. If you want to use existing schema for creating your REST project, search and select the schema in this step. Click Next. This will show you the summary of Workspace that is getting created.

e. Check REST Configuration

3. Logout of the admin work space and login to your new work space. Use the work space details completed in step#2

a. Select SQL Workshop

b. Select Restful Services

4. Create Restful Project or configuration. In this step, we would be creating an REST project and exposing 2 methods. The GET method would be used to fetch all the records from the table that we had created earlier. The post method will be used to create a new record in the table that we had created earlier.

a. Click on "Create a new RestFul Project". If you select "Install a sample restful", it would automatically create the rest project for the default HR schema.

b. Give a name to your project. select and URI Prefix. This prefix will be part of the base url. Make sure you end it with '/'. The below image does not have '/' appended. Think this as the base project name

c. Next give details about "Resource template".  Think this as the table name. Again end the URI template with /. For example 'customer/'

d. For this, we would be create 2 resource handlers. One for fetch and one for save. For Get select method as 'GET', format as JSON, Source type as Query and source as "Select * from customers" and click Save

e. Similarly create one for 'Post'. For this, click on 'Create Handler' from the left navigation

f. Select method as 'Post', Mime type as 'JSON' also select Require secure access as No 

Write a simple plsql that inserts record into the table.

begin

    insert into customers values (:id, :name, :city);

    :status := 200;

exception

    when others then

        :status := 400;

end;

Make a note of the pl sql variable. These variables will translate to as JSON schema parameters.

g. Click Pubish

5. Access REST using URL

6. Use post for sample post

The rest url generated: https://ip/ords/portabledb/workspace/baseuri/resourceuri

As your can see, just by going thru few steps, you can configure database access using REST.