comment in external table

Depending the files, you may use different signs for comments, typically

# hash
// slash slash
/* slash-star star-slash */
: column
-- dash dash

The latest is used in sql and pl/sql, but :

CREATE TABLE t (x NUMBER)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_pump_dir
ACCESS PARAMETERS (
FIELDS TERMINATED BY ';' -- This is a comment
(x))
LOCATION ('x'));

SELECT * FROM t;
SELECT * FROM t
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "minussign": expecting one of:
"column, enclosed, (, ltrim, lrtrim, ldrtrim, missing,
notrim, optionally, rtrim, reject"
KUP-01007: at line 2 column 38

not in external table access parameters.

No comment is allowed there!

3 thoughts on “comment in external table

  1. Mikhail Velikikh

    Hi Laurent,

    Documentation mentioned that:

    http://docs.oracle.com/database/121/SUTIL/et_params.htm#SUTIL1379

    @ Comments are lines that begin with two hyphens followed by text. Comments must be placed before any access parameters, for example:
    @ –This is a comment.
    @ –This is another comment.
    @ RECORDS DELIMITED BY NEWLINE

    I modified your code placing comment in it and receive a error that was expected:

    [code language=”sql”]
    SQL> CREATE TABLE t (x NUMBER)
    2 ORGANIZATION EXTERNAL
    3 (
    4 TYPE ORACLE_LOADER
    5 DEFAULT DIRECTORY data_pump_dir
    6 ACCESS PARAMETERS (
    7 — This is a comment
    8 FIELDS TERMINATED BY ‘;’
    9 (x))
    10 LOCATION (‘x’));

    Table created.

    SQL>
    SQL> select * from t;
    select * from t
    *
    ERROR at line 1:
    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error
    KUP-04080: directory object DATA_PUMP_DIR not found
    [/code]

    Best regards,
    Mikhail.

Comments are closed.