PL/SQL for parsing EDI (X12, NCPDP, HL7)

Question

I have some EDI messages (X12, HL7, etc ...) stored in an Oracle database. I sometimes want to pull out individual fields (e.g. ISA-03). Currently, I have some really ugly sql. I'd like to create a PL/SQL package to make it easier and was wondering if anybody had already done this.

I imagine something like:

select edi.x12.extract_field( clob_column, 'ISA', 4) from edi_table

Answer 1

While I never stored the HL7 message as is in a database it should be possible.

The idea of HL7 (and XML) is that it's a common format for systems to use to transfer information. It was never designed as a "storable" item. Usually, I would pull the data out of the warehouse format into a particular HL7 message and send it to the MQHub/eGate for transmitting. On the return do the opposite extract the fields I'm warehousing and save those. I.E. HL7 should not be stored so I don't have one.

Enough of the lecture. :)

I would suggest a function/procedure per segment and split the message into a temp table.

example of split in oracle