Re: SQL*Loader - Commit point reached - logical record count 27

From: Eric Parker <eric.parkerthedross_at_virgin.net>
Date: Wed, 3 Sep 2003 16:56:51 +0100
Message-ID: <hbo5b.320$Ve3.42_at_newsfep4-winn.server.ntli.net>

"Hari Om" <hari_om_at_hotmail.com> wrote in message news:d1d5ebe4.0309030657.7839951_at_posting.google.com...
> Here is my updated version of my execution.....
>
> SQL> desc h_d_t
> Name Type
> ----------------------------------------------------------------------
> col1 VARCHAR2(3)
> col2 NUMBER(4)
> col3 VARCHAR2(8)
> col4 VARCHAR2(8)
> col5 VARCHAR2(8)
> col6 NUMBER(3)
> col7 NUMBER(8)
> col8 VARCHAR2(1)
> col9 NUMBER(10,2)
> col10 NUMBER(3)
>
> My control file is:
> ----------------------------------------------------------------------
> LOAD DATA
> INFILE '/full_path/hdd.csv'
> INTO TABLE h_d_t
> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
> (
> col1 char "ltrim(rtrim(:col1))",
> col2 integer external,
> col3 char "ltrim(rtrim(:col3))",
> col4 char "ltrim(rtrim(:col4))",
> col5 char "ltrim(rtrim(:col5))",
> col6 integer external,
> col7 integer external,
> col8 char "ltrim(rtrim(:col8))",
> col9 integer external,
> col10 integer external
> )
> ----------------------------------------------------------------------
>
> My CSV File (sample)data is as :
> 120,1992,08221946,02121992,02201992,405,8,,9303.48,0
> 121,1993,08221946,02121992,02201992,405,,,1303.08,0
>
> I execute my SQL*Loader as follows:
> ----------------------------------------------------------------------
> ./sqlldr scott/tiger_at_common control=/full_path/test.ctl
> log=/full_path/test.log discard=/full_path/hdd.dis
>
> SQL*Loader: Release 9.2.0.1.0 - Production on Wed Sep 3 08:33:06 2003
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> Commit point reached - logical record count 36
> Commit point reached - logical record count 72
> ----------------------------------------------------------------------
>
> My LOG File is as follows:
> -----------------------------------------------------------------------
> Record 1: Rejected - Error on table H_D_T, column COL1.
> ORA-01460: unimplemented or unreasonable conversion requested
>
> Record 2: Rejected - Error on table H_D_T.
> ORA-01460: unimplemented or unreasonable conversion requested
>
> Record 3: Rejected - Error on table H_D_T.
> ORA-01460: unimplemented or unreasonable conversion requeste
> ....
> Record 51: Rejected - Error on table H_D_T.
> ORA-01460: unimplemented or unreasonable conversion requeste
> ----------------------------------------------------------------------
>
> Questions:
> -----------
> 1) Is the above definition of the Table H_D_T and one in Control file
> fields proper?
> 2) For VARCHAR2 types do I need to specify the length in Control file?
> 3) What about NUMBER Datatype columns...? Is declaring them as
> "INTEGER EXTERNAL"
> sufficent....? DO I need to typecast them....?
> 4) What about NUMBER(10,2)...how do I represent these in Control
> file...?
>
> Any relate dinformation is appreciated...
>
> THANKS!
Hari

I can only assume you've not supplied the failing records in your sample. I've just run the SQL*Loader using your supplied info with the result

---

Table H_D_T:
  2 Rows successfully loaded.
  0 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.

Space allocated for bind array:                 165120 bytes(64 rows)

Read buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             2
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Wed Sep 03 16:48:43 2003
Run ended on Wed Sep 03 16:48:43 2003

Elapsed time was:     00:00:00.14
CPU time was:         00:00:00.06
-----------------

How about supplying the duff data ?

eric

--
Remove the dross to contact me directly

Received on Wed Sep 03 2003 - 17:56:51 CEST