Product:
Cognos Controller 10.3 (CONTRL_version=CCR-AW64-ML-RTM-10.3.0.169-0)
Microsoft Windows 2008 R2 Server
Oracle 12c database (version Release 12.1.0.2.0) 64 bit
Symptom:
When connect to an Oracle Controller database you get an error during the upgrade steps inside Cognos Controller Configuration program.
Error message in DBCONV:
Running DB Step: 950
Running script batch: script/ora_950.batch
Running script: script/ora/ora_update_xmenu.sql
Running DB Step: 951
Running DB Step: 952
Running script batch: ora_952.batch
Running script: script/ora_xrclayout_add_column.sql
** ERROR: java.sql.SQLSyntaxErrorException: ORA-00904: “DBMS_LOB”.”SUBSTR”: ogiltig identifierare
ORA-06512: vid rad 12
The SQL command that give the error are:
DECLARE
sSql VARCHAR2(4000);
BEGIN
IF NOT UFNC_OBJECT_EXISTS(‘SELECT 1 FROM user_tab_COLUMNS WHERE table_name = ”XRCLAYOUT” AND column_name = ”EXCELFILEFORMAT”’) THEN
sSql := ‘ALTER TABLE XRCLAYOUT ADD EXCELFILEFORMAT number DEFAULT (0) NOT NULL’;
EXECUTE IMMEDIATE (sSql);
sSql := ‘UPDATE XRCLAYOUT SET EXCELFILEFORMAT = 56’;
EXECUTE IMMEDIATE (sSql);
sSql := ‘UPDATE XRCLAYOUT SET EXCELFILEFORMAT = 51 WHERE UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(layout, 2,1)) = ”PK”’;
EXECUTE IMMEDIATE (sSql);
END IF;
END;
Possible Solution:
There can be a setting on the Oracle database that does not allow that many connections. Please check and increase.
Please note: If you get a error during upgrade of a cognos controller database – then the database will be corrupt, because some upgrade steps will be missing. You must restore the original database and run the upgrade steps again from the beginning to ensure the database is fully functional.
It can be that you have the wrong ojdbc7.jar file in the controller folder.
Copy the correct ODBC file from the Oracle client installation on the server
Can be this folder:
C:\orarun\ora12102\Client\jdbc\lib\ojdbc6.jar
To the folders of the Cognos controller installation
that normally is these folders;
C:\Program Files\ibm\cognos\ccr_64
C:\Program Files\ibm\cognos\ccr_64\server\integration
C:\Program Files\ibm\cognos\ccr_64\server\FAP\lib
C:\Program Files\ibm\cognos\ccr_64\webapps\p2pd\WEB-INF\lib
Close the Controller Configuration and start it again so it can pick up the new driver jar file you have paste into the controller ccr_64 folder.
You can also be needing to run the DBCONV.EXE program as standalone from Cognos Controller Configuration.
Go to folder /ccr_64/ and start DBCONV.EXE
Click on the button with dots to select the correct UDL file.
Then click on Connection Button.
Now you can click on Upgrade button, and it should work better.
There can also be other cause of this issue – ensure the host name in cognos controller configuration only use the servername and not .domain.com – the new code does not accept period in server name for the data source.
More Information:
Cognos Controller use different connection methods to connect to ORACLE databases, like this:
ODBC = Controller Consolidation part
OLEDB = Controller Applications part
JDBC = used by DBCONV and java parts, also used by Cognos BI CQM reports
OCI = used by Cognos BI DQM reports (not found on Cognos Controller server)