Oracle's External Tables

Looking at Oracle’s External Tables feature, which ‘uses the ‘sql-loader’ engine.

Links include:

http://www.quest-pipelines.com/newsletter-v6/0105_B.htm

http://www.oracle.com/oramag/oracle/01-sep/o51o9i.html

Ran the following:

create or replace directory bjs_history as ‘D:BJS_HISTORY’;

drop table external_bjs_history;

CREATE TABLE external_bjs_history (bjs_sched_time varchar2(12),
bjs_job_no varchar2(8),
bjs_job_name VARCHAR2(40),
record_type varchar2(5),
job_started varchar2(17),
job_finished varchar2(17)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY bjs_history
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘,’
MISSING FIELD VALUES ARE NULL
badfile ‘history.bad’
)
LOCATION (‘history.dat’)
)
REJECT LIMIT UNLIMITED;

select count(*) from external_bjs_history;

and got:

SQL> @c:bjs_owner

Directory created.
Table dropped.
Table created.

select count(*) from external_bjs_history
*
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 “badfile”: expecting one of: “exit, (, reject”
KUP-01007: at line 4 column 9
ORA-06512: at “SYS.ORACLE_LOADER”, line 14
ORA-06512: at line 1

I took out the badfile clause and all OK:

Probably a rtfm type thing….

Advertisements