My initial understanding with the word 'eText' was basically a generic terminology for electronic data format of information, such as an EDI or EFT format, oh how I was completely wrong!

The terminology of 'eText' is a terminology that Oracle only seem to use to specify a particular format of a RTF template file.

You can create a CSV format by using the RTF format within microsoft word.

Though below are the steps to create a text report within bi publisher, which I blogged about in the following, HOW TO: Create a line-delimited file within Bi Publisher using SQL query data set, though that's not an eText format. An eText is actually a specific tables and conditional labeling within the document that bi publisher can understand.

Secondly, I tried to force a report format by creating a CSV format report by blogging, HOW TO: Configure Bi Publisher in CSV format, though this didn't work for text formats.

All of this was the incorrect approach when creating text files. This was initially a lot easier in 10g though in the 11g, Oracle have cleverly developed a unified a eText format to create ETFs, EDIs and now TXT files.

In my opinion, the eText Template guidelines printed by Oracle is convoluted and difficult to relate this to creating a txt file. There's no mention on the eText templates or how to locate them.

Turns out, they can be located right under your nose. Within the 'catalog' tab, you can go to the 'Samples/eText' folder and click on the 'eText Simple - DelimiterBased' template.

After creating a data model by using the SQL command against the database, I then develop a sample.xml file, which contains the results from the SQL query. This was also blogged within the blog, HOW TO: create a sample data within Bi Publisher

So when I execute the report, I get something that looks like this:

<DATA_DS>
<G_1>
<USERNAME>1,REDFERND</USERNAME>
<INACTIVE>50,INACTIVE</INACTIVE>
</G_1>
<G_1>
<USERNAME>1,ROGERS</USERNAME>
<INACTIVE>50,INACTIVE</INACTIVE>
</G_1>
<G_1>
<USERNAME>1,WILSON</USERNAME>
<INACTIVE>50,INACTIVE</INACTIVE>
</G_1>
</DATA_DS>

This will be used to construct your eText file.

This is where the oracle document falls short because the referenced article above is the steps on how to create EDI and ETF formats.

The eText is broke into two parts (the format setup, and format data records). The format setup is the general understanding of the report (what character set to use, what is the type of template it's going to be etc.). The format data records is the actual layout of the data and how it's going to be presented.

As I wish to create a text file, I am using the following:

• <TEMPLATE TYPE> = DELIMITER_BASED
  
• <OUTPUT CHARACTER SET> = ASCII
  
• <NEW RECORD CHARACTER> = Carriage Return (meaning a new line for each record)
  

The next is the NEW RECORD, that is coupled with END LEVEL column. This states the start of each record. You can see that the response back within the sample.xml, there are 3 accounts, all initiated and ended with the G_1 node.

The final section is the different data that you actually want to present. You can see that I included 3 lines USERNAME, INACTIVE, and another one called CHR(13), which means a new line. If I wanted to use a comma instead of a new line, then it would be something like CHR(44) or a space like CHR(32). Look up ASCII Characters for all the possibilities.