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  |
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 |
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jan 12 02:44:05 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
