iPaaS@ORACLE.CLOUD: #1097 - Controlling use of OIC Connections

Thursday, October 23, 2025

#1097 - Controlling use of OIC Connections

As Lenin was wont to say - trust is good, control is better. So how does this relate to OIC connections? Well some customers don't want certain adapters to be used, e.g. the GMAIL adapter. Nothing against gmail, it's just a simple example to use. Also, what about connections based on the REST adapter, where are they pointing to?

If you need such, then read on; if not then you have my permission, to stop reading and do something more interesting! In this I create an integration that will invoke the OIC Factory api to get connection data. I will then check for use of "banned" adapters and then invoke a pl/sql function to check base urls.

To begin with, I create the following tables in ATP

create table invalid_connections(checkDate TIMESTAMP 
not null, project varchar2(50) not null, 
connectionType varchar2(20) not null, 
message varchar2(100) not null);

create table invalid_adapters(id varchar2(20) not null, 
name varchar2(20) not null);

Insert into invalid_adapters values ('GMAIL', 'gmail');

create table invalid_baseUrls (connectionURL varchar2(50) 
not null);
insert into INVALID_BASEURLS 
values ('api.openweathermap.org');
commit;

Here is a sample project I will interrogate, note its connections -

Now to the OIC factory api, which I will use to check out my connections and make sure they adhere to the rules.

I run this in postman -

Here are snippets from the response -

{
            "adapterType": {
                "displayName": "Google Gmail",
                "links": [],
                "name": "gmail",
                "type": "PREINSTALLED"
            },
            "agentRequired": false,
            "agentSupported": false,
            "connectionProperties": [],
            "created": "2025-10-23T06:05:55.633+0000",
            "createdBy": "niall.commiskey@oracle.com",
            "description": "",
            "hasOverride": false,
            "id": "GMAIL",

another one -

     {
      "displayName": "Connection URL",
      "hasAttachment": false,
      "hiddenFlag": false,
      "propertyGroup": "CONNECTION_PROPS",
      "propertyName": "connectionUrl",
      "propertyShortDesc": "Please make sure that this value really corresponds to the type selected above.",
      "propertyType": "URL",
      "propertyValue": "https://api.openweathermap.org/data/2.5/",
      "requiredFlag": true
                },

{

As you can see, I've all the data here.

I create a connection in the project for the factory api -

and then use this in an integration -

I take the response payload from postman, massage it a bit, to reduce size and then use it as the response.

As you can see, I then invoke ATP to check if the adapter is forbidden.

I am using the following field as input -

I run the integration and check the activity stream -

Now to the second check - this time I'm looking for "forbidden" urls, such as our weather example.

Now to checking the urls. Remember, in our little world, it is forbidden to invoke the following -

I create a PL/SQL function to check this for me, here's the code -

create or replace PACKAGE BODY NIALLC.CONN_PKG AS 
 
Function f_check4InvalidURLS (in_project varchar2, 
                              in_url varchar2, 
                              in_connection varchar2)
RETURN varchar2

IS

v_connectionURL varchar2(50) := '';
v_response varchar2(50) := 'VALID URL';
v_position number := 0;

cursor c1 is
   SELECT connectionURL
   FROM invalid_baseurls;
BEGIN
   open c1;
   LOOP
     FETCH c1 into v_connectionURL;
     v_position := INSTR(in_url, v_connectionURL);
     if v_position > 0 THEN
        v_response := 'INVALID URL'; 
        insert into INVALID_CONNECTIONS values (
                  sysdate, in_project, 
                  in_connection,
                  'URL ' || in_url || ' is not allowed'); 
        commit;
        EXIT;
     end if;
   EXIT WHEN c1%NOTFOUND;
   END LOOP;
   close c1; 
RETURN v_response;

END f_check4InvalidURLS;

END;