| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL*Loader - Commit point reached - logical record count 27
Thanks John.
DO I HAVE TO EXPLICITLY ADD DOUBLE QUOTES to the CSV FILE to all fields that have COMMA....?
One more thing....
I have around 20000 records in my CSV file.
Also my log file shows that the data types are CHARACTER but some of my columns in Table are number(10) and number(10,2)....what do I need to do with them.....? will Oracle take care of this automatically....?
But then in my contorl file I explicitly mention datatypes as INTEGER etc...but then I get following error:
Record 2: Rejected - Error on table H_D_T. ORA-01460: unimplemented or unreasonable conversion requested
Any related information on this is appreciated.
THANKS!
Turkbear <john.greco_at_dot.state.mn.us> wrote in message news:<8jn9lvkqgqd1ng594g45p8r7cjs97i97k2_at_4ax.com>...
> hari_om_at_hotmail.com (Hari Om) wrote:
>
> >Here are the details of my error log files:
> >
> >I execute the command and get following message at console:
> >----------------------------------------------------------------------
> >./sqlldr scott/tiger_at_common control=/full_path/test.ctl
> >log=/full_path/adhoc/test.log
> >
> >SQL*Loader: Release 9.2.0.1.0 - Production on Tue Sep 2 10:49:27 2003
> >
> >Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> >
> >Commit point reached - logical record count 27
> >Commit point reached - logical record count 54
> >----------------------------------------------------------------------
> >
> >My control file is as follows:
> >----------------------------------------------------------------------
> >LOAD DATA
> >INFILE '/full_path/hdd.csv'
> >INTO TABLE h_d_t
> >FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
> >(
> >col1,col2.......col36,rec_id_num
> >)
> >----------------------------------------------------------------------
> >
> >
> >I created CSV File from my EXCEL sheet. Do I need to explicitly add
> >DOUBLE QUOTES to all the VARCHAR2 data columns or would the ORACLE
> >SQL*LOADER do that for me?
> >
> >It seems that the error points to my last column REC_ID_NUM (which is
> >NOT Primary Key) Any related information on this would really be
> >appreciated....
> >
> >Here is what my log file says:
> >---------------------------------------------------------------------------
> >SQL*Loader: Release 9.2.0.1.0 - Production on Tue Sep 2 08:39:55 2003
> >Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
> >Control File: /full_path/test.ctl
> >Data File: /full_path/hdd.csv
> >Bad File: /full_path/hdd.bad
> >Discard File: none specified
> >
> >(Allow all discards)
> >Number to load: ALL
> >Number to skip: 0
> >Errors allowed: 50
> >Bind array: 64 rows, maximum of 256000 bytes
> >Continuation: none specified
> >Path used: Conventional
> >
> >Table H_D_T, loaded from every logical record.
> >Insert option in effect for this table: INSERT
> >
> >Column Name Position Len Term Encl Datatype
> >------------------------------ ---------- ----- ---- ----
> >---------------------
> >COL 1 FIRST * , O(") CHARACTER
> >etc...
> >REC_ID_NUM NEXT * , O(") CHARACTER
> >
> >Record 1: Rejected - Error on table H_D_T, column REC_ID_NUM.
> >ORA-01722: invalid number
> >
> >Record 2: Rejected - Error on table H_D_T, column REC_ID_NUM.
> >ORA-01722: invalid number
> >
> >Record 3: Rejected - Error on table H_D_T, column REC_ID_NUM.
> >ORA-01722: invalid number
> >
> >Record 4: Rejected - Error on table H_D_T, column REC_ID_NUM.
> >ORA-01722: invalid number
> >
> >Record 5: Rejected - Error on table H_D_T, column REC_ID_NUM.
> >ORA-01722: invalid number
> >......
> >
> >Record 51: Rejected - Error on table H_D_T, column REC_ID_NUM.
> >ORA-01722: invalid number
> >
> >
> >MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
> >
> >Table H_D_T:
> >0 Rows successfully loaded.
> >51 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.
> >----------------------------------------------------------------------
> >I checked my CSV file and it seems that my last columns contains a
> >valid number.
> >
> >Any related information on this would really be appreciated....
> >
> >
> >THANKS!
> Hi,
> Have you looked in the
> /full_path/hdd.bad file to see what the data that is being rejected looks like?
>
> Also, if any of your varchar2 fields have a comma in them, the load will fail ...You would need to enclose with "" s
Received on Tue Sep 02 2003 - 17:26:36 CDT
 |
 |