Categories
sql

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!

By Laurent Schneider

Oracle Certified Master

3 replies on “comment in external table”

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.