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)