3.4.1.9 External Table Properties Pane

Specifies options for an external table.

An external table is a read-only table whose metadata is stored in the database but whose data in stored outside the database.

External Table

• Access Driver Type: Specifies the type of external table.

  • ORACLE_LOADER: Extracts data from text data files. This is the default access driver, which loads data from external tables to internal tables.

  • ORACLE_DATAPUMP: Extracts data from binary dump files. This access driver can perform both loads and unloads.

  • ORACLE_BIGDATA: Extracts data from Oracle Big Data Appliance.

  • ORACLE_HDFS: Extracts data stored in a Hadoop Distributed File System (HDFS).

  • ORACLE_HIVE: Extracts data stored in Apache HIVE.

• Default Directory: Specifies the default directory to use for all input and output files that do not explicitly name a directory object. The location is specified with a directory object, not a directory path.

• Access Params: Assigns values to the parameters of the specific access driver for the external table. Access parameters are optional.

  • OPAQUE_FORMAT_SPEC: The opaque_format_spec specifies all access parameters for the ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HDFS, and ORACLE_HIVE access drivers. For descriptions of the access parameters, see Oracle Database Utilities. Field names specified in the opaque_format_spec must match columns in the table definition, else Oracle Database ignores them.

  • USING CLOB: Enables you to derive the parameters and their values through a subquery. The subquery cannot contain any set operators or an ORDER BY clause. It must return one row containing a single item of data type CLOB.

• Reject Limit: The number of conversion errors that can occur during a query of the external data before an Oracle Database error is returned and the query is aborted.

• Project Column: Determines how the access driver validates the rows of an external table in subsequent queries.

  • ALL: Processes all column values, regardless of which columns are selected, and validates only those rows with fully valid column entries. If any column value raises an error, such as a data type conversion error, the row is rejected even if that column was not referenced in the select list of the query.

  • REFERENCED: Processes only those columns in the select list of the query.

  The ALL setting guarantees consistent result sets. The REFERENCED setting can result in different numbers of rows returned, depending on the columns referenced in subsequent queries, but is faster than the ALL setting. If a subsequent query selects all columns of the external table, then the settings behave identically.

• Location: Specifies the data files for the external table. Use the Add (+) icon to add each location specification.

  • For ORACLE_LOADER and ORACLE_DATAPUMP, the files are named in the form directory:file. The directory portion is optional. If it is missing, then the default directory is used as the directory for the file. If you are using the ORACLE_LOADER access driver, then you can use wildcards in the file name. An asterisk (*) signifies multiple characters and a question mark (?) signifies a single character.

  • For ORACLE_HDFS, LOCATION  is a list of Uniform Resource Identifiers (URIs) for a directory or for a file. There is no directory object associated with a URI.

  • For ORACLE_HIVE, LOCATION  is not used. Instead, the Hadoop HCatalog table is read to obtain information about the location of the data source (which could be a file or another database).

Opaque Format Spec

Specifies all access parameters for the ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HDFS, and ORACLE_HIVE access drivers.

For example:

RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    TERRITORY AMERICAN
    BADFILE log_file_dir:'ext_1v3.bad'
    LOGFILE log_file_dir:'ext_1v3.log'
    FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '^' LDRTRIM
    ( PROD_ID,
      CUST_ID ,
      TIME_ID DATE(10) "YYYY-MM-DD",
      CHANNEL_ID ,
      PROMO_ID ,
      QUANTITY_SOLD ,
      AMOUNT_SOLD ,
      UNIT_COST ,
      UNIT_PRICE
    )

and the full statement:

CREATE TABLE SH.SALES_TRANSACTIONS_EXT
    (
      PROD_ID NUMBER ,
      CUST_ID NUMBER , 
      TIME_ID DATE ,
      CHANNEL_ID NUMBER ,
      PROMO_ID NUMBER ,
      QUANTITY_SOLD NUMBER , 
     AMOUNT_SOLD NUMBER (10,2) , 
     UNIT_COST NUMBER (10,2) , 
     UNIT_PRICE NUMBER (10,2)
     )
     ORGANIZATION EXTERNAL
     (
     TYPE ORACLE_LOADER
     DEFAULT DIRECTORY DATA_FILE_DIR
     ACCESS PARAMETERS
     (
     RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    TERRITORY AMERICAN
    BADFILE log_file_dir:'ext_1v3.bad'
    LOGFILE log_file_dir:'ext_1v3.log'
    FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '^' LDRTRIM
    ( PROD_ID ,
      CUST_ID ,
      TIME_ID DATE(10) "YYYY-MM-DD",
      CHANNEL_ID ,
      PROMO_ID ,
      QUANTITY_SOLD ,
      AMOUNT_SOLD ,
      UNIT_COST ,
      UNIT_PRICE
    )
    )
     LOCATION ( "DATA_FILE_DIR":'sale1v3.dat')
     )
     REJECT LIMIT 100
 ;

CLOB Subquery

Type or copy and paste the query.

Note:

For more information about the external table fields, see Oracle Database Utilities and Oracle Database SQL Language Reference