Oracle BI ODBC-connection [SQORAS32]


I have locally installed a 10gR2-database which I have patched to an 10.2.0.3 (Patch-nr: 5337014)

Situation:
I would create a new System Data Source Name to import tables from the HR-scheme. When we configure a new ODBC data source connection, we get following error:

SQORAS32 – An unsupported operation was attempted

SQORA32 is a dll-file that will be called if you select Oracle 10gR2 as driver to set up a data source.

Solution:
We download patch-nr 5699495 from metalink and follow the instructions below.

Part 1 – Exploding the Kit onto your system:

  • Expand the self-extracting archive file onto your hard drive.
  • Extract contents of the archive, then unzip the contents of the .zip file into a new directory.

Part 2 – Installation instructions:

This section assumes the following:

  1. Oracle 10.2.0.1.0 client has already been installed on your system, and the RDBMS version 10.2.0.3.0 patch # 5337014 has been applied.
  2. Part 1 has been completed.

Installation Instructions:
Once the self-extracting archive file has been exploded it will create an directory structure as shown below on your hard drive.

The directory structure after unzipping and where the files to be copied are shown below. should be replaced with the directory of your 10.2 Oracle Home. IE. c:\Oracle10.2.0\db

Steps:

  • ORA10203\bin\sqora32.dll —> <ORACLE_HOME>\bin
  • ORA10203\bin\sqoras32.dll —> <ORACLE_HOME>\bin
  • ORA10203\bin\sqresus.dll —> <ORACLE_HOME>\bin
  • ORA10203\ODBC\html\ODBCRelnotesUS.htm —> <ORACLE_HOME>\ODBC\html\
  • ORA10203\ODBC\readme.txt —> <ORACLE_HOME>\ODBC\html\
  • ORA10203\ODBC\mesg\oraodbus.msb —> <ORACLE_HOME>\ODBC\mesg\

We solved the problem and now we can create a new ODBC-connection.

Column Aliases in BI Publisher


Problem:

If you create a dataset and build your SQL-query by Query Builder or type it manually, you have to watch out that you do not use column aliases like ‘FEED’.

See example below:

SQL-query:

select BI_TABLE.FEED as FEED,
      BI_TABLE.FEEDBACK as FEEDBACK,
      BI_TABLE.APPLICATION_ID as APPLICATION_ID,
      BI_TABLE.NAME as TYPE_NAME,
      BI_TABLE.DESCRIPTION as TYPE_DESCRIPTION
from HR. BI_TABLE;

XML-file:

Internet Explorer seems to perform a strict XML validation. He gives you following error:


Solution:

Replace all column aliases with names like ‘FEED’ and your problem is solved. See solution below:

SQL-query:

select BI_TABLE.FEED as COLUMN_01,
      BI_TABLE.FEEDBACK as COLUMN_02,
      BI_TABLE.APPLICATION_ID as APPLICATION_ID,
      BI_TABLE.NAME as TYPE_NAME,
      BI_TABLE.DESCRIPTION as TYPE_DESCRIPTION
from HR. BI_TABLE;

XML-file:


Note: You can use ‘FEED’ as name for database tables but remember, not for column aliases!!!

Oracle BI Publisher Date-type

You can only format dates when they are in the canonical date format. This format looks like: YYYY-MM-DDTHH24:MI:SS.

You have two options:
1. adapt the XML generation process to generate the date in the canonical format and use the format date functions of BI Publisher in the template.

2. adapt the XML generation process to generate the date in the format of the client (example: DD-MM-YYYY) and print this directly on your report.


Solution 1:

First, we create a new dataset. We give it a name and select SQL Query as Type. Then choose a ‘Data Source’ and check ‘Cache Result’.

We build a query by Query Builder in BI Publisher or type it manually. See below to our ‘Data Set’ -query.

Note: XML-Date is in the canonical format and can be imported directly in the template. Example below is based on a ‘sysdate’ and stored in a field called ‘FULL_DATE’.

DS_Query:

select BI_DATE.FULL_DATE as FULL_DATE
from HRM.BI_DATE BI_DATE

After we create a dataset, we generate an XML to build our RTF-template.

XML-type:

&lt?xml version=”1.0″ encoding=”UTF-8″ ?&gt
&ltROWSET&gt
   &ltROW&gt
      &ltFULL_DATE&gt2008-02-19T13:24:37.000+01:00&lt/FULL_DATE&gt
   &lt/ROW&gt
&lt/ROWSET&gt

Properties:

Load XML into MS Word (with Oracle BI Publisher Desktop). Import the field ‘FULL_DATE’ and edit the properties. We can choose different format-types in the drop-down box. Choose one for example: ‘d-MMM-yy’ or type your own format-type like ‘dd-mm-yyyy’.

Note: Field ‘FULL_DATE’ is a canonical format.

Results in Word:

Solution 2:

First, we create a new dataset. We give it a name and select SQL Query as Type. Then choose a ‘Data Source’ and check ‘Cache Result’.

We build a query by Query Builder in BI Publisher or type it manually. See below to our ‘Data Set’ -query.

DS_Query:

select BI_DATE.FULL_DATE as FULL_DATE,
   to_char(BI_DATE.FULL_DATE) as CHAR_DATE,
   to_char(BI_DATE.FULL_DATE,’dd-mm-yyyy’) as FORMAT_DATE,
   to_char(BI_DATE.DAY_DATE,’DD’) as DAY_DATE,
   to_char(BI_DATE.MONTH_DATE,’MM’) as MONTH_DATE,
   to_char(BI_DATE.YEAR_DATE,’YYYY’) as YEAR_DATE
from HRM.BI_DATE BI_DATE

After we create a dataset, we generate an XML to build our RTF-template.

XML-type:

&lt?xml version=”1.0″ encoding=”UTF-8″ ?&gt
&ltROWSET&gt
   &ltROW&gt
      &ltFULL_DATE&gt2008-02-19T13:24:37.000+01:00&lt/FULL_DATE&gt
      &ltCHAR_DATE&gt19-FEB-08&lt/CHAR_DATE&gt
      &ltFORMAT_DATE&gt19-02-2008&lt/FORMAT_DATE&gt
      &ltDAY_DATE&gt19&lt/DAY_DATE&gt
      &ltMONTH_DATE&gt02&lt/MONTH_DATE&gt
      &ltYEAR_DATE&gt2008&lt/YEAR_DATE&gt
   &lt/ROW&gt
&lt/ROWSET&gt

Load XML into MS Word (with Oracle BI Publisher Desktop). Because we have set our date types in the query, we just can import the available fields without editing the properties.

Results in Word:

Solution 3:

XML-Date is not a canonical date format. Second solution for this problem is similar to solution 2. Except that we don’t edit the SQL-query and generate XML.

Load XML into MS Word (with Oracle BI Publisher Desktop). Import necessary fields and edit properties.

Example field ‘Full Date’:

Choose ‘Word Properties’:

Then ‘Add Help Text…’

Type following statement by ‘Help Key (F1)’: &lt?format-date: FULL_DATE;’dd-MMM-yyyy’;’Europe/Brussels’?&gt

Note: ‘Europe/Brussels’ is the time-zone of your region.

And click ‘OK’ -> ‘OK’.

Disadvantage: you have to apply this solution to every field on the report. If you do it on query-level, you can import fields without editing them in the template.