Oracle Cloud Infrastructure Documentation

DBMS_CLOUD Package Avro, ORC, and Parquet to Oracle Column Name Mapping

Describes rules for how Avro, ORC, and Parquet column names are converted to Oracle column names.

The following are supported for Avro, ORC, and Parquet 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 Avro, ORC, and Parquet column names:

  • Embedded blanks

  • Leading numbers

  • Leading underscores

  • Oracle SQL reserved words

The following table shows various types of Avro, ORC, and Parquet column names, and rules for using the column names in Oracle column names in external tables.

Avro, ORC, or Parquet Name CREATE TABLE Name Oracle CATALOG Valid SQL Notes
part, Part, or PART part, Part, PART PART

select part

select Part

select paRt

select PART

 Oracle implicitly uppercases unquoted column names
Ord No "Ord No" Ord No select "Ord No" Double quotes are required when there are embedded blanks, which also preserves the character case
__index_key__ "__index_key__" __index_key__ select "__index_key__" Double quotes are required when there is a leading underscore, which also preserves the character case
6Way "6Way" 6Way select "6Way" 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

select "CREATE"

select "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  

select "rowid"

select "Rowid"

select "ROWid"

select "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.
Note

  • 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 Avro, ORC, or Parquet column name.

  • After using DBMS_CLOUD.CREATE_EXTERNAL_TABLE to create an external table with the format specified as avro, orc, or parquet, use the DESCRIBE command in SQL*Plus to view the table's column names.

  • When Oracle SQL Reserved Words are used in Avro, ORC, or Parquet column names, they must always be double-quoted when referenced anywhere in SQL. See Oracle SQL Reserved Words for more information.