# Oracle Reference

> All Oracle authentication options and data type mappings.

#  Oracle Reference

Authentication options and data type handling for [Oracle connections](../connect/oracle.md).

## Authentication

| Variable | Example | Description |
|---|---|---|
| `DATACONTRACT_ORACLE_USERNAME` | `system` | Username |
| `DATACONTRACT_ORACLE_PASSWORD` | `0x162e53` | Password |
| `DATACONTRACT_ORACLE_CLIENT_DIR` | `C:\oracle\client` | Path to an Oracle Instant Client installation (required for thick mode) |

`host`, `port` (default 1521), `service_name`, and `schema` come from the contract's `servers` block.

## Data types

### Importing

`datacontract import sql --dialect oracle` maps DDL types as follows; the normalized SQL type is kept as `physicalType`.

| Oracle type | `logicalType` |
|---|---|
| `VARCHAR2`, `NVARCHAR2`, `CHAR`, `NCHAR` | `string` (`maxLength` for varchar/char) |
| `CLOB`, `NCLOB` | `string` |
| `NUMBER` | `number` (precision/scale as custom properties) |
| `BINARY_FLOAT`, `BINARY_DOUBLE`, `FLOAT` | `number` |
| `DATE` | `date` |
| `TIMESTAMP`, `TIMESTAMP WITH (LOCAL) TIME ZONE` | `timestamp` |
| `RAW`, `BLOB`, `BFILE` | `string` (format `binary`) |
| `XMLTYPE`, `ROWID`, `INTERVAL` types | *(unset)* — `physicalType` is still written |

### Testing

Oracle supports **native type introspection**: the declared `physicalType` is checked against `ALL_TAB_COLUMNS`. Length is reconstructed for character and `RAW` types, precision/scale for `NUMBER`; parameters are only enforced when the contract declares them. Types that sqlglot can't parse (`ROWID`, `INTERVAL`) are compared by name. A `physicalType` that can't be interpreted falls back to the logical type category comparison.

{/* AUTOGENERATED TYPE MAPPING: do not edit by hand; regenerate with update_reference_types.py */}

### Logical type mapping

When no `physicalType` is declared, the CLI derives the native type from the `logicalType` — for example in `datacontract export sql` and the dbt exports. This table is generated from the converter in the CLI's code:

| `logicalType` | Oracle type |
|---|---|
| `string` | `NVARCHAR2(2000)` |
| `integer` | `NUMBER` |
| `number` | `NUMBER` |
| `boolean` | `CHAR(1)` |
| `date` | `DATE` |
| `timestamp` | `TIMESTAMP(6) WITH TIME ZONE` |
| `time` | `DATE` |
| `object` | `CLOB` |
| `array` | `CLOB` |

{/* END AUTOGENERATED TYPE MAPPING */}