--- title: Oracle DB API Wrapper slug: solutions/oracle-db-api-wrapper docTags: createdAt: 2024-07-04T15:11:48.013Z --- ## Overview ![](https://api.archbee.com/api/optimize/SSUUxKZUk9bFTEPNn_6Zo/2LPLUwT7QqJUBTvwM2MsG_function-diagram-solutions-oracle-db-api-wrapperdrawio.png) ## 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**: ```linux docker run -dt --name oracle-gateway -p :3000 \ -e LOG_LEVEL='' \ -e LOG_MAX_SIZE='' \ -e 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 \:3000 | Map a host port to port 3000 of the container (required) | | -e LOG\_LEVEL='\' | Set the log level for the application (optional, defaults to 'info'). | | -e LOG\_MAX\_SIZE='\' | Set the maximum size for log files (optional, defaults to '20m'). | | -e 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 ```json { "queryStr" : "SELECT 1 FROM Dual", "user": "sys", "password": "DbPasswordExample@123", "mode": "THN", "privilege": true, "connString": "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST='')(PORT='1521'))(CONNECT_DATA=(SERVER='')(SERVICE_NAME='XE')))", "server": "", "port": 1521, "hostname": "", "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 ```json [ { "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" ] ] } ] ```