Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2006
    Posts
    46

    Unanswered: SQL Loader and large records

    I have been able to use SQL Loader with data that is fairly conventional.
    Howerver, I am faving a problem with data which is from a full text search databases. For those of you who do not know, full text search databases have a field which is very large and would be called something like OCRText and would represent the ASCII representation of the text in a document. This field when exported to an ASCII file can be quite larege and is over the 1048576 bytes that Oracle allows for a physical record.

    Could someone please help me out because my loader routine crashes after the 22nd document (ie. row) because the OCR field is quite large?

    I have the Book by O'reilly on SQL Loader and I need some help here as I do not see how using the Concatenate, ContinueIf and ContinueIf statements will help me out.

  2. #2
    Join Date
    May 2006
    Posts
    132
    What error are you getting?

    Can you post your control file?

  3. #3
    Join Date
    Feb 2006
    Posts
    46

    reply for SQL Loader

    Please note that my table has a definition of type CLOB for the OCRText field.
    Also, I can expand my CHAR definition from 120 million to 240 million and the program gets slower. For instance, if I change the CHAR definition to 1.2 gigs then Oracle loads one document every 20 minutes, which is too slow.


    Load Data
    Infile 'c:\kpmg\duplicates_16000.csv'
    TRUNCATE
    discardmax 1000000
    Into Table kpmg_duplicates
    Fields Terminated By ''
    Optionally Enclosed By ''
    Trailing NULLCOLS
    (
    BEGPSN,
    ENDPSN,
    STARTBATES,
    ENDBATES,
    DOCDATE,
    DOCTYPE,
    OCRTEXT CHAR(120000000)
    )

  4. #4
    Join Date
    May 2006
    Posts
    132
    OK...but what kind of errors are you experiencing?

  5. #5
    Join Date
    Feb 2006
    Posts
    46

    Error noted

    The error that I am getting is SQL Loader error 510 which notes that the physical record size is over 1048576 bytes.

    Could this limit be raised?

  6. #6
    Join Date
    May 2006
    Posts
    132
    Have you tried modifying the READSIZE buffer?

  7. #7
    Join Date
    Feb 2006
    Posts
    46

    readsize buffer

    No, I do not know how to do this. If you know how, please let me know. Otherwise I can look it up.

    Thanks for your help.

  8. #8
    Join Date
    Feb 2006
    Posts
    46

    Readsize May Have Worked! Thanks

    Readsize May Have Worked! Thanks

  9. #9
    Join Date
    May 2006
    Posts
    132
    Your welcome...glad it helped.

Posting Permissions

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