Archive for category Oracle
Depending on your Oracle database configuration, you may find that your Oracle schema names vary across your environments (DEV/QA/PROD). This can be a problem for Cognos Framework Manager, which assumes a single standard schema per data source connection. What’s worse is that your user name and password can be changed easily from one environment to another (since these elements are defined in the Cognos portal data source connections) but not the schema (which is defined in Framework Manager and then published). There are a few different approaches that can be taken to handle this. The first is Oracle synonyms. These can easily resolve your schema name challenge at the database level, but can create headaches for your DBAs. Depending on the DBA policies of your organization, this may or may not be an option.
A second option, although not elegant, is to simply publish your model for each environment, changing the schema in the data source connection as you go. Although this will work, it feels more like a work-around than a solution.
You can also choose to manage them by using Parameter Maps in Cognos Framework Manager. If you choose to manage them in FM, the first step is to determine your environment. This can be done by creating a query subject that will determine your database connection or database server. A very useful Oracle function for this purpose is SYS_CONTEXT, which can tell you the server name, the database name or a variety of other database environment identifiers. It is also a widely available function, which means you should be able to call it no matter what Oracle account you are using.
You can then set up a Parameter Map to read your Environment Query. Base your Parameter Map on your defined Environment Query, using the SCHEMA column as the key and the SCHEMA_VALUE as the value.
Once you’ve defined your parameter map, you need to make two final changes. First, remove any Schema definition in your Data Source properties. Do not attempt to call your Parameter Map schema value here. If you try, you will get a recursive call error as this code will attempt to run for your Environment Query, which needs to run to get your schema value in the first place.
Next, add your parameter value to each Query Subject, except for your Environment Query:
Now you should be able to import and export your data model at will across your database environments without making schema changes at each turn.
If you ever get the following error when attempting to connect Cognos Framework Manager with an Oracle data source, you have an Oracle configuration problem:
QE-DEF-0285 The logon failed.
QE-DEF-0325 The logon failed for the following reason:
RQP-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database(s) in:
UDA-SQL-0432 Unable to locate the gateway “cogudaor”
If you have a valid Oracle client installed on your machine (Oracle 9 or up for Cognos 8/10), then check your System Environment variables. I had a recent experience at a client where both Oracle 8 and Oracle 10 client software were installed on a machine. Because the ora805\bin reference appeared before the ora10\bin reference in the Path variable, Cognos Framework Manager could not connect to the data source and reported the above error. Simply by changing the order of those references in the Path variable, the configuration problem was resolved.
While implementing the Cognos 10 Audit package on an Oracle 10g system, I ran into a problem with one of the pre-defined reports. This one report would fail each time, reporting an Oracle error: ORA-12704 character set mismatch.
My first Google search indicated that there was a problem with Cognos 8.3 Audit package on Oracle 10g. A modification was required in the Framework Manager model, changing all references from nVarChar to characterLength16. Although I was running Cognos 10, I decided to try this fix out. It did not adversely affect my Audit package, but it did not resolve the issue either. As all the model query subjects were working as expected in the original Framework Manager model, I reverted back to it and decided to focus my efforts on the Report Studio report.
Eventaully I isolated the problem down to three If/Then/Else statements in the report, and then realized it was not able to compare query results against an empty string. I casted this empty string into a nvarchar and the problem was resolved:
IF [Audit].[Audit Report Table].[Audit Report Column] = ” THEN…
IF [Audit].[Audit Report Table].[Audit Report Column] = cast(”, nvarchar(1)) THEN…