Home » RDBMS Server » Server Administration » SQL*Loader Problem
| SQL*Loader Problem [message #59411] |
Mon, 17 November 2003 23:32  |
Sujit Sarkar
Messages: 40
Registered: September 2003
|
Member |
|
|
Hi friends,
I am facing a problem with SQL*Loader utility while i am loading some data from a text file into Oracle.
I am loading data into two tables using a single controlfile.
one of the field has varchar2(4000) and i am loading a very long string into that and due to that i am getting an Error message which is as follows
"Record 6: Rejected - Error on table QMASTER, column EXPL.
Field in data file exceeds maximum length"
How to Solve this my controlfile,data file and sqlloader logfile i am specifying as follows.
I want this kind help from any of you and It is very Urgent
------ Sql*Loader Controlfile
load data
infile 'f:test.dat'
into table qmaster
when recid = "1"
fields terminated by ',' optionally enclosed by '"'
(recid filler position(1) integer external terminated by ',',
qid char terminated by ',',
tid char terminated by ',',
question char terminated by ',',
type char terminated by ',',
correct_ans char terminated by ',',
expl char)
into table qdetail
when recid = "2"
fields terminated by ',' optionally enclosed by '"'
(recid filler position(1) integer external terminated by ',',
optid char terminated by ',',
qid char terminated by ',',
opt char)
--------- Sql*Loader Datafile
1,Q001,T01,Which effect will pinning large and frequently used object in the shared pool have on your database,S,A,"Pinning frequently used objects in the shared pool allows users to share code, because these objects do not have to be cached in each time they are executed."
2,A,Q001,Users can share the same code.
2,B,Q001,Users will NOT have to execute ad hoc queries.
2,C,Q001,Operating system resources will not be overhead.
2,D,Q001,The DBA wil not have to load the DBMS_APPLICATION _PKG.
1,Q001,T01,"Which statement about performance tuning is true?",S,C,"Everyone who is involved with the Oracle9i system should be concerned with performance tuning. This includes application developers database administrators, and system administrators. When problem occurs, it is usually the DBA who makes the first attempt to solve the problem."
2,A,Q002,Only application developers should be concerned with performance tuning.
2,B,Q002,Only database administration should be concerned with performance and tunning.
2,C,Q002,Application designers should think about performance and tuning when designing systems.
2,D,Q002,The system administration should make the first attempt at solving any performance issues.
---------Sql*Loader Logfile
SQL*Loader: Release 9.0.1.1.1 - Production on Sun Nov 17 23:48:13 2002
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Control File: f:test.ctl
Data File: f:test.dat
Bad File: f:test.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 QMASTER, loaded when RECID = 0X31(character '1')
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
RECID 1 * , O(") CHARACTER
(FILLER FIELD)
QID NEXT * , O(") CHARACTER
TID NEXT * , O(") CHARACTER
QUESTION NEXT * , O(") CHARACTER
TYPE NEXT * , O(") CHARACTER
CORRECT_ANS NEXT * , O(") CHARACTER
EXPL NEXT * , O(") CHARACTER
Table QDETAIL, loaded when RECID = 0X32(character '2')
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
RECID 1 * , O(") CHARACTER
(FILLER FIELD)
OPTID NEXT * , O(") CHARACTER
QID NEXT * , O(") CHARACTER
OPT NEXT * , O(") CHARACTER
Record 6: Rejected - Error on table QMASTER, column EXPL.
Field in data file exceeds maximum length
Record 7: Rejected - Error on table QDETAIL.
ORA-02291: integrity constraint (EXAM.FK_QID) violated - parent key not found
Record 8: Rejected - Error on table QDETAIL.
ORA-02291: integrity constraint (EXAM.FK_QID) violated - parent key not found
Record 9: Rejected - Error on table QDETAIL.
ORA-02291: integrity constraint (EXAM.FK_QID) violated - parent key not found
Record 10: Rejected - Error on table QDETAIL.
ORA-02291: integrity constraint (EXAM.FK_QID) violated - parent key not found
Table QMASTER:
1 Row successfully loaded.
1 Row not loaded due to data errors.
8 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table QDETAIL:
4 Rows successfully loaded.
5 Rows not loaded due to data errors.
2 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 181632 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 10
Total logical records rejected: 5
Total logical records discarded: 0
Run began on Sun Nov 17 23:48:13 2002
Run ended on Sun Nov 17 23:48:14 2002
Elapsed time was: 00:00:00.42
CPU time was: 00:00:00.09
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 10:29:06 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
