OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » sqlldr error

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlldr error [message #201141]

Thu, 02 November 2006 15:28

ellicott
Messages: 6
Registered: February 2006

Junior Member

Hi,

I tried to load csv file into oracle using sqlldr. My control file is following:

LOAD DATA
INFILE 'E:\usr\hong\projects\trans-informatics\archive\dummy_110206-case.csv'
BADFILE 'E:\usr\hong\projects\oracle\case.bad'
APPEND
INTO TABLE case
TRAILING NULLCOLS
(id INTEGER,
person_id INTEGER,
path_acc_number CHAR TERMINATED BY ','
NULLIF path_acc_number=BLANKS,
operation_date DATE "MM/DD/YYYY" TERMINATED BY ','
NULLIF operation_date=BLANKS,
operation_id CHAR TERMINATED BY WHITESPACE

)

The table columns are following:

id number not null,
person_id number not null,
path_acc_number varchar2(20),
operation_date date,
operation_id varchar2(20)

sample data likes:
1,1,2000-1,7/15/2002,o10
...
26,26,2000-26,11/29/2005,o72
27,27,2000-27,12/18/2000,o81
28,1,,7/15/2002,o140
29,2,,4/3/1995,o140

Errors I got are:

Record 1: Rejected - Error on table CASE.
ORA-02291: integrity constraint (TRAM.CASE_PERSON_ID_FK) violated - parent key not found

Record 28: Rejected - Error on table CASE, column OPERATION_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected

What is wrong for row 28 as NULLIF path_acc_number=BLANKS, is used?

Your help will be apprecaited,

[Updated on: Thu, 02 November 2006 15:43]

Report message to a moderator

Re: sqlldr error [message #201158 is a reply to message #201141]

Thu, 02 November 2006 17:16

ellicott
Messages: 6
Registered: February 2006

Junior Member

I think the question really is can you use date mask without position. In this case
operation_date DATE "MM/DD/YYYY" TERMINATED BY ','
NULLIF operation_date=BLANKS,

how to find position for the column?

[Updated on: Thu, 02 November 2006 17:17]

Report message to a moderator

Re: sqlldr error [message #201521 is a reply to message #201141]

Sun, 05 November 2006 09:37

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

SQL> edit c:\test.csv

SQL> edit c:\test.ctl

SQL> host sqlldr hr/hr@orcl control=c:\test.ctl log=C:\test.log

SQL*Loader: Release 10.1.0.2.0 - Production on Sun Nov 5 19:35:09 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Commit point reached - logical record count 6

SQL> select * from a;

        ID  PERSON_ID PATH_ACC_NO          OPERATION OPERATION_ID
---------- ---------- -------------------- --------- --------------------
         1          1 2000-1               15-JUL-02 o10
        26         26 2000-26              29-NOV-05 o72
        27         27 2000-27              18-DEC-00 o81
        28          1                      15-JUL-02 o140
        29          2                      03-APR-95 o140

SQL>

test.ctl
Load Data
INFILE 'c:\test.csv'
badfile 'c:\test.bad'
APPEND
INTO TABLE a
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
(id, person_id, path_acc_no, operation_date DATE "MM/DD/YYYY", operation_id)

hope this helps
Mohammad Taj.

Report message to a moderator

Previous Topic:	Localization of Control file
Next Topic:	10g to 8i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 00:30:45 CST 2024