Oracle

Description

Create an API to allow Oracle to be accessible via REST.

UI

Access the DataTrucker URL via a browser

Create a credentials

1. Go to Credentials in the sidebar > Oracle Credentials
2. Scroll down to Credential Management
3. Fill the form to create the connection
   1. Credential name – an arbitrary value to identify the credential object
   2. The hostname of the Oracle [ This is a TNS entry see the API for an example]. Note, make sure the API server(not the UI) has access into Oracle
   3. Username of Oracle,
      1. Limit access to this user to the specific API. Please reach out to a DBA to provide specific grant access to this user
   4. The password of the Oracle User
   5. Min /Max Pool for connection pooling.

Make an API

1. Go to Database API in the Sidebar > Oracle API’s
2. Fill the form to create the API
   1. Resource Name: an arbitrary name to identify the resource
   2. The Credential linked to the resources
   3. Method: The type of REST Call
   4. Timeout: To ensure query times out after a set period in time
   5. Script: The SQL Query
      1. The SQL query can have parameters identified by ‘[[]]’ Example ‘[[id]]’ [ single quotes recommended instead of double quotes ]”
      2. Parameters are case sensitive in some cases
   6. Validation Regex of Input values, input sanitization before querying the database
      1. Examples provided in the UI when you try to create a new API

Query the resource you created

URL: /api/v1/jobs/<resource name>TYPE: <method defined>HEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {        "<input_key>": "<value>",        "<input_key>": "<value>",        "<input_key>": "<value>",        "<input_key>": "<value>"}
Response: 200 OK

As a CRD in Openshift / Kubernetes

For credentials use the the API below to the management end point

---apiVersion: datatrucker.datatrucker.io/v1kind: DatatruckerFlowmetadata:  name: datatruckerflow-samplespec:  Resources:    requests:      memory: "256Mi"      cpu: "250m"    limits:      memory: "256Mi"      cpu: "500m"  JobDefinitions:  - resourcename: oracle1    name: oracle1    type: DB-Oracle    credentialname: oracledata1    tenant: Admin    restmethod: POST    script: SELECT '[[dbname]]' as dbname,CURRENT_DATE  as current_date FROM DUAL    job_timeout: 4354    validations:      type: object      properties:        dbname:          type: string          pattern: "^[a-z0-9]*$"          maxLength: 8  Keys:    configmap: placeholder  Scripts:    configmap: placeholder  Type: Job  DatatruckerConfig: datatruckerconfig-sample  Replicas: 1  API:    name: api    Image:      repository: docker.io      imageName: datatruckerio/datatrucker-api      tagName: latest  

API

Create a credential via REST

URL: /api/v1/credentials/TYPE: POSTHEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {                "credentialname": "oracledata1",                "type": "DB-Oracle",                "hostname": "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=\"Oracle\")(PORT=\"1521\"))(CONNECT_DATA=(SERVER=DEDICATED)(SID=\"ORCLCDB\")))",                "username": "john",                "password": "abcd1234",                "minpool": 100,                "maxpool": 200}
Response: 201 OK

Create the API via REST

URL: /api/v1/resourcesTYPE: POSTHEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {        "resourcename": "oracle1",        "type": "DB-Oracle",        "credentialname": "oracledata1",        "restmethod": "POST",        "script": "SELECT '[[dbname]]' as dbname,CURRENT_DATE  as current_date FROM DUAL",        "job_timeout": 4354,        "validations": {          "type": "object",          "properties": {            "dbname": {              "type": "string",              "pattern": "^[a-z0-9]*$",              "maxLength": 8            }          }        }      }
Response: 201 OK