-
Help plz in sql loader!!!!
I wan to use sql loader but when i am using it i get the following error
sql*loader-501 unable to read file (dat file which i am using)
sql*loader-563 bad length for var record
can anyone plz help me
thanks in advance
bhu
-
Post all control file parameters that you are using.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
yup sure here is the control file
load data
infile 'c:/ex.dat' "var 3"
into table exam
fields terminated by ',' optionally by '"'
(col1 char(5),col2 char(7))
ex.dat file
009hello,cd,
010world,im,
012my,name is,
-
fields terminated by ',' optionally by '"'
(col1 char(5),col2 char(7))
ex.dat file
009hello,cd,
010world,im,
012my,name is,
col1 is char(5) where as in the ex.dat it is more than 5. correct that.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
ya i made the changes accoringly n here is my dat file
hello,cd,
world,im,
012my,nameis,
still i get the same error
thanks
bhu
-
Code:
'hello','cd'
'world','im'
'012my','nameis'
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
ya i tried in the way u mentioned but still i am getting the same error...
-
show it.
post the whole session log.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
here is the log file
SQL*Loader: Release 9.0.1.1.1 - Production on Wed Nov 11 19:50:21 2009
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Control File: C:\controlvar.CTL
Data File: c:/ex.dat
File processing option string: "var 3"
Bad File: c:/ulcase5.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 EXAM, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1 FIRST 5 , O(") CHARACTER
COL2 NEXT 7 , O(") CHARACTER
SQL*Loader-501: Unable to read file (c:/ex.dat)
SQL*Loader-563: bad length for VAR record
-
Remove the garbage
Remove the "var" and the column lengths:
Code:
-- Your control file
load data
infile 'C:\ex.dat'
truncate into table exam
fields terminated by ',' optionally enclosed by '"'
(col1 char,col2 char)
And you get this:
Code:
H:\app\prj\sql>sqlldr scott/tiger control=ex.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Wed Nov 11 09:18:47 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 3
H:\app\prj\sql>type ex.log
SQL*Loader: Release 10.2.0.1.0 - Production on Wed Nov 11 09:18:47 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: ex.ctl
Data File: c:\ex.dat
Bad File: ex.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 EXAM, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1 FIRST * , O(") CHARACTER
COL2 NEXT * , O(") CHARACTER
Table EXAM:
3 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: 33024 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Wed Nov 11 09:18:47 2009
Run ended on Wed Nov 11 09:18:47 2009
Elapsed time was: 00:00:00.37
CPU time was: 00:00:00.11
H:\app\prj\sql>
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|