Sql loader hanging
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Sql loader hanging

  1. #1
    Join Date
    Sep 2000
    Posts
    362

    Angry

    Hi,
    I am trying to load data using sql loader but it just hangs without inserting any records. It sends all the records to the bad file.

    Below is the output i get on my command prompt when trying to execute sql loader.
    Also please see the control file at the bottom.

    C:\ofus>sqlldr80 userid=ofus/ofus@orcl control=loaddata.ctl log=loaddata.log

    SQL*Loader: Release 8.0.5.0.0 - Production on Tue May 29 10:37:16 2001

    (c) Copyright 1998 Oracle Corporation. All rights reserved.

    SQL*Loader-307: Warning: conflicting lengths 20 and 4 specified for column TEST.
    ACTUAL.
    SQL*Loader-307: Warning: conflicting lengths 20 and 4 specified for column TEST.
    ACTUALUS.
    Commit point reached - logical record count 101

    (This is where it hangs)

    Control File
    -------------
    load data
    infile 'test.txt'
    badfile 'test.bad'
    discardfile 'test.dcs'
    into table test
    (company position(01:10) char,
    time position(11:20) char,
    region position(21:30) char,
    lineitem position(31:45) char,
    actual position(46:65) decimal,
    actualus position(66:85) decimal)

    Sample Data
    --------------
    MBOC YR_1991 REGROLL BSPSIEB 1.000000 1.000000
    ALLEP YR_1991 REGROLL COFYE 24.000000 24.000000
    ALLEP YR_1991 REGROLL COLOP 48.000000 48.000000
    ALLEP YR_1991 REGROLL COEPAM 8.000000 8.000000
    ALLEP YR_1991 REGROLL COCAI 5.000000 5.000000

    Please help.

    Thanks
    Anurag

  2. #2
    Join Date
    Sep 2000
    Posts
    362
    Hi ,
    I was able to figure out the problem. In the control file I had to specify external for the decimal.

    Example
    ----------
    Control File
    -------------
    load data
    infile 'test.txt'
    badfile 'test.bad'
    discardfile 'test.dcs'
    into table test
    (company position(01:10) char,
    time position(11:20) char,
    region position(21:30) char,
    lineitem position(31:45) char,
    actual position(46:65) decimal external,
    actualus position(66:85) decimal external)


    What does the external parameter signify??


    Now I have another problem. sql loader is rejecting the records which have a comma in them.

    Example of the records are
    ALLEP YR_1991 REGROLL ISGR 2,215.964133 2,090.143202
    ALLEP YR_1991 REGROLL ISOEXP 1,237.364420 1,176.367821


    Is there any way I can import the data without commas by making some changes in the control file rather than removing the commas from the datafile itself


    The other question which I have is why does the sql loader not return you back to the command prompt after Commit point reached - logical record count 101



    Please suggest.

    Thanks
    Anurag



    [Edited by anuragmin on 05-29-2001 at 11:12 AM]

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