Describes rules for how ORC, Parquet, and Avro column names are converted to Oracle column names.
The following are supported for ORC, Parquet and Avro column names, but may require use of double quotes for Oracle SQL references in external tables. Thus, for ease of use and to avoid having to use double quotes when referencing column names, if possible do not use the following in ORC, Parquet, or Avro column names:
Oracle SQL reserved words
Note:Support for ORC format requires Oracle Database 19c. The format option
orcis not supported with Oracle Database 18c.
The following table shows various types of ORC, Parquet, and Avro column names, and rules for using the column names in Oracle column names in external tables.
|Parquet, ORC, or Avro name||CREATE TABLE Name||Oracle CATALOG||Valid SQL||Notes|
|part, Part, or PART||part, Part, PART||PART||
||Oracle implicitly uppercases unquoted column names|
|Ord No||"Ord No"||Ord No||
||Double quotes are required when there are embedded blanks, which also preserves the character case|
||Double quotes are required when there is a leading underscore, which also preserves the character case|
||Double quotes are required when there is a leading numeric digit, which also preserves the character case|
|create, Create, or CREATE, and so on. (any case variation) partition, Partition, PARTITION, and so on (for an Oracle Reserved word)||"CREATE" "PARTITION"||CREATE PARTITION||
||Double quotes are required around Oracle SQL Reserved words. These are forced to uppercase, but must always be double-quoted when used anywhere in SQL|
|rowid, Rowid, ROWid, and so on (for ROWID see notes)||rowid||
||For ROWID, any mixed or lower-case variation of ROWID preserves the case and must always be double-quoted and use the original case variations. Due to the inherent conflict with Oracle ROWID for the table, if you specify upper-case ROWID, it is automatically stored as lower-case "rowid" and must always be double-quoted when referenced.|
In general a column name in an external table can be referenced without double quotes.
Unless there is an embedded blank, a leading underscore ("_") or leading numeric digit ("0" through "9") in the column name, the original case of the column name is preserved, and it must always be referenced with double quotes and using the original case (upper, lower or mixed-case) of the ORC, Parquet, or Avro, column name.
DBMS_CLOUD.CREATE_EXTERNAL_TABLEto create an external table with ORC, Parquet, or Avro format specified, use the
DESCRIBEcommand in SQL*Plus to view the table's column names.
When Oracle SQL Reserved Words are used in ORC, Parquet, or Avro column names, they must always be double-quoted when referenced anywhere in SQL. See Oracle SQL Reserved Words for more information.