CREATE TABLE T1
(field1 VARCHAR2(25) NOT NULL,
field2 VARCHAR2(80) NOT NULL,
...
fieldn NUMBER)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY <oracle_directory_object_name>
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
BADFILE <file_name>
DISCARDFILE <file_name>
LOGFILE <file_name>
FIELDS TERMINATED BY '<terminator>'
LOCATION ('<file_name>'));
(field1 VARCHAR2(25) NOT NULL,
field2 VARCHAR2(80) NOT NULL,
...
fieldn NUMBER)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY <oracle_directory_object_name>
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
BADFILE <file_name>
DISCARDFILE <file_name>
LOGFILE <file_name>
FIELDS TERMINATED BY '<terminator>'
LOCATION ('<file_name>'));
When I compiled the table, I got the following error.
ORA-30657: Operation not supported on
external organized table
Cause: User attempted on operation on an external table which is not
supported.
Action: Don't do that!
Quite hilarious... but what did I do wrong? The Oracle error message did not help me at all (and who could ever help?). I checked the directory. It was there and its name was typed correctly. File names were ok. So what was the issue?
It was as easy as removing the NOT NULL clause on the field specification.
CREATE TABLE T1
(field1 VARCHAR2(25)NOT NULL,
field2 VARCHAR2(80) NOT NULL,
...
fieldn NUMBER)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY <oracle_directory_object_name>
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
BADFILE <file_name>
DISCARDFILE <file_name>
LOGFILE <file_name>
FIELDS TERMINATED BY '<terminator>'
LOCATION ('<file_name>')
(field1 VARCHAR2(25)
field2 VARCHAR2(80)
...
fieldn NUMBER)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY <oracle_directory_object_name>
ACCESS PARAMETERS (
RECORDS DELIMITED BY newline
BADFILE <file_name>
DISCARDFILE <file_name>
LOGFILE <file_name>
FIELDS TERMINATED BY '<terminator>'
LOCATION ('<file_name>')
Silly error, but it took me a while to find out what was wrong. Hope this helps.