Generate a Flat File from a View in Oracle DB

Question

We have a view in the Oracle Database which looks like

HEADER,P,Lab,16-Dec-16,MEMBER,1,134,134,1,5,BENEFIT,1,MEA,1,TRAILER,          ,20161216,14:08:51
HEADER,P,Lab,16-Dec-16,MEMBER,1,134,134,1,5,BENEFIT,1,DEN,2,TRAILER,          ,20161216,14:08:51
HEADER,P,Lab,16-Dec-16,MEMBER,2,572,572,2,5,BENEFIT,2,DEN,2,TRAILER,          ,20161216,14:08:51
HEADER,P,Lab,16-Dec-16,MEMBER,2,572,572,2,5,BENEFIT,2,MEA,1,TRAILER,          ,20161216,14:08:51

Each record has header which keeps repeating and has the same information. Then the each MEMBER has 'n' no.of Benefits example

Member (Employee ID -1)

 MEMBER,1,134,134,1,5

has benefits (for the Employee ID-1)

BENEFIT,1,MEA,1
BENEFIT,1,MEA,1

I am trying to generate the flat file like

HEADER,P,Lab,16-Dec-16   //--- Header
MEMBER,1,134,134,1,5    //--- Member (Employee ID -1)
BENEFIT,1,MEA,1        //--- Benefits (Employee ID -1)
BENEFIT,1,DEN,2       // --- Benefits (Employee ID -1)
MEMBER,2,572,572,2,5 //--- Member (Employee ID -2)
BENEFIT,2,DEN,2     //--- Benefits (Employee ID -2)
BENEFIT,2,MEA,1    // --- Benefits (Employee ID -2)
TRAILER,Total no.of lines,20161216,14:08:51 // Trailer

I am not sure how to create the schema for the output file. Do I have to create create separate schema's for Header Member Benefits and Trailer. Or create one schema with multiple records for Header Member Benefits and Trailer. Iam not sure how to proceed. Any help with this is greatly appreciated

Answer 1

You can have a Member table with employeeid and memberid and then Benefits table with Benefits id and memberid and have a primary key foreign key relationship to join all of them together.

Hopefully that works.