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,

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,

Quote:

---

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,

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'

---

ya i tried in the way u mentioned but still i am getting the same error...

show it.
post the whole session log.

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 "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)

---

:rolleyes:
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>

---

ya thanks i hav already tried in the way u said it works.but i wan to know the reason y its not working wit var

thanks
bhu

wht should i do if i am using var?????

Why use "var" when you don't need it?
If you do have to use it, then your data lengths are incorrect.
Here is the correct data file to use with your controlfile:

Code:

---

011hello,cd,
011world,im,
013my,name is,

---

And the controlfile would be:

Code:

---

load data
infile 'c:\ex.dat' "var 3"
truncate into table exam
fields terminated by ',' optionally enclosed by '"'
(col1 char,col2 char)

---

:p

thank you so much but its working only with this data file. can you please tell me how the data should be.

thanks
bhu

thank you so much i got my query cleared... am getting proper output with out error now...


thanks
bhu