Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Unanswered: Oracle 9i SQLLDR error - HELP !

    I am trying to use sqlldr to load some data sets generated from a mainframe system. I transferred the data set to my local unix server using NDM (ascii mode) and then am trying to invoke sqlldr.

    The issue is :

    There are certain packed decimal fields which are not being loaded into Oracle with the correct values. For eg:
    there is a value '8' at our side, instead of value '2' (original value)
    there is a value '14' at our side, instead of value '3' (original value)
    there is a value '3' at our side, instead of value '4' (original value).

    Can anyone suggest, please ?

    Thanks.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    transer in binary and not ASCII


    Check out your flat file to verify the same data is in that file that was loaded into the db. If they match then you know you need to transfer in binary.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2003
    Posts
    4
    Originally posted by The_Duck
    transer in binary and not ASCII


    Check out your flat file to verify the same data is in that file that was loaded into the db. If they match then you know you need to transfer in binary.
    Hai, Thanks for the hint. If it transfer in Binary, where/how would the EOL marker be formed. Moreover, it is a variable length file at the host end. Can you suggest something more ?

    Thanks.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    for a transfer in binary, the file is not augmented in the end. It just ensures all the values remain the same through the transfer (it is reconverted and it will look the same in the end).

    If using FTP all you add is "bin" before the get clause.

    try it out. nothing should change in the way you handle the file after the transfer.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2003
    Posts
    4
    Hi, Thanks . But , when I transfer in bin mode, I am not able to get the EOL marker. As a result, "wc -l" on that file returns "0" records. However, ascii conversion does help, but like I mentioned the values are jumbled up.

    I tried using "dd" also in unix - to convert the file after transfer in bin mode - but no avail !

    Any suggestions ?

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    what, you want to count how many lines are in the file??

    why? to find the end-of-line?


    I am confused. Perhaps if you posted a few lines of the flat-file you are loading and also post your .ctl file we can get to the root of the problem here.

    or, are you saying you get an empty file when you try to transfer in binary mode?

    Maybe post your FTP script or something.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Nov 2003
    Posts
    4
    Originally posted by The_Duck
    what, you want to count how many lines are in the file??

    why? to find the end-of-line?


    I am confused. Perhaps if you posted a few lines of the flat-file you are loading and also post your .ctl file we can get to the root of the problem here.

    or, are you saying you get an empty file when you try to transfer in binary mode?

    Maybe post your FTP script or something.
    If we do not have the EOL marker, how do we get to load the same ?There is no specific ftp "script", per say. I just use NDM to download the same in binary and wc -l returs "0", if I transfer is ascii, "wc -l" returns the exact number of lines as per the original file. However , when I try to load the same, the packed decimal values get jumbled.

    Do you have an email id where i can send the control file and other details ?

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •