Writing Master Detail Files
By David Allan-Oracle on Aug 10, 2009
I've seen this question a few times about how to write a master detail file using OWB, so thought a quick post was in order. Here you'll see a technique for writing such files and using the union all and sorter operator to process the records in order. The OWB flat file operator lets you write a single record at a time, so you have to be creative to use in the multi-record master-detail case.
The example we'll look at is writing a master detail set of records representing the EMP and DEPT tables from the SCOTT schema. So the DEPT records will be our master records, and the EMP records the detail.
The mapping will perform a UNION ALL on the columns from EMP and DEPT, and then sort the columns by DEPTNO and DEPTNAME in order to group the DEPT and EMP records together, then we will have an expression which will result in a single VARCHAR2 which is then written to the target file.
The set operation as mentioned above performs a UNION ALL on the EMP AND DEPT records, the sorter sorts based on DEPTNO and DNAME (so we get the DEPT records interspersed with the EMP records).
The sort operation as mentioned includes both the DEPTNO and DNAME columns;
The expression results in a single string with the delimiters concatenated into the string, this is the 'record'. Obviously if you had a much larger record size you would need to chunk the expressions.
This results in a file like the following with each department and associated employees immediately following;
Hopefully this is useful and may inspire some more ideas and thoughts!