Oracle Db

Overview

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

Configuration

Designer Desktop

info

To run Oracle DB step in Designer Desktop it is necessary to set up Oracle DB client on local machine following these instructions

Connection

• User Name (user) - username to access the Oracle DB, we recommend consuming it as workspace variable e.g. {{$env.DB_USER}})
• Password (password) - password for accessing the database, we recommend consuming it as workspace variable e.g. {{$env.DB_PASSWORD}})
• Connection string (connectString) - 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)

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

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

Examples

Create table

Create table

tags: []
sampleInputSchema: ""
sampleData: {}
description: ""
steps:
  - name: Step oracle-db
    description: ""
    config:
      actions:
        - sql: CREATE TABLE Persons (LastName varchar(255), FirstName varchar(255))
          binds: []
          options: {}
          targetPath: result
      user: SYSTEM
      password: "1234"
      connectString: oracle:1521/XEPDB1
    stepType: oracle-db
    condition: ""

Result

{
  "doc": {},
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "oracle-db",
        "executionTime": 68
      }
    ],
    "executionTimeOfFlow": 68,
    "timeMetric": "ms"
  }
}

Insert

Insert in table

tags: []
sampleInputSchema: ""
sampleData: {}
description: ""
steps:
  - name: Step oracle-db
    description: ""
    config:
      actions:
        - sql: INSERT INTO Persons (LastName, FirstName) VALUES ('Jane', 'Doe')
          binds: []
          options: {}
          targetPath: result
      user: SYSTEM
      password: "1234"
      connectString: oracle:1521/XEPDB1
    stepType: oracle-db
    condition: ""

Result

{
  "doc": {},
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "oracle-db",
        "executionTime": 68
      }
    ],
    "executionTimeOfFlow": 68,
    "timeMetric": "ms"
  }
}

Select

Select all data from table

tags: []
sampleInputSchema: ""
sampleData: {}
description: ""
steps:
  - name: Step oracle-db
    description: ""
    config:
      actions:
        - sql: SELECT * FROM Persons
          binds: []
          options: {}
          targetPath: result
      user: SYSTEM
      password: "1234"
      connectString: oracle:1521/XEPDB1
    stepType: oracle-db
    condition: ""

Result

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

Delete

Delete table

tags: []
sampleInputSchema: ""
sampleData: {}
description: ""
steps:
  - name: Step oracle-db
    description: ""
    config:
      actions:
        - sql: DROP TABLE Persons
          binds: []
          options: {}
          targetPath: result
      user: SYSTEM
      password: "1234"
      connectString: oracle:1521/XEPDB1
    stepType: oracle-db
    condition: ""

Result

{
  "doc": {},
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "oracle-db",
        "executionTime": 68
      }
    ],
    "executionTimeOfFlow": 68,
    "timeMetric": "ms"
  }
}