Re: Oracle 7 and EDI

From: Mike Nolan <nolan_at_gw.tssi.com>
Date: 1998/07/08
Message-ID: <6nulgj$g6k$1_at_gw.tssi.com>#1/1


jared_at_pandora.planet.net (Jared Hecker) writes:

:Last I looked, EDI formats (whatever standard you use) is a fixed file
:format. Use a SQL*Plus script.
 

:Matthew MacFarland (matthew_mac&*(^%farland_at_dril-quip.com) wrote:
:: Hello
 

:: Does anyone have experience getting Oracle table data into EDI format? What
:: is a good program to handle the translation?

If you're serious about EDI, you will want to look into getting a commercial EDI translator. Some of these can go direct to/from Oracle table structures and the corresponding EDI files, others go from the EDI format to/from a flat file, meaning yu have to write other programs to create or process those flat files with Oracle.

The X12 standard does not require carriage returns or line feeds between elements, segments, or even transaction sets, and most individual elements are variable length, so it is NOT fixed file format in the conventional sense of the word, nor would it necessarily be a 'text' file.

EDIFACT uses similar trading partner defined separator conventions.

Further, the intricacies of making sure that X12 transaction sets are complete, consistent and valid is a LOT of work!

A relatively basic translator may run $10,000 or more, plus annual maintenance, but that is generally a small part of the cost of implementing EDI, and a small part of the potential savings involved, as well. (Especially if you have trading partners who say 'USE EDI OR LOSE OUR BUSINESS!') We started out writing simple input/output programs to create fixed format EDI transaction sets, but when our industry (publishing) moved over to X12, I wrote the first version of a translator in PERL and decided that the work necessary to keep a half dozen or more transaction sets consistent at multiple standard levels for different trading partners (sometimes there are different standard levels WITHIN a single customer) was more work than I felt like doing.

We now use a fixed format intermediate flat file for output from Oracle or input into Oracle, and have a translator that does all the transaction set validity checking and handles the generation of functional acknowledgements. (I will not mention our translator by name, because we've got their old Windows-based product and I don't know that it is representative of their more current products, but I don't have the time or budget to upgrade or switch to a different translator.)

I am comfortable with the intermediate flat file approach because there are reasons why we might not want to load a particular transaction set into Oracle immediately after it is received. Some of these (such as a delayed shipping date or a need to parse the transaction set for compliance with our sales policies) could be gotten around by using a different table for 'unprocessed' transaction sets, but since our translator doesn't talk directly to Oracle anyway, it isn't something we can do.

--
Mike Nolan

PS.  I am a member of several Book Industry Systems Advisory Committee 
(BISAC) X12 EDI standards subcommittees, including the Technical Advisory
Group, which tries to make sure that each implementation guide we prepare
conforms to the applicable ANSI X12 standards, and also tries to anticipate
changes that will be needed for later standards or the upcoming industry
move to EDIFACT.
Received on Wed Jul 08 1998 - 00:00:00 CEST

Original text of this message