Oracle DB API Wrapper

7 min

Overview

Note

This solution supports SELECT functions as well as INSERT, UPDATE, DELETE functions.

Requirements

A Litmus Edge is setup. An Oracle DB is accessible through the network.

How to use the solution using Docker

After Checkout, a tar.gz file is expected to be downloaded
Log in to your LitmusEdge, Navigate to Applications -> Images
Click on ([27-icon icon="fa fa-plus"]) Upload Image and upload the tar file there
Once uploaded successfully, Navigate to Applications -> Containers and enter the docker run command

Here is an example to run Oracle DB API Wrapper with Docker:

docker run -dt --name oracle-gateway -p <HOST_PORT>:3000 \
-e LOG_LEVEL='<LOG_LEVEL>' \
-e LOG_MAX_SIZE='<LOG_MAX_SIZE>' \
-e LOG_MAX_FILES='<LOG_MAX_FILES>' \
--restart=always \
oracle-gateway:latest

Configuration Options

⚠️ When using Docker, the following environment variables must be set before running the container.

Key	Description
-dt	Run the containers in detached mode and with Terminal.
-- name oracle-gateway	Assign a name to the container (optional)
-p <HOST_PORT>:3000	Map a host port to port 3000 of the container (required)
-e LOG_LEVEL='<LOG_LEVEL>'	Set the log level for the application (optional, defaults to 'info').
-e LOG_MAX_SIZE='<LOG_MAX_SIZE>'	Set the maximum size for log files (optional, defaults to '20m').
-e LOG_MAX_FILES='<LOG_MAX_FILES>'	Set the maximum number of files for log rotation (optional, defaults to '14d').
--restart=always	Automatically restart the container if it exits unexpectedly.
oracle-gateway:latest	Docker Image tag and version

Example Request Body used in Flows

{
    "queryStr" : "SELECT 1 FROM Dual",
    "user": "sys",
    "password": "DbPasswordExample@123",
    "mode": "THN",
    "privilege": true,
    "connString": "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST='<server>')(PORT='1521'))(CONNECT_DATA=(SERVER='<server>')(SERVICE_NAME='XE')))",
    "server": "<server>",
    "port": 1521,
    "hostname": "<server>",
    "SID": "XE",
    "serviceName": "XE"
}

Method	URL	Description	Extras
POST	/api/data
		Parameters in body
		mode	One of the following: ("CNS","SRN","SID","THK","THN")
		-> CNS Connection String	(default)
		Parameters	user, password, connString, queryStr
		-> SRN Service Name
		Parameters	user, password, hostname, port, serviceName, queryStr
		-> SID Session ID
		Parameters	user, password, hostname, port, SID, queryStr
		-> THK Thick Mode using ORACLE Client Connection
		Parameters	user, password, connString, queryStr
		-> THN Thin Mode using ORACLE Client Connection
		Parameters	user, password, connString, queryStr

Example LE Flow

[
    {
        "id": "01740c8932692cc4",
        "type": "tab",
        "label": "OracleDB Connection",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "e6cf2c52c4ec85dd",
        "type": "inject",
        "z": "01740c8932692cc4",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 200,
        "y": 200,
        "wires": [
            [
                "57137f9e.ce895"
            ]
        ]
    },
    {
        "id": "94b070f38334a607",
        "type": "debug",
        "z": "01740c8932692cc4",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 990,
        "y": 120,
        "wires": []
    },
    {
        "id": "3eb9c0257dda01e3",
        "type": "http request",
        "z": "01740c8932692cc4",
        "name": "",
        "method": "use",
        "ret": "txt",
        "paytoqs": "ignore",
        "url": "",
        "tls": "",
        "persist": false,
        "proxy": "",
        "insecureHTTPParser": false,
        "authType": "",
        "senderr": false,
        "headers": [],
        "x": 690,
        "y": 200,
        "wires": [
            [
                "1ef3217515dd667e"
            ]
        ]
    },
    {
        "id": "57137f9e.ce895",
        "type": "function",
        "z": "01740c8932692cc4",
        "name": "Set connection parameters",
        "func": "const ip = '10.30.50.1:3000'; //IP Address to the Oracle Gateway API Wrapper container and port \nconst ip_db = '10.20.30.40' //IP Address of the Oracle Database\n\nmsg.method ='post';\nmsg.url = `http://${ip}/api/data`;\nmsg.headers={\n    'Content-Type' : 'application/json'\n}\n\nmsg.payload = {\n    queryStr : \"SELECT 1 FROM Dual\",\n    user: \"sys\", // Oracle DB username\n    password: \"DbPasswordExample@123\", // Oracle DB password \n    mode: \"THN\", // Connection type (aka mode)\n    privilege: true, // True if accessing as DB Administrator. False if access as DB user\n    connString: `(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST='${ip_db}')(PORT='1521'))(CONNECT_DATA=(SERVER='${ip_db}')(SERVICE_NAME='XE')))`, // ensure all connection string is accurate \n}\n\nmsg.redirect= \"follow\"\n\nreturn msg;",
        "outputs": 1,
        "timeout": "",
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 440,
        "y": 200,
        "wires": [
            [
                "3eb9c0257dda01e3"
            ]
        ]
    },
    {
        "id": "1ef3217515dd667e",
        "type": "json",
        "z": "01740c8932692cc4",
        "name": "",
        "property": "payload",
        "action": "",
        "pretty": false,
        "x": 870,
        "y": 200,
        "wires": [
            [
                "94b070f38334a607"
            ]
        ]
    }
]