DBMS_CLOUD Package Oracle Data Type to Parquet Mapping
Describes the mapping of Oracle data types to Parquet data types.
| Oracle Type | Parquet Type | Apache Arrow Type |
|---|---|---|
BINARY_DOUBLE |
DBL |
DOUBLE |
BINARY_FLOAT |
DBL |
DOUBLE |
DATE |
TIMESTAMP_MILLIS |
TIMESTAMP[MS] |
NUMBER(p,s) |
DBL |
DOUBLE |
NUMBER(p); p<19 |
DECIMAL(p) |
INT64 |
NUMBER(p); p>19 |
DBL |
DOUBLE |
TIMESTAMP(3) |
TIMESTAMP_MILLIS |
TIMESTAMP[MS] |
TIMESTAMP(6) |
TIMESTAMP_MICROS |
TIMESTAMP[US] |
TIMESTAMP(9) |
TIMESTAMP_NANOS |
TIMESTAMP[NS] |
TIMESTAMP(3) WITH LOCAL TIME ZONE |
TIMESTAMP_MILLIS |
TIMESTAMP[MS] |
TIMESTAMP(6) WITH LOCAL TIME ZONE |
TIMESTAMP_MICROS |
TIMESTAMP[US] |
TIMESTAMP(9) WITH LOCAL TIME ZONE |
TIMESTAMP_NANOS |
TIMESTAMP[NS] |
TIMESTAMP(3) WITH TIME ZONE |
TIMESTAMP_MILLIS |
TIMESTAMP[MS] |
TIMESTAMP(6) WITH TIME ZONE |
TIMESTAMP_MICROS |
TIMESTAMP[US] |
TIMESTAMP(9) WITH TIME ZONE |
TIMESTAMP_NANOS |
TIMESTAMP[NS] |
VARCHAR2(4000) |
STRING |
STRING/LARGE_STRING |
NLS Session Parameters
The NLS session parameters NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT and NLS_NUMERIC_CHARACTERS define how the date, timestamp, timestamp with time zone format, and radix separator for timestamp with decimal marker should be shown when a table with those column types are queried.
In addition, when you export data using DBMS_CLOUD.EXPORT_DATA and specify Parquet output, Autonomous AI Database reads the values of these parameters from the NLS_SESSION_PARAMETERS table. Autonomous AI Database uses these values to convert the Oracle data types DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE to Parquet types.
The NLS_SESSION_PARAMETERS parameters support an RR format mask (two character year specification).
The RR format mask for the year is not supported for these parameters when you export data to Parquet with DBMS_CLOUD.EXPORT_DATA. An application error is raised if you attempt to export to parquet and the NLS_SESSION_PARAMETERS are set to use the RR format mask (the default value for the RR format depends on the value of the NLS_TERRITORY parameter).
When one of the parameters NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT uses the RR format mask, you must change the format value to supported value to export data to Parquet with DBMS_CLOUD.EXPORT_DATA. For example:
ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY";
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH:MI:SS.FF TZH:TZM';After you change the value, you can verify the change by querying the NLS_SESSION_PARAMETERS view:
SELECT value FROM NLS_SESSION_PARAMETERS
WHERE parameter IN ('NLS_DATE_FORMAT','NLS_TIMESTAMP_FORMAT','NLS_TIMESTAMP_TZ_FORMAT');If NLS_DATE_FORMAT is set, it applies to the columns with DATE datatype. If NLS_TIMESTAMP_FORMAT is set, it applies to the columns with TIMESTAMP datattype. If NLS_TIMESTAMP_TZ_FORMAT is set, it applies to the columns with TIMESTAMP WITH TIME ZONE datatype.
See Date and Time Parameters and NLS Data Dictionary Views for more information.