Oracle 8 with Cognos 8

[JoeBass]

Oracle 8 isn't supported with Cognos 8 but, we were able to put a work-around together.  We may be the only company who's still using Oracle 8 but, in case we're not:

One Time Setup
1.  Install Oracle Client on SQL Server
2.  Create a linked server on SQL Server

For Each Report
1.  Create a stored procedure on SQL Server - This is used to build a sql string that is sent to Oracle

• A.  Each parameter needed in the report must be specified as a parameter in the stored procedure.
• B.  In order to use the normal date prompts in the report, the stored procedure must accept DateTime parameters.  To feed these dates into Oracle, corresponding text variables must be created within the body of the stored procedure.  These variables must be assigned the results of a conversion to text.
• C.  Build a SQL String by substituting variable values into a where clause.
  
• D.  Pass the SQL string to OPENQUERY within the SQL Server stored procedure to force Oracle to process the query and return only a result set.

2.  Create a query subject within Framework manager (don't use the Stored Procedure Query Subject type)

• A.  Write SQL to execute the SQL Server stored procedure.
• B.  Create prompts for each parameter the stored procedure is expecting.
• C.  For a list type prompt, create a promptmany integer macro surrounded by single quotes.
• D.  For a date type prompt, create a prompt date macro surrounded by single quotes.

3.  Create prompts in ReportNet whose parameters correspond to the prompt names established in Framework Manager.

[MFGF]

Thanks for taking the time and effort to share this with us Joe.  It's much appreciated.

MF.