Home » RDBMS Server » Server Utilities » Only half the records from dat file is loaded into table using sqlldr (Oracle 9i)
Only half the records from dat file is loaded into table using sqlldr [message #287274] Tue, 11 December 2007 15:10 Go to next message
karikal84
Messages: 4
Registered: June 2007
Junior Member
Hi,

I'm unable to load the complete data from .dat file to a table. I guess it has something to do with logical record count, because, when i tried the same ctl file with reduced number of records, i can see that the logical record count also gets reduced and only half the records in the dat file is loaded, again. Interestingly, i'm not getting any errors.

The following are the cmd, ctl i had used.

SQL Loader Statement
======================

sqlldr xxx/yyy CONTROL=test.ctl DATA=test.dat

CONTROL FILE (test.ctl)
=========================

LOAD DATA
INFILE 'test.dat'
INTO TABLE temp_table
FIELDS TERMINATED BY ':'
TRAILING NULLCOLS
(
emp_id,
emp_nm,
emp_type,
unit_id,
dept_nm,
div_nm,
sub_div_nm
)

Table Creation:
================

CREATE TABLE temp_table
(
emp_id NUMBER(10),
emp_nm VARCHAR2(256),
emp_type VARCHAR2(256),
unit_id NUMBER(10),
dept_nm VARCHAR2(512),
div_nm VARCHAR2(256),
sub_div_nm VARCHAR2(256),
dept_id NUMBER(10),
emp_type_cd NUMBER(4),
div_id NUMBER(10),
sub_div_id NUMBER(10),
unit_nm VARCHAR2(256),
PROCESSED_IND NUMBER(1)
);


This is what i get in the log file.

=============================
SQL*Loader: Release 9.2.0.6.0 - Production on Tue Dec 11 20:36:00 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: test.ctl
Data File: test.dat
Bad File: test.bad
Discard File: none specified

(Allow all discards)

Number to load: 5000
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct

Table TEMP_TABLE, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMP_ID FIRST * : CHARACTER
EMP_NM NEXT * : CHARACTER
EMP_TYPE NEXT * : CHARACTER
UNIT_ID NEXT * : CHARACTER
DEPT_NM NEXT * : CHARACTER
DIV_NM NEXT * : CHARACTER
SUB_DIV_NM NEXT * : CHARACTER

Record 2187: Rejected - Error on table TEMP_TABLE, column BASE_GRP_ID.
ORA-01722: invalid number


Table TEMP_TABLE:
2271 Rows successfully loaded.
1 Row 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.

Bind array size not used in direct path.
Column array rows : 143
Stream buffer bytes: 256000
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 2272
Total logical records rejected: 1
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 16
Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Tue Dec 11 20:36:00 2007
Run ended on Tue Dec 11 20:36:00 2007

Elapsed time was: 00:00:00.46
CPU time was: 00:00:00.09
=============================

Could any one please help me to load complete set of record into the table?
I had tried adding the following command to ctl file.

OPTIONS (DIRECT=(TRUE), LOAD=5000, ROWS=5000)
LOAD DATA
INFILE 'test.dat'
INTO TABLE temp_table
FIELDS TERMINATED BY ':'
TRAILING NULLCOLS
(
emp_id,
emp_nm,
emp_type,
unit_id,
dept_nm,
div_nm,
sub_div_nm
)

[Updated on: Tue, 11 December 2007 15:15]

Report message to a moderator

Re: Only half the records from dat file is loaded into table using sqlldr [message #287275 is a reply to message #287274] Tue, 11 December 2007 15:16 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Table TEMP_TABLE:
2271 Rows successfully loaded.
1 Row 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.
How many records were there in the input file? Regarding this information, only one record wasn't loaded due to INVALID_NUMBER error. The error says:
Quote:

Record 2187: Rejected - Error on table TEMP_TABLE, column BASE_GRP_ID.
ORA-01722: invalid number
I couldn't find the 'base_grp_id' column in the 'temp_table'. Why is that so?
Re: Only half the records from dat file is loaded into table using sqlldr [message #287276 is a reply to message #287275] Tue, 11 December 2007 15:31 Go to previous messageGo to next message
karikal84
Messages: 4
Registered: June 2007
Junior Member
Hi,
The one record had failed as it was given in wrong format. I had just changed the names of the columns and table, just in case... guess u would understand Smile
and the total number of record in the dat file was about 4687, of which only 2272 was considered and 2271 got loaded.
I'm sure it had not stuck inbetween due to the bad record, as it is located within 1K count, in the dat file and not around 2270th row.
Thanks for the quick reply. Smile
Amar
Re: Only half the records from dat file is loaded into table using sqlldr [message #287278 is a reply to message #287276] Tue, 11 December 2007 15:46 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

the total number of record in the dat file was about 4687
How did you check that?

Are you sure you loaded the correct file?
Re: Only half the records from dat file is loaded into table using sqlldr [message #287279 is a reply to message #287278] Tue, 11 December 2007 16:23 Go to previous message
karikal84
Messages: 4
Registered: June 2007
Junior Member
Hi,
It was my mistake. Embarassed
As you had mentioned, the count of records in the dat file was lesser (2272). I tried to update the dat file with the missing records, and then i was able to load them all.
Sorry for the confusion. And thanks for ur composed replies for my doubt.
Amar
Previous Topic: Import error
Next Topic: SQL LOADER - SILENT option
Goto Forum:
  


Current Time: Sat Dec 10 16:57:42 CST 2016

Total time taken to generate the page: 0.06866 seconds