I am trying to load the following csv file into a table:
TIMESTAMP ; SESSIONID;PROXY_SESSIONID; ACTION;USERNAME ;USERHOST ;OS_USERNAME ;CLIENT_ID ;ACTION_NAME ;TRANSLATE(SQL_TEXT,'X'||CHR(9)||CHR(10)||CHR(13),'X')
04-29-2014 21:46:40; 707304320;NULL ; 3;COMP ;preview.firma.de ;console ; ;SELECT ;SELECT TRIM(LEADING '.' FROM class || '.' || name) AS key_for_value, value AS value FROM config_tbl
my control file is the following:
OPTIONS(skip=1)
LOAD DATA
INFILE 'dump_audit_table.csv'
INTO TABLE tmp_audit_trail
TRAILING NULLCOLS
(
TIMESTAMP date "MM-DD-YYYY HH24:MI:SS",
SESSIONID INTEGER EXTERNAL TERMINATED BY ';',
PROXY_SESSIONID INTEGER EXTERNAL TERMINATED BY ';',
ACTION char "TO_NUMBER(:ACTION, '999')",
USERNAME char(30) TERMINATED BY ';',
USERHOST char(128) TERMINATED BY ';',
OS_USERNAME char(255) TERMINATED BY ';',
CLIENT_ID char(64) TERMINATED BY ';',
ACTION_NAME char(28) TERMINATED BY ';',
SQL_TEXT char(2000) TERMINATED BY ';'
)
And this fails with:
Record 1: Rejected - Error on table TMP_AUDIT_TRAIL, column ACTION.
ORA-01722: invalid number
I Can't figure why this is an invalid number. #
Update: The following line can be read:
04-29-2014 21:46:40; 707304320;;3;USER;preview.firma.de;console;;SELECT;SELECT TRIM(LEADING '.' FROM class || '.' || name) AS key_for_value, value AS value FROM config_tbl
The solution is: No leading and trailing spaces are allowed!
