Oracle
#
DescriptionCreate an API to allow Oracle to be accessible via REST.
#
UIAccess the DataTrucker URL via a browser
#
Create a credentials- Go to Credentials in the sidebar > Oracle Credentials
- Scroll down to Credential Management
- Fill the form to create the connection
- Credential name โ an arbitrary value to identify the credential object
- 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
- Username of Oracle,
- Limit access to this user to the specific API. Please reach out to a DBA to provide specific grant access to this user
- The password of the Oracle User
- Min /Max Pool for connection pooling.
#
Make an API- Go to Database API in the Sidebar > Oracle APIโs
- Fill the form to create the API
- Resource Name: an arbitrary name to identify the resource
- The Credential linked to the resources
- Method: The type of REST Call
- Timeout: To ensure query times out after a set period in time
- Script: The SQL Query
- The SQL query can have parameters identified by โ[[]]โ Example โ[[id]]โ [ single quotes recommended instead of double quotes ]โ
- Parameters are case sensitive in some cases
- Validation Regex of Input values, input sanitization before querying the database
- Examples provided in the UI when you try to create a new API
#
Query the resource you createdURL: /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 / KubernetesFor 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 RESTURL: /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 RESTURL: /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