Skip to main content
Version: ACE 4

Oracle Db

Overview​

Oracle Db step is used to access Oracle DB and run SQL queries against it.

Configuration​

Connection​

  • User Name - username to access the Oracle Db, we recommend consuming it as workspace variable e.g. {{$env.DB_USER}})
  • Password - password for accessing the database, we recommend consuming it as workspace variable e.g. {{$env.DB_PASSWORD}})
  • Connection string - the connection string to connect to database (the recommended way of usage would be to specify a global connection string and access it here through e.g. {{$env.DB_CONNECTION}})

Actions​

Actions allow creating SQL queries that need to be run when the step is executed.

  • SQL - SQL statement to execute on the database (e.g. SELECT id FROM users WHERE email = :email)
  • Binds - Optional array of variables that need to be inserted into query (For more details about binds refer to oracle documentation)
  • Options - Optional object of query options (For detailed list of options refer to oracle documentation for constant values refer to this link)
  • Target path - doc node on which to place the query results

Examples​

Create table​

Create table
{
  "id": "92c059b1-4ec0-4f1c-a7e3-0118c9f6628f",
  "name": "oracledb",
  "flow": {
    "name": "oracledb",
    "description": "",
    "steps": [
      {
        "stepType": "oracle-db",
        "color": "rgb(95, 206, 165)",
        "displayName": "OracleDB",
        "isSelected": true,
        "config": {
          "actions": [
            {
              "sql": "CREATE TABLE Persons (LastName varchar(255), FirstName varchar(255))",
              "binds": [],
              "options": {}
            }
          ],
          "user": "SYSTEM",
          "password": "1234",
          "connectString": "oracle:1521/XEPDB1"
        },
        "textColor": "black"
      }
    ]
  },
  "inputSchema": {},
  "createDate": "2023-01-15T16:34:10.904Z",
  "tags": [
    "general"
  ],
  "inputSchemaLabel": "",
  "version": 1
}
Result
{
  "doc": {},
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "oracle-db",
        "executionTime": 68
      }
    ],
    "executionTimeOfFlow": 68,
    "timeMetric": "ms"
  }
}

Insert​

Insert in table
{
  "id": "92c059b1-4ec0-4f1c-a7e3-0118c9f6628f",
  "name": "oracledb",
  "flow": {
    "name": "oracledb",
    "description": "",
    "steps": [
      {
        "stepType": "oracle-db",
        "color": "rgb(95, 206, 165)",
        "displayName": "OracleDB",
        "isSelected": true,
        "config": {
          "actions": [
            {
              "binds": [],
              "sql": "INSERT INTO Persons (LastName, FirstName) VALUES ('Jane', 'Doe')",
              "options": {}
            }
          ],
          "user": "SYSTEM",
          "password": "1234",
          "connectString": "oracle:1521/XEPDB1"
        },
        "textColor": "black"
      }
    ]
  },
  "inputSchema": {},
  "createDate": "2023-01-15T16:34:10.904Z",
  "tags": [
    "general"
  ],
  "inputSchemaLabel": "",
  "version": 1
}
Result
{
  "doc": {},
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "oracle-db",
        "executionTime": 68
      }
    ],
    "executionTimeOfFlow": 68,
    "timeMetric": "ms"
  }
}

Select​

Select all data from table
{
  "id": "92c059b1-4ec0-4f1c-a7e3-0118c9f6628f",
  "name": "oracledb",
  "flow": {
    "name": "oracledb",
    "description": "",
    "steps": [
      {
        "stepType": "oracle-db",
        "color": "rgb(95, 206, 165)",
        "displayName": "OracleDB",
        "isSelected": true,
        "config": {
          "actions": [
            {
              "binds": [],
              "sql": "SELECT * FROM Persons",
              "options": {},
              "targetPath": "result"
            }
          ],
          "user": "SYSTEM",
          "password": "1234",
          "connectString": "oracle:1521/XEPDB1"
        },
        "textColor": "black"
      }
    ]
  },
  "inputSchema": {},
  "createDate": "2023-01-15T16:34:10.904Z",
  "tags": [
    "general"
  ],
  "inputSchemaLabel": "",
  "version": 1
}

Update​

Result
{
  "doc": {
    "result": [
      {
        "LASTNAME": "Jane",
        "FIRSTNAME": "Doe"
      }
    ]
  },
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "oracle-db",
        "executionTime": 54
      }
    ],
    "executionTimeOfFlow": 54,
    "timeMetric": "ms"
  }
}

Delete​

Delete table
{
  "id": "92c059b1-4ec0-4f1c-a7e3-0118c9f6628f",
  "name": "oracledb",
  "flow": {
    "name": "oracledb",
    "description": "",
    "steps": [
      {
        "stepType": "oracle-db",
        "color": "rgb(95, 206, 165)",
        "displayName": "OracleDB",
        "isSelected": true,
        "config": {
          "actions": [
            {
              "sql": "DROP TABLE Persons",
              "binds": [],
              "options": {}
            }
          ],
          "user": "SYSTEM",
          "password": "1234",
          "connectString": "oracle:1521/XEPDB1"
        },
        "textColor": "black"
      }
    ]
  },
  "inputSchema": {},
  "createDate": "2023-01-15T16:34:10.904Z",
  "tags": [
    "general"
  ],
  "inputSchemaLabel": "",
  "version": 1
}
Result
{
  "doc": {},
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "oracle-db",
        "executionTime": 68
      }
    ],
    "executionTimeOfFlow": 68,
    "timeMetric": "ms"
  }
}