Grokbase › Groups › Oracle › oracle-l › October 2002
FAQ
I have to create packages that will generate several flat files of data
from tables that will be sent to other systems to be processed.

I am looking for ideas on how to ensure data integrity in the flat
files.

For example, the expected record count is stored on the first line of
the file to ensure that the correct amount of records was received.

The systems group is chartered to ensure the flat files are correctly
FTPed between systems, so that's covered.

I just worry that if "somehow" a flat file is scrambled then the
scrambled data is loaded into the database, therefore corrupting it.

At this phase, XML is not an option

I keep thinking that some sort of CRC should be stored with each line in
the flat file. And then before the line is loaded into the database,
the CRC is compared against the generated CRC of the just read line.
Has anyone done anything like this? Any examples out there?

Many TIA!!

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Grabowy, Chris
INET: cgrabowy_at_fcg.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Mercadante, Thomas F at Oct 24, 2002 at 6:14 pm ⇧
    Chris,


    have you thought about summing a number column in the record and placing
    this sum in a trailer record? this way, you have a header and trailer
    record which helps you be confident that the whole file made it to the
    target system. and by comparing the sum of the imported records with the
    trailer record, it gives you a better level of confidence that things didn't
    get scrambled.


    I used to do all sorts of these things when file transfer was not as good as
    it is now. the above method is pretty simple, easy to do, and pretty
    accurate.


    hope this helps.


    Tom Mercadante
    Oracle Certified Professional

    -----Original Message-----
    Sent: Thursday, October 24, 2002 1:35 PM
    To: Multiple recipients of list ORACLE-L

    I have to create packages that will generate several flat files of data from
    tables that will be sent to other systems to be processed.

    I am looking for ideas on how to ensure data integrity in the flat files.

    For example, the expected record count is stored on the first line of the
    file to ensure that the correct amount of records was received.

    The systems group is chartered to ensure the flat files are correctly FTPed
    between systems, so that's covered.

    I just worry that if "somehow" a flat file is scrambled then the scrambled
    data is loaded into the database, therefore corrupting it.

    At this phase, XML is not an option

    I keep thinking that some sort of CRC should be stored with each line in the
    flat file. And then before the line is loaded into the database, the CRC is
    compared against the generated CRC of the just read line. Has anyone done
    anything like this? Any examples out there?

    Many TIA!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mercadante, Thomas F
    INET: NDATFM_at_labor.state.ny.us

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • DENNIS WILLIAMS at Oct 24, 2002 at 6:19 pm ⇧
    Chris - I would consider checksums. Many systems have built-in checksum
    functions. On Unix these are cksum and sum. You could probably write a
    simple one that would work on your various systems. Search for checksum and
    somewhere on the Internet is probably sample code for one in a language that
    will work for you.


    Dennis Williams
    DBA, 40%OCP

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Thursday, October 24, 2002 12:35 PM
    To: Multiple recipients of list ORACLE-L

    I have to create packages that will generate several flat files of data from
    tables that will be sent to other systems to be processed.

    I am looking for ideas on how to ensure data integrity in the flat files.

    For example, the expected record count is stored on the first line of the
    file to ensure that the correct amount of records was received.

    The systems group is chartered to ensure the flat files are correctly FTPed
    between systems, so that's covered.

    I just worry that if "somehow" a flat file is scrambled then the scrambled
    data is loaded into the database, therefore corrupting it.

    At this phase, XML is not an option

    I keep thinking that some sort of CRC should be stored with each line in the
    flat file. And then before the line is loaded into the database, the CRC is
    compared against the generated CRC of the just read line. Has anyone done
    anything like this? Any examples out there?

    Many TIA!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jamadagni, Rajendra at Oct 24, 2002 at 6:46 pm ⇧
    you could also use dbms_utility.get_hash_value ... to compute hash value for
    the whole row and store that as an additional column. Be syre to use the
    same parameters on both sides to compute and test, else it will fail the
    check.

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jamadagni, Rajendra
    INET: Rajendra.Jamadagni_at_espn.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    text/plain attachment: ESPN_Disclaimer.txt
  • Grabowy, Chris at Oct 24, 2002 at 6:49 pm ⇧
    (slapping my forehead) Duh.


    Nice. I like it. Simple but effective. Minimal impact on performance.
    And easy for the other systems to implement.


    You da man, Tom, I don't care what Jared and Rachel said about you...


    Thanks.

    -----Original Message-----
    From: Mercadante, Thomas F
    Sent: Thursday, October 24, 2002 2:14 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Flat file generation integrity ideas...

    Chris,

    have you thought about summing a number column in the record and

    placing this sum in a trailer record? this way, you have a header and
    trailer record which helps you be confident that the whole file made it
    to the target system. and by comparing the sum of the imported records
    with the trailer record, it gives you a better level of confidence that
    things didn't get scrambled.


    I used to do all sorts of these things when file transfer was
    not as good as it is now. the above method is pretty simple, easy to
    do, and pretty accurate.


    hope this helps.


    Tom Mercadante
    Oracle Certified Professional

    -----Original Message-----
    From: Grabowy, Chris
    Sent: Thursday, October 24, 2002 1:35 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Flat file generation integrity ideas...

    I have to create packages that will generate several

    flat files of data from tables that will be sent to other systems to be
    processed.

    I am looking for ideas on how to ensure data integrity
    in the flat files.

    For example, the expected record count is stored on the
    first line of the file to ensure that the correct amount of records was
    received.

    The systems group is chartered to ensure the flat files
    are correctly FTPed between systems, so that's covered.

    I just worry that if "somehow" a flat file is scrambled
    then the scrambled data is loaded into the database, therefore
    corrupting it.

    At this phase, XML is not an option

    I keep thinking that some sort of CRC should be stored
    with each line in the flat file. And then before the line is loaded
    into the database, the CRC is compared against the generated CRC of the
    just read line. Has anyone done anything like this? Any examples out
    there?

    Many TIA!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Grabowy, Chris
    INET: cgrabowy_at_fcg.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Kevin Lange at Oct 24, 2002 at 6:54 pm ⇧
    Chris;


    I would suggest the following:

    Generating Check Sums of the files before they are sent.
    Send the files.
    Generating an after Check Sum and compare.

    Here are the steps I go thru to make sure our Oracle Archive Logs are
    correctly transfered to our Standby Server:


    Zip the Archive Logs into 1 Zip.
    Check Sum the Zip.
    Use the TEST option to make sure the Zip is good.
    RCP the zip file to the standby location.
    RSH a test of the Zip.
    RSH a Check Sum of the Transfered Zip and compare to original value.
    Unzip the file

    With this method you have 2 independent checks.... the Check Sum and the Zip
    Integrity Check.


    -----Original Message-----
    Sent: Thursday, October 24, 2002 12:35 PM
    To: Multiple recipients of list ORACLE-L

    I have to create packages that will generate several flat files of data from
    tables that will be sent to other systems to be processed.

    I am looking for ideas on how to ensure data integrity in the flat files.

    For example, the expected record count is stored on the first line of the
    file to ensure that the correct amount of records was received.

    The systems group is chartered to ensure the flat files are correctly FTPed
    between systems, so that's covered.

    I just worry that if "somehow" a flat file is scrambled then the scrambled
    data is loaded into the database, therefore corrupting it.

    At this phase, XML is not an option

    I keep thinking that some sort of CRC should be stored with each line in the
    flat file. And then before the line is loaded into the database, the CRC is
    compared against the generated CRC of the just read line. Has anyone done
    anything like this? Any examples out there?

    Many TIA!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Kevin Lange
    INET: kgel_at_ppoone.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Grabowy, Chris at Oct 24, 2002 at 7:01 pm ⇧
    I will have to keep those in mind, if I ever get back onto a UNIX
    platform.

    Right now, I'm sticking to Tom's suggestion because I religiously follow
    KISS.

    Thanks!!

    -----Original Message-----
    Sent: Thursday, October 24, 2002 2:19 PM
    To: Multiple recipients of list ORACLE-L

    Chris - I would consider checksums. Many systems have built-in checksum
    functions. On Unix these are cksum and sum. You could probably write a
    simple one that would work on your various systems. Search for checksum
    and somewhere on the Internet is probably sample code for one in a
    language that will work for you.


    Dennis Williams
    DBA, 40%OCP

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Thursday, October 24, 2002 12:35 PM
    To: Multiple recipients of list ORACLE-L

    I have to create packages that will generate several flat files of data
    from tables that will be sent to other systems to be processed.

    I am looking for ideas on how to ensure data integrity in the flat
    files.

    For example, the expected record count is stored on the first line of
    the file to ensure that the correct amount of records was received.

    The systems group is chartered to ensure the flat files are correctly
    FTPed between systems, so that's covered.

    I just worry that if "somehow" a flat file is scrambled then the
    scrambled data is loaded into the database, therefore corrupting it.

    At this phase, XML is not an option

    I keep thinking that some sort of CRC should be stored with each line in
    the flat file. And then before the line is loaded into the database,
    the CRC is compared against the generated CRC of the just read line.
    Has anyone done anything like this? Any examples out there?

    Many TIA!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
    message BODY, include a line containing: UNSUB ORACLE-L (or the name of
    mailing list you want to be removed from). You may also send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Grabowy, Chris
    INET: cgrabowy_at_fcg.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Grabowy, Chris at Oct 24, 2002 at 7:05 pm ⇧
    Yes, Melissa also mentioned this to me. I will have to look into that
    function...along with the million other procedures and functions that
    Oracle has. At this point, Tom's suggestion seems to be the
    simpliest/effective/fastest. Any other suggestions?

    -----Original Message-----
    From: Jamadagni, Rajendra
    Sent: Thursday, October 24, 2002 2:46 PM
    To: Multiple recipients of list ORACLE-L
    Subject: RE: Flat file generation integrity ideas...

    you could also use dbms_utility.get_hash_value ... to compute

    hash value for the whole row and store that as an additional column. Be
    syre to use the same parameters on both sides to compute and test, else
    it will fail the check.

    Raj
    ______________________________________________________
    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that
    of ESPN Inc.
    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Grabowy, Chris
    INET: cgrabowy_at_fcg.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Kevin Lange at Oct 24, 2002 at 7:43 pm ⇧
    Trouble with this is that there is a possibility that a change in the number
    in the file could result in the same sum.


    Then you have different data but your checks says everything is ok.


    Checksums are far more accurate.

    -----Original Message-----
    Sent: Thursday, October 24, 2002 1:49 PM
    To: Multiple recipients of list ORACLE-L

    (slapping my forehead) Duh.


    Nice. I like it. Simple but effective. Minimal impact on performance.
    And easy for the other systems to implement.


    You da man, Tom, I don't care what Jared and Rachel said about you...


    Thanks.

    -----Original Message-----
    Sent: Thursday, October 24, 2002 2:14 PM
    To: Multiple recipients of list ORACLE-L

    Chris,


    have you thought about summing a number column in the record and placing
    this sum in a trailer record? this way, you have a header and trailer
    record which helps you be confident that the whole file made it to the
    target system. and by comparing the sum of the imported records with the
    trailer record, it gives you a better level of confidence that things didn't
    get scrambled.


    I used to do all sorts of these things when file transfer was not as good as
    it is now. the above method is pretty simple, easy to do, and pretty
    accurate.


    hope this helps.


    Tom Mercadante
    Oracle Certified Professional

    -----Original Message-----
    Sent: Thursday, October 24, 2002 1:35 PM
    To: Multiple recipients of list ORACLE-L

    I have to create packages that will generate several flat files of data from
    tables that will be sent to other systems to be processed.

    I am looking for ideas on how to ensure data integrity in the flat files.

    For example, the expected record count is stored on the first line of the
    file to ensure that the correct amount of records was received.

    The systems group is chartered to ensure the flat files are correctly FTPed
    between systems, so that's covered.

    I just worry that if "somehow" a flat file is scrambled then the scrambled
    data is loaded into the database, therefore corrupting it.

    At this phase, XML is not an option

    I keep thinking that some sort of CRC should be stored with each line in the
    flat file. And then before the line is loaded into the database, the CRC is
    compared against the generated CRC of the just read line. Has anyone done
    anything like this? Any examples out there?

    Many TIA!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Kevin Lange
    INET: kgel_at_ppoone.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • DENNIS WILLIAMS at Oct 24, 2002 at 7:51 pm ⇧
    Chris - If Tom's idea provides you the protection you require, with
    convenience to boot, then it sounds like you have a winner. I think all the
    suggestions provide some variety of checksum, a standard computer science
    technique. What are your requirements? Mainly to make sure the file doesn't
    get truncated? To make sure some of the records don't get changed?

    Dennis Williams
    DBA, 40%OCP

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Thursday, October 24, 2002 2:02 PM
    To: Multiple recipients of list ORACLE-L

    I will have to keep those in mind, if I ever get back onto a UNIX
    platform.

    Right now, I'm sticking to Tom's suggestion because I religiously follow
    KISS.

    Thanks!!

    -----Original Message-----
    Sent: Thursday, October 24, 2002 2:19 PM
    To: Multiple recipients of list ORACLE-L

    Chris - I would consider checksums. Many systems have built-in checksum
    functions. On Unix these are cksum and sum. You could probably write a
    simple one that would work on your various systems. Search for checksum
    and somewhere on the Internet is probably sample code for one in a
    language that will work for you.


    Dennis Williams
    DBA, 40%OCP

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Thursday, October 24, 2002 12:35 PM
    To: Multiple recipients of list ORACLE-L

    I have to create packages that will generate several flat files of data
    from tables that will be sent to other systems to be processed.

    I am looking for ideas on how to ensure data integrity in the flat
    files.

    For example, the expected record count is stored on the first line of
    the file to ensure that the correct amount of records was received.

    The systems group is chartered to ensure the flat files are correctly
    FTPed between systems, so that's covered.

    I just worry that if "somehow" a flat file is scrambled then the
    scrambled data is loaded into the database, therefore corrupting it.

    At this phase, XML is not an option

    I keep thinking that some sort of CRC should be stored with each line in
    the flat file. And then before the line is loaded into the database,
    the CRC is compared against the generated CRC of the just read line.
    Has anyone done anything like this? Any examples out there?

    Many TIA!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
    message BODY, include a line containing: UNSUB ORACLE-L (or the name of
    mailing list you want to be removed from). You may also send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Grabowy, Chris
    INET: cgrabowy_at_fcg.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Yechiel Adar at Oct 24, 2002 at 8:14 pm ⇧
    Flat file generation integrity ideas...I do not see how the file can get "scrambled".
    You write it out ok.
    The ftp is guaranteed.
    So what is the problem.

    I will go along with the suggestion to zip it. It saves on the ftp time and also gives you some protection.

    Yechiel Adar
    Mehish

    Original Message -----
    From: Grabowy, Chris
    To: Multiple recipients of list ORACLE-L
    Sent: Thursday, October 24, 2002 7:34 PM
    Subject: Flat file generation integrity ideas...

    I have to create packages that will generate several flat files of data from tables that will be sent to other systems to be processed.

    I am looking for ideas on how to ensure data integrity in the flat files.

    For example, the expected record count is stored on the first line of the file to ensure that the correct amount of records was received.

    The systems group is chartered to ensure the flat files are correctly FTPed between systems, so that's covered.

    I just worry that if "somehow" a flat file is scrambled then the scrambled data is loaded into the database, therefore corrupting it.

    At this phase, XML is not an option

    I keep thinking that some sort of CRC should be stored with each line in the flat file. And then before the line is loaded into the database, the CRC is compared against the generated CRC of the just read line. Has anyone done anything like this? Any examples out there?

    Many TIA!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Yechiel Adar
    INET: adar76_at_inter.net.il

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Grabowy, Chris at Oct 24, 2002 at 10:04 pm ⇧
    Well, it's kinda like saying your backups will never fail. The backup
    script works now, and it has for many months, so I should never worry.
    Right?


    Well I'm paranoid, and Andy Groove is my god. So I have to put in place
    some checks to make sure the data is ok, before I load 1,000s of records
    into my database that could corrupt it.


    Yes, I can implement all the suggestions, but I also have to consider
    the possibility that one of these other systems is sending me a file
    that is just plain wrong, or a file meant for a different system, or an
    old file.


    Thanks everyone!!!

    -----Original Message-----
    From: Yechiel Adar
    Sent: Thursday, October 24, 2002 4:15 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: Flat file generation integrity ideas...

    I do not see how the file can get "scrambled".
    You write it out ok.
    The ftp is guaranteed.
    So what is the problem.

    I will go along with the suggestion to zip it. It saves on the

    ftp time and also gives you some protection.


    Yechiel Adar
    Mehish

    ----- Original Message -----
    From: Grabowy, Chris
    To: Multiple recipients of list ORACLE-L

    Sent: Thursday, October 24, 2002 7:34 PM
    Subject: Flat file generation integrity ideas...

    I have to create packages that will generate several

    flat files of data from tables that will be sent to other systems to be
    processed.

    I am looking for ideas on how to ensure data integrity
    in the flat files.

    For example, the expected record count is stored on the
    first line of the file to ensure that the correct amount of records was
    received.

    The systems group is chartered to ensure the flat files
    are correctly FTPed between systems, so that's covered.

    I just worry that if "somehow" a flat file is scrambled
    then the scrambled data is loaded into the database, therefore
    corrupting it.

    At this phase, XML is not an option

    I keep thinking that some sort of CRC should be stored
    with each line in the flat file. And then before the line is loaded
    into the database, the CRC is compared against the generated CRC of the
    just read line. Has anyone done anything like this? Any examples out
    there?

    Many TIA!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Grabowy, Chris
    INET: cgrabowy_at_fcg.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Tim Gorman at Oct 24, 2002 at 10:24 pm ⇧
    RE: Flat file generation integrity ideas...DBMS_OBFUSCATION_TOOLKIT has an (undocumented) function called MD5 which uses that protocol (MD5) to calculate checksums. Look at $OH/rdbms/admin/dbmsobtk.sql...

    Original Message -----
    From: Jamadagni, Rajendra
    To: Multiple recipients of list ORACLE-L
    Sent: Thursday, October 24, 2002 12:46 PM
    Subject: RE: Flat file generation integrity ideas...

    you could also use dbms_utility.get_hash_value ... to compute hash value for the whole row and store that as an additional column. Be syre to use the same parameters on both sides to compute and test, else it will fail the check.

    Raj

    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
    QOTD: Any clod can have facts, but having an opinion is an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • DENNIS WILLIAMS at Oct 24, 2002 at 10:33 pm ⇧
    Chris - Your comment about the other systems maybe sending you the wrong
    file sparked an idea. XML! Yeah, it has been overhyped, but the basic idea
    is that the file specifies the format. So you could get a file and ensure
    the content conforms to your expectation. I am an XML novice, but I believe
    you can create a DTD that defines the valid contents of an XML document and
    then use that to validate the XML files before you load them into Oracle.
    The biggest downside is that it increases the file size, maybe double. And
    you have to convince the other systems to use XML.

    Dennis Williams
    DBA, 40%OCP

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Thursday, October 24, 2002 5:05 PM
    To: Multiple recipients of list ORACLE-L

    Well, it's kinda like saying your backups will never fail. The backup
    script works now, and it has for many months, so I should never worry.
    Right?


    Well I'm paranoid, and Andy Groove is my god. So I have to put in place
    some checks to make sure the data is ok, before I load 1,000s of records
    into my database that could corrupt it.


    Yes, I can implement all the suggestions, but I also have to consider the
    possibility that one of these other systems is sending me a file that is
    just plain wrong, or a file meant for a different system, or an old file.


    Thanks everyone!!!

    -----Original Message-----
    Sent: Thursday, October 24, 2002 4:15 PM
    To: Multiple recipients of list ORACLE-L

    I do not see how the file can get "scrambled".
    You write it out ok.
    The ftp is guaranteed.
    So what is the problem.


    I will go along with the suggestion to zip it. It saves on the ftp time and
    also gives you some protection.


    Yechiel Adar
    Mehish

    Original Message -----
    To: Multiple recipients of list ORACLE-L
    Sent: Thursday, October 24, 2002 7:34 PM

    I have to create packages that will generate several flat files of data from
    tables that will be sent to other systems to be processed.

    I am looking for ideas on how to ensure data integrity in the flat files.

    For example, the expected record count is stored on the first line of the
    file to ensure that the correct amount of records was received.

    The systems group is chartered to ensure the flat files are correctly FTPed
    between systems, so that's covered.

    I just worry that if "somehow" a flat file is scrambled then the scrambled
    data is loaded into the database, therefore corrupting it.

    At this phase, XML is not an option

    I keep thinking that some sort of CRC should be stored with each line in the
    flat file. And then before the line is loaded into the database, the CRC is
    compared against the generated CRC of the just read line. Has anyone done
    anything like this? Any examples out there?

    Many TIA!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Mark Richard at Oct 25, 2002 at 12:43 am ⇧
    Chris,

    Our favourite footer (at a previous project) was:

    #EOF#...

    Sometimes there were no numerical columns, sometimes 1 and sometimes 12 or
    more. In most cases we were the recipients of the files, so we wrote a
    utility that created the footer based on an input specification. When we
    received a file we would trim the header and footer into separate files,
    then run our footer generation script against the detail records. We then
    used diff to compare our footer with the supplied footer. If we didn't get
    the same result as the source system then something was wrong. Since we
    didn't know and didn't care about how the source system created the
    original footer it also meant that effectively the data was verified twice.

    For our situation the utility was worthwhile - we ended up with over 100
    files using this validation technique from all sorts of source systems and
    in each case we just created a 1 line text file detailing the location of
    the numerical columns and some info or whether the file was fixed width or
    csv.

    cheers,

    Mark

    "Grabowy,
    Chris" To: Multiple recipients of list ORACLE-L
    Subject: RE: Flat file generation integrity ideas...
    Sent by:
    root_at_fatcity.c
    om

    25/10/2002
    05:01
    Please respond
    to ORACLE-L

    I will have to keep those in mind, if I ever get back onto a UNIX
    platform.

    Right now, I'm sticking to Tom's suggestion because I religiously follow
    KISS.

    Thanks!!

    -----Original Message-----
    Sent: Thursday, October 24, 2002 2:19 PM
    To: Multiple recipients of list ORACLE-L

    Chris - I would consider checksums. Many systems have built-in checksum
    functions. On Unix these are cksum and sum. You could probably write a
    simple one that would work on your various systems. Search for checksum
    and somewhere on the Internet is probably sample code for one in a
    language that will work for you.

    Dennis Williams
    DBA, 40%OCP

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Thursday, October 24, 2002 12:35 PM
    To: Multiple recipients of list ORACLE-L

    I have to create packages that will generate several flat files of data
    from tables that will be sent to other systems to be processed.

    I am looking for ideas on how to ensure data integrity in the flat
    files.

    For example, the expected record count is stored on the first line of
    the file to ensure that the correct amount of records was received.

    The systems group is chartered to ensure the flat files are correctly
    FTPed between systems, so that's covered.

    I just worry that if "somehow" a flat file is scrambled then the
    scrambled data is loaded into the database, therefore corrupting it.

    At this phase, XML is not an option

    I keep thinking that some sort of CRC should be stored with each line in
    the flat file. And then before the line is loaded into the database,
    the CRC is compared against the generated CRC of the just read line.
    Has anyone done anything like this? Any examples out there?

    Many TIA!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
    message BODY, include a line containing: UNSUB ORACLE-L (or the name of
    mailing list you want to be removed from). You may also send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Grabowy, Chris
    INET: cgrabowy_at_fcg.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Privileged/Confidential information may be contained in this message.
    If you are not the addressee indicated in this message
    (or responsible for delivery of the message to such person),
    you may not copy or deliver this message to anyone.
    In such case, you should destroy this message and kindly notify the sender
    by reply e-mail or by telephone on (61 3) 9612-6999.
    Please advise immediately if you or your employer does not consent to
    Internet e-mail for messages of this kind.
    Opinions, conclusions and other information in this message
    that do not relate to the official business of
    Transurban City Link Ltd
    shall be understood as neither given nor endorsed by it.
    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mark Richard
    INET: mrichard_at_transurban.com.au

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Mark Richard at Oct 25, 2002 at 12:48 am ⇧
    Unfortunately it is a trust issue...

    Trust me when I say a file can get scrambled. I have seen it happen. In
    our wierdest scenario two received files appeared to be merged into a
    single file - on the source system they had two intact files, on our system
    1.5 files merged into a single file and .5 of a file missing.

    We could never replicate it, we had extensive testing on ftp processes,
    etc, all we know is that it happened and our validation techniques saw it
    and saved us a lot of greif.

    "Yechiel Adar"

    net.il> cc:
    Sent by: Subject: Re: Flat file generation integrity ideas...
    root_at_fatcity.c
    om

    25/10/2002
    06:14
    Please respond
    to ORACLE-L

    I do not see how the file can get "scrambled".
    You write it out ok.
    The ftp is guaranteed.
    So what is the problem.

    I will go along with the suggestion to zip it. It saves on the ftp time and
    also gives you some protection.

    Yechiel Adar
    Mehish

    Original Message -----
    From: Grabowy, Chris
    To: Multiple recipients of list ORACLE-L
    Sent: Thursday, October 24, 2002 7:34 PM
    Subject: Flat file generation integrity ideas...

    I have to create packages that will generate several flat files of data
    from tables that will be sent to other systems to be processed.

    I am looking for ideas on how to ensure data integrity in the flat files.

    For example, the expected record count is stored on the first line of the
    file to ensure that the correct amount of records was received.

    The systems group is chartered to ensure the flat files are correctly
    FTPed between systems, so that's covered.

    I just worry that if "somehow" a flat file is scrambled then the scrambled
    data is loaded into the database, therefore corrupting it.

    At this phase, XML is not an option

    I keep thinking that some sort of CRC should be stored with each line in
    the flat file. And then before the line is loaded into the database, the
    CRC is compared against the generated CRC of the just read line. Has
    anyone done anything like this? Any examples out there?

    Many TIA!!

    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    Privileged/Confidential information may be contained in this message.

    If you are not the addressee indicated in this message
    (or responsible for delivery of the message to such person),
    you may not copy or deliver this message to anyone.
    In such case, you should destroy this message and kindly notify the sender
    by reply e-mail or by telephone on (61 3) 9612-6999.
    Please advise immediately if you or your employer does not consent to
    Internet e-mail for messages of this kind.
    Opinions, conclusions and other information in this message
    that do not relate to the official business of
    Transurban City Link Ltd
    shall be understood as neither given nor endorsed by it.
    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mark Richard
    INET: mrichard_at_transurban.com.au

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Grabowy, Chris at Oct 25, 2002 at 1:04 pm ⇧
    Yes, which is the ideal solution, but the other systems aren't ready for
    XML.

    Anytime we have a really good solution it's "defered to the next phase".

    -----Original Message-----
    Sent: Thursday, October 24, 2002 6:33 PM
    To: Multiple recipients of list ORACLE-L

    Chris - Your comment about the other systems maybe sending you the wrong
    file sparked an idea. XML! Yeah, it has been overhyped, but the basic
    idea is that the file specifies the format. So you could get a file and
    ensure the content conforms to your expectation. I am an XML novice, but
    I believe you can create a DTD that defines the valid contents of an XML
    document and then use that to validate the XML files before you load
    them into Oracle. The biggest downside is that it increases the file
    size, maybe double. And you have to convince the other systems to use
    XML.

    Dennis Williams
    DBA, 40%OCP

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Thursday, October 24, 2002 5:05 PM
    To: Multiple recipients of list ORACLE-L

    Well, it's kinda like saying your backups will never fail. The backup
    script works now, and it has for many months, so I should never worry.
    Right?


    Well I'm paranoid, and Andy Groove is my god. So I have to put in place
    some checks to make sure the data is ok, before I load 1,000s of records
    into my database that could corrupt it.


    Yes, I can implement all the suggestions, but I also have to consider
    the possibility that one of these other systems is sending me a file
    that is just plain wrong, or a file meant for a different system, or an
    old file.


    Thanks everyone!!!

    -----Original Message-----
    Sent: Thursday, October 24, 2002 4:15 PM
    To: Multiple recipients of list ORACLE-L

    I do not see how the file can get "scrambled".
    You write it out ok.
    The ftp is guaranteed.
    So what is the problem.


    I will go along with the suggestion to zip it. It saves on the ftp time
    and also gives you some protection.


    Yechiel Adar
    Mehish

    Original Message -----
    To: Multiple recipients of list ORACLE-L
    Sent: Thursday, October 24, 2002 7:34 PM

    I have to create packages that will generate several flat files of data
    from tables that will be sent to other systems to be processed.

    I am looking for ideas on how to ensure data integrity in the flat
    files.

    For example, the expected record count is stored on the first line of
    the file to ensure that the correct amount of records was received.

    The systems group is chartered to ensure the flat files are correctly
    FTPed between systems, so that's covered.

    I just worry that if "somehow" a flat file is scrambled then the
    scrambled data is loaded into the database, therefore corrupting it.

    At this phase, XML is not an option

    I keep thinking that some sort of CRC should be stored with each line in
    the flat file. And then before the line is loaded into the database,
    the CRC is compared against the generated CRC of the just read line.
    Has anyone done anything like this? Any examples out there?

    Many TIA!!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
    message BODY, include a line containing: UNSUB ORACLE-L (or the name of
    mailing list you want to be removed from). You may also send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Grabowy, Chris
    INET: cgrabowy_at_fcg.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Grabowy, Chris at Oct 25, 2002 at 1:04 pm ⇧
    Tim, I can always count on you for a slick answer. Thanks. I will have
    to check into that.

    -----Original Message-----
    From: Tim Gorman
    Sent: Thursday, October 24, 2002 6:24 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: Flat file generation integrity ideas...

    DBMS_OBFUSCATION_TOOLKIT has an (undocumented) function called

    MD5 which uses that protocol (MD5) to calculate checksums. Look at
    $OH/rdbms/admin/dbmsobtk.sql...

    Original Message -----
    From: Jamadagni, Rajendra

    To: Multiple recipients of list ORACLE-L

    Sent: Thursday, October 24, 2002 12:46 PM
    Subject: RE: Flat file generation integrity ideas...

    you could also use dbms_utility.get_hash_value ... to
    compute hash value for the whole row and store that as an additional
    column. Be syre to use the same parameters on both sides to compute and
    test, else it will fail the check.

    Raj
    ______________________________________________________
    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't
    reflect that of ESPN Inc.
    QOTD: Any clod can have facts, but having an opinion is

    an art!

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Grabowy, Chris
    INET: cgrabowy_at_fcg.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jared Still at Oct 27, 2002 at 3:48 am ⇧
    I was going to suggest MD5 as well. Though I was thinking of the Perl version.

    The DBMS_OBFUSCATION version is fine, but I was thinking of the unload
    method.

    Chris, how are you creating the flat files? UTL_FILE? SqlPlus?

    Perl is much faster than sqlplus, dunno about UTL_FILE, as I haven't
    compared them for speed. (yet)

    PL/SQL also requires about 10x more code for this kind of thing than Perl.

    You *could* just modify the sqlunldr.pl script in "Perl for Oracle DBA's" to
    add MD5 checksums at the beginning of the line. You could also easily\
    MD5 the entire file in one line of code.

    If you're using C, well, you're just working too hard.;)

    Jared
    On Thursday 24 October 2002 15:24, Tim Gorman wrote:
    RE: Flat file generation integrity ideas...DBMS_OBFUSCATION_TOOLKIT has an
    (undocumented) function called MD5 which uses that protocol (MD5) to
    calculate checksums. Look at $OH/rdbms/admin/dbmsobtk.sql... -----
    Original Message -----
    From: Jamadagni, Rajendra
    To: Multiple recipients of list ORACLE-L
    Sent: Thursday, October 24, 2002 12:46 PM
    Subject: RE: Flat file generation integrity ideas...


    you could also use dbms_utility.get_hash_value ... to compute hash value
    for the whole row and store that as an additional column. Be syre to use
    the same parameters on both sides to compute and test, else it will fail
    the check.

    Raj
    ______________________________________________________
    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN
    Inc. QOTD: Any clod can have facts, but having an opinion is an art!
    Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
    Content-Transfer-Encoding: quoted-printable
    Content-Description:
    ----------------------------------------

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jared Still
    INET: jkstill_at_cybcon.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Grabowy, Chris at Oct 27, 2002 at 3:59 am ⇧
    Hey Jared,


    The current thinking is using a PL/SQL package to create the external flat file.


    And I don't have proof, but I am absolutely positive that using UTL_FILE is faster then Perl...I just know it is....it's gotta be.......


    I will take a look at sqlunldr.pl, thanks.


    CG


    -----Original Message-----
    From: Jared Still
    Sent: Sat 10/26/2002 10:45 PM
    To: ORACLE-L_at_fatcity.com; Tim Gorman; Grabowy, Chris
    Cc:
    Subject: Re: Flat file generation integrity ideas...

    I was going to suggest MD5 as well. Though I was thinking of the Perl version.

    The DBMS_OBFUSCATION version is fine, but I was thinking of the unload
    method.

    Chris, how are you creating the flat files? UTL_FILE? SqlPlus?

    Perl is much faster than sqlplus, dunno about UTL_FILE, as I haven't
    compared them for speed. (yet)

    PL/SQL also requires about 10x more code for this kind of thing than Perl.

    You *could* just modify the sqlunldr.pl script in "Perl for Oracle DBA's" to
    add MD5 checksums at the beginning of the line. You could also easily\
    MD5 the entire file in one line of code.

    If you're using C, well, you're just working too hard.;)

    Jared
    On Thursday 24 October 2002 15:24, Tim Gorman wrote:
    RE: Flat file generation integrity ideas...DBMS_OBFUSCATION_TOOLKIT has an
    (undocumented) function called MD5 which uses that protocol (MD5) to
    calculate checksums. Look at $OH/rdbms/admin/dbmsobtk.sql... -----
    Original Message -----
    From: Jamadagni, Rajendra
    To: Multiple recipients of list ORACLE-L
    Sent: Thursday, October 24, 2002 12:46 PM
    Subject: RE: Flat file generation integrity ideas...
    >
    >
    you could also use dbms_utility.get_hash_value ... to compute hash value
    for the whole row and store that as an additional column. Be syre to use
    the same parameters on both sides to compute and test, else it will fail
    the check. >
    Raj
    ______________________________________________________
    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of ESPN
    Inc. QOTD: Any clod can have facts, but having an opinion is an art!
    ----------------------------------------
    Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
    Content-Transfer-Encoding: quoted-printable
    Content-Description:
    ----------------------------------------

    ÿóåy«±ç­…ê~'"jS‘
    â°ƒDO÷ ­¦èÃ/ßrÜ¢aZ´(­È׭ŠäIêï‰Ç¬ÿÿ9óþwóþtçXm¶Ÿÿÿ 0ýö­r+rýÊ&I©Ã‰è(ü&¥‰ú+ž&ÿüÆ¢–)à–+-jwpy¸h²Ø§‚Ç«¾'³ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿôèDCTL¨º»•÷ë¢kaŠÉšŠX§‚X¬·ûÖ§óŠYž²Æ zÚÿ.+-êîýö­r+rýÊ&þz-xEÀ;)zYbž

    ü¸¬´k«»ÿڝا¶¦zˁàN
    âÉnu楊wœ¢{ZŠx§ƒõ
    I_at_ND
  • Jared Still at Oct 27, 2002 at 4:38 am ⇧

    And I don't have proof, but I am absolutely positive that using UTL_FILE is
    faster then Perl...I just know it is....it's gotta be.......
    :)

    I'm currently working on a little project for someone to create a table
    unloader in PL/SQL. Before you ask, I can't share on this one, it's
    proprietary. And it has to be done from the database. :(

    A limitation of UTL_FILE is that the output line length is limited to 32767
    characters. 32768 including the LF.

    Some problems with unloading in PL/SQL are obvious:

    What if the line is longer than 32767? I'm going to create continuation
    lines in the file. An external utility will need to be run to 'correct' these
    lines.

    What if there is a LONG column. Ugh. These can be dealt with, but it
    ain't pretty. Think global temporary tables and the TO_LOB SQL function
    to convert them to a LOB, so that DBMS_LOB may be used to read them.

    I'm going to allow NCLOBS, CLOBS only. No BLOB or LONG RAW columns.
    I don't know of any way to load these from sqlldr if the column length
    exceeds 32767. ( via utl_raw package )

    Of course, none of this may be a problem with your data.

    Jared
    I will take a look at sqlunldr.pl, thanks.

    CG


    -----Original Message-----
    From: Jared Still
    Sent: Sat 10/26/2002 10:45 PM
    To: ORACLE-L_at_fatcity.com; Tim Gorman; Grabowy, Chris
    Cc:
    Subject: Re: Flat file generation integrity ideas...




    I was going to suggest MD5 as well. Though I was thinking of the Perl
    version.

    The DBMS_OBFUSCATION version is fine, but I was thinking of the unload
    method.

    Chris, how are you creating the flat files? UTL_FILE? SqlPlus?

    Perl is much faster than sqlplus, dunno about UTL_FILE, as I haven't
    compared them for speed. (yet)

    PL/SQL also requires about 10x more code for this kind of thing than Perl.

    You *could* just modify the sqlunldr.pl script in "Perl for Oracle DBA's"
    to add MD5 checksums at the beginning of the line. You could also easily\
    MD5 the entire file in one line of code.

    If you're using C, well, you're just working too hard.;)

    Jared
    On Thursday 24 October 2002 15:24, Tim Gorman wrote:
    RE: Flat file generation integrity ideas...DBMS_OBFUSCATION_TOOLKIT has
    an (undocumented) function called MD5 which uses that protocol (MD5) to
    calculate checksums. Look at $OH/rdbms/admin/dbmsobtk.sql... -----
    Original Message -----
    From: Jamadagni, Rajendra
    To: Multiple recipients of list ORACLE-L
    Sent: Thursday, October 24, 2002 12:46 PM
    Subject: RE: Flat file generation integrity ideas...


    you could also use dbms_utility.get_hash_value ... to compute hash
    value for the whole row and store that as an additional column. Be syre
    to use the same parameters on both sides to compute and test, else it
    will fail the check.

    Raj
    ______________________________________________________
    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of
    ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an
    art!
    ----------------------------------------
    Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
    Content-Transfer-Encoding: quoted-printable
    Content-Description:
    ----------------------------------------
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jared Still
    INET: jkstill_at_cybcon.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jay Wade at Oct 27, 2002 at 9:53 pm ⇧
    Just an idea but how about using Java Stored Procedures? They would allow
    better control over OS files. Also they would be housed within the
    database. Combine this with some Oracle XML technology your work could be
    made alittle easier. Although memory intensive.
    From: Jared Still
    Reply-To: ORACLE-L_at_fatcity.com
    To: Multiple recipients of list ORACLE-L
    Subject: Re: Flat file generation integrity ideas...
    Date: Sat, 26 Oct 2002 20:38:30 -0800

    And I don't have proof, but I am absolutely positive that using UTL_FILE is
    faster then Perl...I just know it is....it's gotta be.......
    :)

    I'm currently working on a little project for someone to create a table
    unloader in PL/SQL. Before you ask, I can't share on this one, it's
    proprietary. And it has to be done from the database. :(

    A limitation of UTL_FILE is that the output line length is limited to 32767
    characters. 32768 including the LF.

    Some problems with unloading in PL/SQL are obvious:

    What if the line is longer than 32767? I'm going to create continuation
    lines in the file. An external utility will need to be run to 'correct'
    these
    lines.

    What if there is a LONG column. Ugh. These can be dealt with, but it
    ain't pretty. Think global temporary tables and the TO_LOB SQL function
    to convert them to a LOB, so that DBMS_LOB may be used to read them.

    I'm going to allow NCLOBS, CLOBS only. No BLOB or LONG RAW columns.
    I don't know of any way to load these from sqlldr if the column length
    exceeds 32767. ( via utl_raw package )

    Of course, none of this may be a problem with your data.

    Jared



    I will take a look at sqlunldr.pl, thanks.

    CG


    -----Original Message-----
    From: Jared Still
    Sent: Sat 10/26/2002 10:45 PM
    To: ORACLE-L_at_fatcity.com; Tim Gorman; Grabowy, Chris
    Cc:
    Subject: Re: Flat file generation integrity ideas...




    I was going to suggest MD5 as well. Though I was thinking of the Perl
    version.

    The DBMS_OBFUSCATION version is fine, but I was thinking of the unload
    method.

    Chris, how are you creating the flat files? UTL_FILE? SqlPlus?

    Perl is much faster than sqlplus, dunno about UTL_FILE, as I haven't
    compared them for speed. (yet)

    PL/SQL also requires about 10x more code for this kind of thing than Perl.
    You *could* just modify the sqlunldr.pl script in "Perl for Oracle DBA's"
    to add MD5 checksums at the beginning of the line. You could also easily\
    MD5 the entire file in one line of code.

    If you're using C, well, you're just working too hard.;)

    Jared
    On Thursday 24 October 2002 15:24, Tim Gorman wrote:
    RE: Flat file generation integrity ideas...DBMS_OBFUSCATION_TOOLKIT
    has
    an (undocumented) function called MD5 which uses that protocol (MD5)
    to
    calculate checksums. Look at $OH/rdbms/admin/dbmsobtk.sql... -----
    Original Message -----
    From: Jamadagni, Rajendra
    To: Multiple recipients of list ORACLE-L
    Sent: Thursday, October 24, 2002 12:46 PM
    Subject: RE: Flat file generation integrity ideas...


    you could also use dbms_utility.get_hash_value ... to compute hash
    value for the whole row and store that as an additional column. Be
    syre
    to use the same parameters on both sides to compute and test, else it
    will fail the check.

    Raj
    ______________________________________________________
    Rajendra Jamadagni MIS, ESPN Inc.
    Rajendra dot Jamadagni at ESPN dot com
    Any opinion expressed here is personal and doesn't reflect that of
    ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an
    art!
    ----------------------------------------
    Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
    Content-Transfer-Encoding: quoted-printable
    Content-Description:
    ----------------------------------------
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jared Still
    INET: jkstill_at_cybcon.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    Internet access plans that fit your lifestyle -- join MSN.
    http://resourcecenter.msn.com/access/plans/default.asp

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jay Wade
    INET: fish_dba_at_hotmail.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Yechiel Adar at Oct 28, 2002 at 4:54 pm ⇧
    But he was talking about sending, not receiving.
    and he says that the ftp is assured to work ok.

    Yechiel Adar
    Mehish
    ----- Original Message -----
    To: Multiple recipients of list ORACLE-L
    Sent: Friday, October 25, 2002 2:48 AM
    Unfortunately it is a trust issue...

    Trust me when I say a file can get scrambled. I have seen it happen. In
    our wierdest scenario two received files appeared to be merged into a
    single file - on the source system they had two intact files, on our system
    1.5 files merged into a single file and .5 of a file missing.

    We could never replicate it, we had extensive testing on ftp processes,
    etc, all we know is that it happened and our validation techniques saw it
    and saved us a lot of greif.





    "Yechiel Adar"
    list ORACLE-L
    net.il> cc:
    Sent by: Subject: Re: Flat file
    generation integrity ideas...
    root_at_fatcity.c
    om


    25/10/2002
    06:14
    Please respond
    to ORACLE-L






    I do not see how the file can get "scrambled".
    You write it out ok.
    The ftp is guaranteed.
    So what is the problem.

    I will go along with the suggestion to zip it. It saves on the ftp time and
    also gives you some protection.

    Yechiel Adar
    Mehish
    ----- Original Message -----
    From: Grabowy, Chris
    To: Multiple recipients of list ORACLE-L
    Sent: Thursday, October 24, 2002 7:34 PM
    Subject: Flat file generation integrity ideas...



    I have to create packages that will generate several flat files of data
    from tables that will be sent to other systems to be processed.


    I am looking for ideas on how to ensure data integrity in the flat files.


    For example, the expected record count is stored on the first line of the
    file to ensure that the correct amount of records was received.


    The systems group is chartered to ensure the flat files are correctly
    FTPed between systems, so that's covered.


    I just worry that if "somehow" a flat file is scrambled then the scrambled
    data is loaded into the database, therefore corrupting it.


    At this phase, XML is not an option


    I keep thinking that some sort of CRC should be stored with each line in
    the flat file. And then before the line is loaded into the database, the
    CRC is compared against the generated CRC of the just read line. Has
    anyone done anything like this? Any examples out there?


    Many TIA!!






    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Privileged/Confidential information may be contained in this message.
    If you are not the addressee indicated in this message
    (or responsible for delivery of the message to such person),
    you may not copy or deliver this message to anyone.
    In such case, you should destroy this message and kindly notify the sender
    by reply e-mail or by telephone on (61 3) 9612-6999.
    Please advise immediately if you or your employer does not consent to
    Internet e-mail for messages of this kind.
    Opinions, conclusions and other information in this message
    that do not relate to the official business of
    Transurban City Link Ltd
    shall be understood as neither given nor endorsed by it.
    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mark Richard
    INET: mrichard_at_transurban.com.au

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Yechiel Adar
    INET: adar76_at_inter.net.il

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Grabowy, Chris at Oct 29, 2002 at 4:19 pm ⇧
    Actually, I am sending and receiving files.

    They will be handling the FTP of the files, and making sure it has FTPed
    correctly.

    I just have to have a sanity check of the file. Basically, I decided to
    prefix each data line with 'DAT', and the "CRC line" with 'CRC'. The
    flat file is "read into the database" via an external table. I query
    the CRC record, get the expected record count and then count how many
    rows were actually sent. And then if there is a number column, then I
    sum that up and check it against the "CRC" expected sum.

    Perhaps all this is overkill, but I know that the odds of data
    corruption are slim to none. I don't like making assumptions, and I
    can't assume the file is ok.

    Many thanks to everyone that responded!!!

    -----Original Message-----
    Sent: Monday, October 28, 2002 11:54 AM
    To: Multiple recipients of list ORACLE-L

    But he was talking about sending, not receiving.
    and he says that the ftp is assured to work ok.

    Yechiel Adar
    Mehish
    ----- Original Message -----
    To: Multiple recipients of list ORACLE-L
    Sent: Friday, October 25, 2002 2:48 AM
    Unfortunately it is a trust issue...

    Trust me when I say a file can get scrambled. I have seen it happen.
    In our wierdest scenario two received files appeared to be merged into
    a single file - on the source system they had two intact files, on our system
    1.5 files merged into a single file and .5 of a file missing.

    We could never replicate it, we had extensive testing on ftp
    processes, etc, all we know is that it happened and our validation
    techniques saw it and saved us a lot of greif.





    "Yechiel Adar"
    of
    list ORACLE-L
    net.il> cc:
    Sent by: Subject: Re: Flat file
    generation integrity ideas...
    root_at_fatcity.c
    om


    25/10/2002
    06:14
    Please respond
    to ORACLE-L






    I do not see how the file can get "scrambled".
    You write it out ok.
    The ftp is guaranteed.
    So what is the problem.

    I will go along with the suggestion to zip it. It saves on the ftp
    time and
    also gives you some protection.

    Yechiel Adar
    Mehish
    ----- Original Message -----
    From: Grabowy, Chris
    To: Multiple recipients of list ORACLE-L
    Sent: Thursday, October 24, 2002 7:34 PM
    Subject: Flat file generation integrity ideas...



    I have to create packages that will generate several flat files of
    data from tables that will be sent to other systems to be processed.


    I am looking for ideas on how to ensure data integrity in the flat
    files.


    For example, the expected record count is stored on the first line of
    the file to ensure that the correct amount of records was received.


    The systems group is chartered to ensure the flat files are correctly
    FTPed between systems, so that's covered.


    I just worry that if "somehow" a flat file is scrambled then the scrambled
    data is loaded into the database, therefore corrupting it.


    At this phase, XML is not an option


    I keep thinking that some sort of CRC should be stored with each line
    in the flat file. And then before the line is loaded into the
    database, the CRC is compared against the generated CRC of the just
    read line. Has anyone done anything like this? Any examples out
    there?


    Many TIA!!






    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Privileged/Confidential information may be contained in this message.
    If you are not the addressee indicated in this message
    (or responsible for delivery of the message to such person),
    you may not copy or deliver this message to anyone. In
    such case, you should destroy this message and kindly notify the sender
    by reply e-mail or by telephone on (61 3) 9612-6999.
    Please advise immediately if you or your employer does not consent to
    Internet e-mail for messages of this kind.
    Opinions, conclusions and other information in this message
    that do not relate to the official business of
    Transurban City Link Ltd
    shall be understood as neither given nor endorsed by it.
    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mark Richard
    INET: mrichard_at_transurban.com.au

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Yechiel Adar
    INET: adar76_at_inter.net.il

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
    message BODY, include a line containing: UNSUB ORACLE-L (or the name of
    mailing list you want to be removed from). You may also send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Grabowy, Chris
    INET: cgrabowy_at_fcg.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Yechiel Adar at Oct 30, 2002 at 3:54 pm ⇧
    When you get files from an external source no amount of checking is enough.

    Yechiel Adar
    Mehish
    ----- Original Message -----
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 29, 2002 6:19 PM

    Actually, I am sending and receiving files.

    They will be handling the FTP of the files, and making sure it has FTPed
    correctly.

    I just have to have a sanity check of the file. Basically, I decided to
    prefix each data line with 'DAT', and the "CRC line" with 'CRC'. The
    flat file is "read into the database" via an external table. I query
    the CRC record, get the expected record count and then count how many
    rows were actually sent. And then if there is a number column, then I
    sum that up and check it against the "CRC" expected sum.

    Perhaps all this is overkill, but I know that the odds of data
    corruption are slim to none. I don't like making assumptions, and I
    can't assume the file is ok.

    Many thanks to everyone that responded!!!

    -----Original Message-----
    Sent: Monday, October 28, 2002 11:54 AM
    To: Multiple recipients of list ORACLE-L

    But he was talking about sending, not receiving.
    and he says that the ftp is assured to work ok.

    Yechiel Adar
    Mehish
    ----- Original Message -----
    To: Multiple recipients of list ORACLE-L
    Sent: Friday, October 25, 2002 2:48 AM
    Unfortunately it is a trust issue...

    Trust me when I say a file can get scrambled. I have seen it happen.
    In our wierdest scenario two received files appeared to be merged into
    a single file - on the source system they had two intact files, on our system
    1.5 files merged into a single file and .5 of a file missing.

    We could never replicate it, we had extensive testing on ftp
    processes, etc, all we know is that it happened and our validation
    techniques saw it and saved us a lot of greif.





    "Yechiel Adar"
    of
    list ORACLE-L
    net.il> cc:
    Sent by: Subject: Re: Flat file
    generation integrity ideas...
    root_at_fatcity.c
    om


    25/10/2002
    06:14
    Please respond
    to ORACLE-L






    I do not see how the file can get "scrambled".
    You write it out ok.
    The ftp is guaranteed.
    So what is the problem.

    I will go along with the suggestion to zip it. It saves on the ftp
    time and
    also gives you some protection.

    Yechiel Adar
    Mehish
    ----- Original Message -----
    From: Grabowy, Chris
    To: Multiple recipients of list ORACLE-L
    Sent: Thursday, October 24, 2002 7:34 PM
    Subject: Flat file generation integrity ideas...



    I have to create packages that will generate several flat files of
    data from tables that will be sent to other systems to be processed.


    I am looking for ideas on how to ensure data integrity in the flat
    files.


    For example, the expected record count is stored on the first line of
    the file to ensure that the correct amount of records was received.


    The systems group is chartered to ensure the flat files are correctly
    FTPed between systems, so that's covered.


    I just worry that if "somehow" a flat file is scrambled then the scrambled
    data is loaded into the database, therefore corrupting it.


    At this phase, XML is not an option


    I keep thinking that some sort of CRC should be stored with each line
    in the flat file. And then before the line is loaded into the
    database, the CRC is compared against the generated CRC of the just
    read line. Has anyone done anything like this? Any examples out
    there?


    Many TIA!!






    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Privileged/Confidential information may be contained in this message.
    If you are not the addressee indicated in this message
    (or responsible for delivery of the message to such person),
    you may not copy or deliver this message to anyone. In
    such case, you should destroy this message and kindly notify the sender
    by reply e-mail or by telephone on (61 3) 9612-6999.
    Please advise immediately if you or your employer does not consent to
    Internet e-mail for messages of this kind.
    Opinions, conclusions and other information in this message
    that do not relate to the official business of
    Transurban City Link Ltd
    shall be understood as neither given nor endorsed by it.
    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mark Richard
    INET: mrichard_at_transurban.com.au

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L (or the
    name of mailing list you want to be removed from). You may also send
    the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Yechiel Adar
    INET: adar76_at_inter.net.il

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the
    message BODY, include a line containing: UNSUB ORACLE-L (or the name of
    mailing list you want to be removed from). You may also send the HELP
    command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Grabowy, Chris
    INET: cgrabowy_at_fcg.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Yechiel Adar
    INET: adar76_at_inter.net.il

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 24, '02 at 5:34p
activeOct 30, '02 at 3:54p
posts25
users10
websiteoracle.com

10 users in discussion

Grabowy, Chris: 9 posts Yechiel Adar: 3 posts DENNIS WILLIAMS: 3 posts Jared Still: 2 posts Mark Richard: 2 posts Kevin Lange: 2 posts Mercadante, Thomas F: 1 post Jay Wade: 1 post Jamadagni, Rajendra: 1 post Tim Gorman: 1 post

Content

People

Support

Translate

site design / logo © 2022 Grokbase