Help plz in sql loader!!!!

[bhu]

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

[vnktummala]

Post all control file parameters that you are using.

Thanks,

[bhu]

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,

[vnktummala]

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,

[bhu]

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

[PAVB]

'hello','cd'
'world','im'
'012my','nameis'

[bhu]

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

[PAVB]

show it.
post the whole session log.

[bhu]

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

[LKBrwn_DBA]

Remove the "var" and the column lengths:

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

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>