You need to add the 'terminated by' clause after the 'INTEGER external'.
Jay wrote:
>
> Hello,
> The control file used by sqlloader below would not load successfully. I
> can't seem to find the correct settings to load the data. Note that I
> must choose the character "|" as the delimiter for each field. Please
> give suggestions on how to load the ff. data.
>
> Thanks in advance for help.
>
> ************************** control file **************************
> LOAD DATA INFILE *
> APPEND
> INTO TABLE lim_library_books
> (
> name terminated by '|',
> b_id INTEGER external,
> author terminated by '|',
> year INTEGER external,
> keywords terminated by '|',
> entry_date DATE
> )
> -- there are 3 lines of data below
> -- it may show more than 3 lines in the browser
> BEGINDATA
> ORACLE - The Complete Reference - 3rd edition|1|George Koch, Kevin
> Loney|1995|oracle complete reference koch loney 1995|30-SEP-99
> ORACLE - Developer's Guide|2|David McClanahan|1996|oracle developer
> guide 1996 mcclanahan|30-SEP-99
> Advanced Oracle PL/SQL Programming with Packages|3|Steven
> Feuerstein|1996|advanced oracle pl sql packages 1996
> feuerstein|30-SEP-99
> ************************** log file **************************
> Path used: Conventional
>
> Table LIM_LIBRARY_BOOKS, loaded from every logical record.
> Insert option in effect for this table: APPEND
>
> Column Name Position Len Term Encl Datatype
> ------------------------------ ---------- ----- ---- ---- --------
> NAME FIRST * | CHARACTER
> B_ID NEXT 1 CHARACTER
> AUTHOR NEXT * | CHARACTER
> YEAR NEXT 1 CHARACTER
> KEYWORDS NEXT * | CHARACTER
> ENTRY_DATE NEXT 9 DATE DD-MON-YY
>
> Record 1: Rejected - Error on table LIM_LIBRARY_BOOKS, column YEAR.
> ORA-01722: invalid number
>
> Record 2: Rejected - Error on table LIM_LIBRARY_BOOKS, column YEAR.
> ORA-01722: invalid number
>
> Record 3: Rejected - Error on table LIM_LIBRARY_BOOKS, column YEAR.
> ORA-01722: invalid number
>
> Table LIM_LIBRARY_BOOKS:
> 0 Rows successfully loaded.
> 3 Rows not loaded due to data errors.
> 0 Rows not loaded because all WHEN clauses were failed.
> 0 Rows not loaded because all fields were null.
>
> Space allocated for bind array: 50816 bytes(64 rows)
> Space allocated for memory besides bind array: 0 bytes
> ****************************************************
Received on Mon Oct 04 1999 - 12:00:13 CDT
