Results 1 to 13 of 13

Thread: SQL*Loader Help

  1. #1
    Join Date
    Mar 2005
    Posts
    14

    Exclamation Unanswered: SQL*Loader Help

    I have a file I need to load into two tables that has two record types in it. The first record type "Header" has a length of 73 bytes with the layout...

    bytes 1-5 : table name
    byte 6 : record type indicator
    bytes 7-73: line text

    The second record type "Detail" has a length of 498 bytes with the layout...

    bytes 1-5 : table name
    byte 6 : record type indicator
    bytes 7-498: line text

    Th layout of the file is 1 "Header" record followed by a various number of "Detail" records. The file is one big stream of data (3.7 G) with no indicators as to field or record terminators...this is my problem.

    I was wondering if there was a way SQL*Loader can handle files like this or if there was a procedure that I could create to accomplish similar results.

    Thanks for your help in advance...THis is my first time posting!!!

  2. #2
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    Are all the fields fixed width? If they are not fixed width with no delimeters, I'd say it'd be pretty tough to work with the file. =P
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  3. #3
    Join Date
    Mar 2005
    Posts
    14
    Yes all of the fields are fixed width...

  4. #4
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    Here's a couple links with info on fixed format loads. You can control how logn each line will be, and use positioning for each column.

    http://www.adp-gmbh.ch/ora/tools/sql_loader/
    http://www.dba-oracle.com/tips_oracl...ontol_file.htm
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  5. #5
    Join Date
    Mar 2005
    Posts
    14
    Thanks for the links, but I couldn't find anything to solve my problem. I've loaded fixed width fields before, but the problem this time is that the 2 record types themselves are of variable lengths without any indication of a record separator or any indication of record length in the data. Did I miss something on the links? Thanks again.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I think I could handle this IF the following is true.
    All the detail records immediately follow the header record and table_names match.
    If/when the table_name changes, then you "know" that you have a new header record and the next 68 characters belong with it.
    It is just a Small Matter Of Programming (SMOP) in PERL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    looking at this from the other end, is it possible for the data file to be recreated, but with delimiters this time?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  8. #8
    Join Date
    Mar 2005
    Posts
    14
    The detail does follow the header in the file and the table names do match...so writing a quick procedure is the only way to load this? Is there anyway a straight SQL*Loader control file can do this?

  9. #9
    Join Date
    Mar 2005
    Posts
    14
    There is no way to recreate the file. It is from a MF archive tape and the MF has been decomissioned. This is the only file we have...any ideas?

  10. #10
    Join Date
    Mar 2005
    Posts
    14
    There is no way to recreate the file. It is from a MF archive tape and the MF has been decomissioned. This is the only file we have...any ideas?

    Thanks again for all ofyour help guys, hopefully we'll figure this out

  11. #11
    Join Date
    Mar 2005
    Posts
    14
    sorry for the double post

  12. #12
    Join Date
    Feb 2005
    Location
    Leesburg, VA
    Posts
    42
    I'd have to agree with anacedent...it's going to take extra programming to make it work.
    Steven Karam
    Oracle 10g Certified Master
    Web: OrclDBA.com
    Email: steve@orcldba.com

  13. #13
    Join Date
    Mar 2005
    Posts
    14
    LOAD DATA
    CHARACTERSET UTF8
    INFILE '/appdata1/ftpin/RXIRST01_D43_2004.txt'
    APPEND
    INTO TABLE atab
    WHEN ATABI = '1'
    (
    ATABN CHAR(5),
    ATABI CHAR(1),
    ATABT CHAR(67)
    )
    INTO TABLE atab
    WHEN ATABI = '2'
    (
    ATABN CHAR(5),
    ATABI CHAR(1),
    ATABT CHAR(492)
    )

    I'm trying to do something like this, it seems like it should work, but I'm only loading the first record...I double checked my lengths and they are all correct, any ideas?

Posting Permissions

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