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 Replies to “comment in external table”

  1. 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.

Leave a Reply

Your email address will not be published.