DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Help plz in sql loader!!!!

  1. #1
    Join Date
    Nov 2009
    Location
    India
    Posts
    9

    Question 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

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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.

  3. #3
    Join Date
    Nov 2009
    Location
    India
    Posts
    9
    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,

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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.

  5. #5
    Join Date
    Nov 2009
    Location
    India
    Posts
    9
    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

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  7. #7
    Join Date
    Nov 2009
    Location
    India
    Posts
    9
    ya i tried in the way u mentioned but still i am getting the same error...

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  9. #9
    Join Date
    Nov 2009
    Location
    India
    Posts
    9
    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

  10. #10
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool 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
  •  


Click Here to Expand Forum to Full Width