I have a multi-part problem, so let me start by telling you the story from the beginning. We have a third party application that is written in COBOL that we cannot touch as the third party would not allow. This tool generates several reports in form of text files. The text files contain information as well as page breaks (NP Form feed), line breaks etc. The requirement is to read these reports and display them in Oracle BI.
I started off with reading these files as is and loading them into CLOB field in one of the tables and was thinking that I could simply display the data as is without any manipulation in ORACLE BI. However, I soon found out that the approach would not work for the following reasons:
1. Oracle BI is not very fond of "NP Form Feed, New Page" Character (CHR(12)).
2. Oracle BI seems to be treating the line feed as a regular space and removes it from the CLOB databaset.
3. Oracle BI is not very fond of extra space formatting and removes any extra spaces down to one space.
4. The entire CLOB field is displayed as a single line and basically one chunk of data without any formatting.
To counter all these issues, I broke the file into multiple records in a table with one line per record. So in essence I read one line from the file and store that as one record in the table, the next line as the next record and so on and so fourth. I have the data in the table without any line feeds, page breaks etc. The only thing I now have in the data is spaces that I need to preserve. I generated the report again and found the following issues:
* Oracle BI is not very fond of extra spaces and the xml generation removes and extra spaces down to one space
To counter this problem I thought I could wrap this in a CDATA in my query so that XML would preserve the spaces, however after several tries and eventually successfully creating the XML to my surprise I found the following:
1. Oracle BI is still not fond of extra spaces and trims spaces down to one
2. XML generation engine is where the spaces are being taken out
3. CDATA is not even taken into consideration when generating the XML as spaces are trimmed even when CDATA is used in the query
4. CDATA is not removed when the data is displayed in the report using the data table.
So I am basically stuck trying to figure out why this is happening and would really appreciate if someone could please explain what this issue is and what I can do to go about resolving it. I have tried basically everything I could think of to fix this but I appear to be banging my head against the wall to no avail. Any assistance at this point or a direction would be greatly appreciates.
Oh and one more thing in regards to the releases for Oracle and BI, We are using oracle 11g and BI Publisher Enterprise 11g (11.1.1)