Tip applicable to Discoverer 4i, 9.0.2+

You use Discoverer to do the following:

• Query an Oracle database and

• Query two Microsoft data sources (Access and Excel)

Users can do this by using the Heterogenous Data Services.

For additional information, please refer to some useful documents on Metalink (109730.1).

Solution:

This example sets up an Excel workbook with two sheets: One containing a Forecast and The other containing Journals. View the Excel spreadsheet link. Create and save the Excel workbook. Highlight the range you want to query. It should contain columns with column names that are valid in Oracle (no spaces, <= 30 chars). The range includes the column headings and the data. Highlight the area and name the range (insert - name - define). You will see this in the attached spreadsheet. When you are in Oracle the workbook will be similar to a database and the range names will be treated like tables

Setup a SYSTEM DSN in the Microsoft ODBC Administrator

Note: Program - O8 home - Network Administration - Microsoft ODBC Administrator
         You may need Administrator privileges.

1. This should be a system DSN not a User DSN. Give it a name. In this example, the file is called mine MSExcel.
2. If you have not installed the Oracle ODBC drivers when you installed your database you need to go back and do this. Ensure your Excel version is specified correctly.
3. Test this with the Oracle ODBC test.    
   •  Select CONNECT    
   •  Select Machine Data Source—select your data source     
   •  Enter a simple test select statement such as SELECT COUNT(*) FROM JOURNALS
4. Ensure your database Global naming is set to false: global_names = false

Create a file in the Oracle Home\HS\Admin directory

Note: Sample already exists, it is called init (HS SID Name—in this example it is called hsodbc). The ODBC source name is specified in here (MSExcel).

# Environment variables required for the non-Oracle system
#
# set = <envvar>=<value>
# This is a sample agent init file that contains the HS parameters that are # needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MSExcel
HS_AUTOREGISTER = TRUE HS_DB_NAME = hsodbc
# HS_FDS_TRACE_LEVEL = 0
# HS_FDS_TRACE_FILE_NAME = hsodbc.trc

Modify your listener.ora file to set your global db name and add a new section for access to your HS source (see below)

# LISTENER.ORA Network Configuration File: d:\oracle\root816\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dcameron-lap)(PORT = 1521))
   )
SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
    (SID_NAME = hsodbc)
    (ORACLE_HOME = D:\O8i)
    (PROGRAM = hsodbc)
  )
  (SID_DESC =
   (GLOBAL_DBNAME = O8i.US.ORACLE.COM)
   (ORACLE_HOME = d:\O8i) (SID_NAME = O8i)
  )
)

Add an entry in your TNSNames file

Here is an example.

HSODBC.US.ORACLE.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp)(HOST = dcameron-lap)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SID = hsodbc)
     )
     (HS = OK)
   )

Create a database link

Note: You need CREATE DATABASE LINK to do this.

SQL> create database link hsodbc using 'HSODBC.US.ORACLE.COM';
Database link created.

Test your connection using a SELECT

SQL> select count(*) from journals@hsodbc;
COUNT(*)
-------
    124

Create a view that references this table (you can't see the table through the database link in Discoverer (as you can with other links)):

SQL> create view journals as select * from journals@hsodbc;
View created.

Go into Discoverer Administrator and create a folder from this view as you with any other table or view.

You need to make sure the EUL owner has SELECT privilege on that view.