...
Oracle datatypes mappings and conversions:
Oracle has two types of driver thin and oci.
The JDBC Thin client is a pure Java, Type IV driver. It is lightweight and easy to install. More https://docs.oracle.com/cd/E11882_01/java.112/e16548/jdbcthin.htm#JJDBC28195
Oci driver requires native libraries to be installed, but provides some additional features like OCI Connection Pooling, Client Result Cache etc.
More https://www.oracle.com/database/technologies/appdev/oci.html,
https://docs.oracle.com/cd/E11882_01/java.112/e16548/instclnt.htm#JJDBC28218
Also oracle support tnsnames.ora file on client machine. More https://docs.oracle.com/database/121/NETRF/tnsnames.htm#NETRF007
Design
The suggestion is to create maven submodule oracle-plugin under database-plugins repo.
...
| User Facing Name | Type | Description | Constraints |
|---|---|---|---|
| Label | String | Label for UI | |
| Reference Name | String | Uniquely identified name for lineage | |
| Host | String | Oracle host | Required (defaults to localhost on UI) |
| Port | Number | Specific port where Oracle running on | Optional (default 1521) Database |
| SID | String | SID name to connect | Required |
| Service name | String | Database Service name to connect | Required |
| Username | String | DB username | Required |
| Password | Password | User password | Required |
| Connection Arguments | Keyvalue | A list of arbitrary string tag/value pairs as connection arguments, list of properties https://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html | |
| Table Name | String | Name of a database table to write to | |
| Driver type | Select | Oracle driver type | Possible values (thin, oci) |
...
| User Facing Name | Type | Description | Constraints |
|---|---|---|---|
| Label | String | Label for UI | |
| Reference Name | String | Uniquely identified name for lineage | |
| Host | String | Oracle host | Required (defaults to localhost on UI) |
| Port | Number | Specific port where Oracle running on | Optional (default 1521)Database |
| SID | String | SID name to connect | Required |
| Service name | String | Database Service name to connect | Required |
| Import Query | String | Query for import data | Valid SQL query |
| Username | String | DB username | Required |
| Password | String | User password | Required |
| Bounding Query | String | Returns max and minof split-By Filed | Valid SQL query |
| Split-By Field Name | String | Field name which will be used to generate splits | |
| Number of Splits to Generate | Number | Number of splits to generate | |
| Connection Arguments | Keyvalue | A list of arbitrary string tag/value pairs as connection arguments, list of properties https://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html | |
| Driver type | Select | Oracle driver type | Possible values (thin, oci) |
Action Properties
| User Facing Name | Type | Description | Constraints |
|---|---|---|---|
| Label | String | Label for UI | |
| Host | String | Oracle host | Required (defaults to localhost on UI) |
| Port | Number | Specific port where Oracle running on | Optional (default 1521) |
| SID | String | SID name to connect | Required |
| Service name | String |
| Service name to connect | Required | ||
| Username | String | DB username | Required |
| Password | String | User password | Required |
| Connection Arguments | Keyvalue | A list of arbitrary string tag/value pairs as connection arguments, list of properties https://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/jdbc/OracleDriver.html | |
| Database Command | String | Database command to run | Valid SQL query |
| Driver type | Select | Oracle driver type | Possible values (thin, oci) |
Data Types Mapping
| Oracle Data Type | CDAP Schema Data Type | Support | Comment |
|---|---|---|---|
| VARCHAR2 | Schema.Type.STRING | + | |
| NVARCHAR2 | Schema.Type.STRING | + | |
| VARCHAR | Schema.Type.STRING | + | |
| NUMBER | Schema.LogicalType.DECIMAL | + | |
| FLOAT | Schema.Type.DOUBLE | + | FLOAT(126) by default value is represented internally as NUMBER |
| LONG | Schema.Type.STRING | + | Character data of variable length. |
| DATE | Schema.LogicalType.TIMESTAMP_MICROS | + | |
| BINARY_FLOAT | Schema.Type.FLOAT | + | |
| BINARY_DOUBLE | Schema.Type.DOUBLE | + | |
| TIMESTAMP | Schema.LogicalType.TIMESTAMP_MICROS | + | |
| TIMESTAMP WITH TIME ZONE | Schema.LogicalType.TIMESTAMP_MICROS | * | Currently converted to UTC time, modifying original time zone. |
| TIMESTAMP WITH LOCAL TIME ZONE | Schema.LogicalType.TIMESTAMP_MICROS | + | |
| INTERVAL YEAR TO MONTH | Schema.Type.STRING | + | |
| INTERVAL DAY TO SECOND | Schema.Type.STRING | + | |
| RAW | Schema.Type.BYTES | + | |
| LONG RAW | Schema.Type.BYTES | + | |
| ROWID | Schema.Type.STRING | + | |
| UROWID | Schema.Type.STRING | + | |
| CHAR | Schema.Type.STRING | + | |
| NCHAR | Schema.Type.STRING | + | |
| CLOB | Schema.Type.STRING | + | |
| NCLOB | Schema.Type.STRING | + | |
| BLOB | Schema.Type.BYTES | + | |
| BFILE | Schema.Type.BYTES | * | Deprecated by Oracle in java api, added mapping to Type.BYTES |
Approach
Create a module oracle-plugin in database-plugins project, reuse existing database-plugins code if possible. Add Oracle-specific properties to configuration, add support for Oracle-specific datatypes. Update UI widgets JSON definitions.
...