HOW TO: create a .txt (text) file within bi publisher 11g

This was a nightmare to understand, though incredibly easy to create. The oracle documentation is somewhat convoluted to the point where I extracted various information from multiple websites to help me with building eText intuitively.
August 29, 2013
BI-Publisher-11g

Brief understanding about eText

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.


Retrieving a CSV template for TXT reports

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.

https://technicalconfessions.com/images/postimages/postimages/_219_8_eText template.png

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.


Understand the report structure

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.


Understanding the eText format

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.

https://technicalconfessions.com/images/postimages/postimages/_219_13_first header information within etext.png

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)

Next, is to understand the format data records.

The Level highlighted is the initial node that you can see within the sample.xml. You can also see the node is also ended at the bottom of the eText report.

https://technicalconfessions.com/images/postimages/postimages/_219_14_DS level bi publisher.png

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.

https://technicalconfessions.com/images/postimages/postimages/_219_15_G1_level bi publisher.png

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.

  • NOTE: I changed the OUTPUT CHARACTER SET to ASCII so I could use CHR(13) as a value, which is a new line
  • .

    https://technicalconfessions.com/images/postimages/postimages/_219_16_separate values within the eText file.png

    Next thing you need to do is to save the file and upload it as a eText template, then you're good to go. The result of this was a .txt file, with the format as follows:

    1,REDFERND
    50,INACTIVE
    1,ROGERS
    50,INACTIVE
    1,WILSON
    50,INACTIVE

    Note: Don't get confused with the comma. the value '1,REDFERND' is actually the result from the SQL result, not incorporated within the eText


    eText example

    Here's an example eText Template if you wanted to download this directly from the technical confessions server (you're welcome :-) )

    About the author

    Daniel is a Technical Manager with over 10 years of consulting expertise in the Identity and Access Management space.
    Daniel has built from scratch this blog as well as technicalconfessions.com
    Follow Daniel on twitter @nervouswiggles

    Comments

    Other Posts

    NameID element must be present as part of the Subject in the Response message

    ShibbolethSAML

    August 7, 2018
    Created by: Daniel Redfern
    NameID element must be present as part of the Subject in the Response message, please enable it in the IDP configuration.
    Read More...

    HOW TO provision AD group membership from OpenIDM

    OpenIDMICFAD-connector

    June 15, 2018
    Created by: Daniel Redfern
    For what I see, there's not too many supportive documentations out there that will demonstrate how provision AD group membership with the ICF connector using OpenIDM. The use of the special ldapGroups attribute is not explained anywhere in the Integrators guides to to the date of this blog. This quick blog identifies the tasks required to provision AD group membership from OpenIDM to AD using the LDAP ICF connector. However this doesn't really explain what ldapGroups actually does and there's no real worked example of how to go from an Assignment to ldapGroups to an assigned group in AD. I wrote up a wiki article for my own reference: AD group memberships automatically to users This is just my view, others may disagree, but I think the implementation experience could be improved with some more documentation and a more detailed example here.
    Read More...

    ForgeRock OpenIDM - InvalidCredentialException: Remote framework key is invalid

    ICFIDMOpenIDMOpenICF

    November 8, 2017
    Created by: Daniel Redfern
    In the past, the similar error occurred though for the Oracle Identity Management solution. invalidcredentialexception remote framework key is invalid Because they all share the ICF connector framework, the error/solution would be the same.
    Read More...

    org.forgerock.script.exception.ScriptCompilationException: missing ; before statement

    IDMsync.confforgerockopenidm

    November 8, 2017
    Created by: Daniel Redfern
    org.forgerock.script.exception.ScriptCompilationException: missing ; before statement
    Read More...

    ForgeRock IDM - org.forgerock.script.exception.ScriptCompilationException: missing ; before statemen

    OpenIDMsync.confForgeRock

    September 17, 2017
    Created by: Daniel Redfern
    ForgeRock IDM - org.forgerock.script.exception.ScriptCompilationException: missing ; before statement
    Read More...

    Caused by: org.forgerock.json.resource.BadRequestException: Target does not support attribute groups

    OpenIDMForgeRockICFConnector

    September 17, 2017
    Created by: Daniel Redfern
    When performing the attempt of a reconciliation from ForgeRock IDM to Active Directory, I would get the following error
    Read More...

    ForgeRock OpenIDM - InvalidCredentialException: Remote framework key is invalid

    OpenIDMForgeRockICFConnectorAD

    September 17, 2017
    Created by: Daniel Redfern
    In the past, the similar error occurred though for the Oracle Identity Management solution. invalidcredentialexception remote framework key is invalid Because they all share the ICF connector framework, the error/solution would be the same.
    Read More...

    ERROR Caused by com.google.api.client.auth.oauth2.TokenResponseException 400 Bad Request - invalid_g

    OpenIDMIDMGoogleGoogle-AppsICFreconciliation

    September 12, 2017
    Created by: Daniel Redfern
    During the reconcilation from OpenIDM to the ICF google apps connector, the following error response would occur. ERROR Caused by com.google.api.client.auth.oauth2.TokenResponseException 400 Bad Request - invalid_grant
    Read More...

    forgerock-openidm-encryptedjwt-error

    OpenIDMIDMForgeRockJWTIAM

    August 29, 2017
    Created by: Daniel Redfern
    Received the JWT error
    Read More...

    Unexpected character ('¾' (code 190)): expected a valid value

    ForgeRock-OpenIDMOpenIDMIDMKeystore

    June 25, 2017
    Created by: Daniel Redfern
    Unexpected character occurred when the IP addresses changes and the virtual instance was migrated into a separate network subnet.
    Read More...