Creating text files with bursting

For the customer i had to create a solution which generated PDF files out of the data of the appraisal (HR) module. These files need to be imported into another system so another requirement was the fact that meta-data has to be provided. The meta-data needs to be provided as a flat text-file.

The first thought that sprang to my mind was: “Let’s use the bursting engine to accomplish this“.

However this turned out to be more of a challenge than expected.

The PDF file generation was quite straightforward. Just create the template to be used and create the bursting control file. The text-file however turned out to be a nasty little fellow.

I created the XSL stylesheet which should translate the data into a text file, tested this and it seemed to work fine. When used with bursting however, the text-file stayed completely empty.

So what went wrong?

I logged a TAR (SR, sorry the term TAR sticks) and according to Oracle text-output is not supported when using the bursting engine. This proved to be a bummer because i had to produce the file with meta info.

Luckilly I remembered the existence of so called “etext” templates. These templates are being used to generate EDIFACT and EFT files, which are fixed length or delimiter separated text files. Wouldn’t using an etext template solve my problem? And eventually it did 🙂

So how did i do this?

1. Create an etext template
2. Use the etext template in the bursting control file together with the pdf output

Create an etext template

The first thing to do is to create an extext template. This is a RTF file which contains a header plus a few tables in which you describe the output document.

The template consists of (not specific Oracle Terms) :

• a header
  Describing the template itself
• an output table
  Describing the output format of the file created by the template
• a record table
  Describing the records put in the file

In the template you can make use of quite a lot of advanced commands, but I’m not going to describe them here. You can look them up in the Oracle XML Publisher Report Designer’s Guide (Chapter 4).

Note: I discovered the format (lay-out) of the RTF template is quite important. BI Publisher will accept the template, even if it has an invalid format. While generating the output file you’ll get an error.

The header

The header of the file just describes the template file. Nothing spectaculair

The output table

The output table describes the output format of the file the extext template generates. The output format TEMPLATE_TYPE is either DELIMITER_BASED or FIXED_POSITION_BASED.
Based on the output choosen you need to specify either 3 mandatory columns in the record table or 5 mandatory columns. This example using the DELIMITER_BASED output type, for more info on the FIXED_POSITION_BASED output type, take a look at the guide.

The OUTPUT CHARACTER SET just specifies which output character set needs to be used.

The NEW RECORD CHARACTER specifies which character(s) is/are being used to separate the records specified in the records table.
Carriage Return is a reserved word to let Oracle use a Carriage Return as the separator. This provides you with an *NIX style text-file.

The record table

This table describes the layout of the records within the output file. 

Within the delimiter based output file you need to fill in three columns for each field. In these columns you specify what the record in the output file looks like. The records are being outputted at levels. You attach these levels to the XML tags found in the output.

At each level you can then output the data contained in the fields of the XML data.
For a delimiter based output file you specify the maximum length of the field, the type and the name or just plain data. Anything longer than the maximum length of the field get’s truncated. Field names are just typed in, data is encapsulated by quotes:

In the record table it is possible to give a new record command. This way it’s possible to create a multiline text-file using the extext template.
See the attached RTF for an example of that.

The bursting control file

The bursting file needs to use the etext template. The output type for the eText template is text.

So this is what the final bursting control file looks like:

<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">
  <xapi:request select="/XML_PATH/G_EMPLOYEEDATA">
    <xapi:delivery>
      <xapi:filesystem output="path_to_a_folder/output.pdf" id="pdf"/>
    </xapi:delivery>
    <xapi:document output-type="pdf" delivery="pdf">
      <xapi:template type="rtf" location="xdo://template.lang.00?getSource=true"/>
    </xapi:document>
  </xapi:request>
  <xapi:request select="/XML_PATH/G_EMPLOYEEDATA">
    <xapi:delivery>
      <xapi:filesystem output="path_to_a_folder/output.txt" id="txt"/>
    </xapi:delivery>
    <xapi:document output-type="text" delivery="txt">
      <xapi:template type="etext" location="xdo://template.lang.00?getSource=true"/>
    </xapi:document>
  </xapi:request>
</xapi:requestset>