2

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!

4
  • I'm guessing you're missing TERMINATED BY ';'. Also, you probably need it on the first column definition, and not on the last column. – Colin 't Hart May 27 '14 at 9:29
  • That's not changing anything. If I put this statement I see another error: SQL*Loader-350: Syntax error at line 10. Expecting valid column specification, "," or ")", found keyword terminated. ACTION char "TO_NUMBER(:ACTION, '999')" TERMINATED BY ';', – oz123 May 27 '14 at 10:14
  • That's because you put it in the wrong position: it should be after char and before the function specification. Also, why don't you specify fields terminated by ";" once in the header rather than the same for each column? I strongly suggest you study the documentation carefully. – Colin 't Hart May 27 '14 at 10:34
  • 1
    Might be the leading white space before the "3". – Philᵀᴹ May 27 '14 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.