Oracle loader ERROR - using external table

[Rolldice]

hello,

I'm using an external table to load data into my database.

I was getting an error of: data to big for data type, which came from blank space in my last source field.

After looking up a thread on this I found a solution to Trim that incoming field - But I can't seem to get the sintax right:

Here's the error I now get:

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 "identifier": expecting one of: "comma,
date_format, defaultif, enclosed, (, ltrim, lrtrim, ldrtrim, notrim, nullif,
optionally, ), rtrim, terminated"
KUP-01008: the bad identifier was: trim
KUP-01007: at line 34 column 23
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

And here's my exteral table def:

CREATE TABLE CAPTAIN_UPLOAD(
CustomerNo CHAR(6),
CustomerName CHAR(30),
InstallBranchCode CHAR(2),
ContactPersonName CHAR(20),
ContactPersonEmailAddress VARCHAR2(80),
ContactPersonPhone CHAR(15),
BkupContactPersonName VARCHAR2(80),
BkupContactPersonEmail VARCHAR2(80),
BkupContactPersonPhone CHAR(15),
UICModel CHAR(12),
ModelAlias CHAR(12),
ColourCopierCode CHAR(2),
SerialNumber CHAR(12),
TenderCode CHAR(3),
PSPContract CHAR(18),
ReadDate DATE,
InstallDate DATE,
LastTestCopies_L NUMBER,
LastTestCopies_S NUMBER,
LastMeter_L NUMBER,
LastMeter_S NUMBER,
Grp1ReadingDate DATE,
Grp1Usage_L NUMBER,
Grp1Usage_S NUMBER,
Grp2ReadingDate DATE,
Grp2Usage_L NUMBER,
Grp2Usage_S NUMBER,
Grp3ReadingDate DATE,
Grp3Usage_L NUMBER,
Grp3Usage_S CHAR(200))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY CAPTAIN_DIR
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE 'BAD_CAPTAIN_UPLOAD'
LOGFILE 'LOG_CAPTAIN_UPLOAD'
FIELDS TERMINATED BY ','
(CustomerNo CHAR,
CustomerName CHAR,
InstallBranchCode CHAR,
ContactPersonName CHAR,
ContactPersonEmailAddress CHAR,
ContactPersonPhone CHAR,
BkupContactPersonName CHAR,
BkupContactPersonEmail CHAR,
BkupContactPersonPhone CHAR,
UICModel CHAR,
ModelAlias CHAR,
ColourCopierCode CHAR,
SerialNumber CHAR,
TenderCode CHAR,
PSPContract CHAR,
ReadDate CHAR date_format date mask "YYYYMMDD",
InstallDate CHAR date_format date mask "YYYYMMDD",
LastTestCopies_L CHAR,
LastTestCopies_S CHAR,
LastMeter_L CHAR,
LastMeter_S CHAR,
Grp1ReadingDate CHAR date_format date mask "YYYYMMDD",
Grp1Usage_L CHAR,
Grp1Usage_S CHAR,
Grp2ReadingDate CHAR date_format date mask "YYYYMMDD",
Grp2Usage_L CHAR,
Grp2Usage_S CHAR,
Grp3ReadingDate CHAR date_format date mask "YYYYMMDD",
Grp3Usage_L CHAR,
Grp3Usage_S CHAR trim(Grp3Usage_S)))
LOCATION ('UPLOADT.TXT'))
PARALLEL 5
REJECT LIMIT 200;

Any help - Very appreciated!!!

RDGS Luke

----------------------------------------
Another throw closer to a win ... ???
----------------------------------------

 

[Rolldice]

Go those that have this issue:

The answer was to add:

RTRIM in the External table definition after that
FIELDS TERMINATED BY ','.

COOL AS!!

Luke

----------------------------------------
Another throw closer to a win ... ???
----------------------------------------