Last January, I installed a test instance of Questionmark’s Perception server, version 4.2, in preparation for upgrading our version 3.4 Perception server. At the time, we were using Oracle version 9i. Once installed, our testing went well, and we did not notice any significant problems.

In July, we finally got around to doing the actual upgrade, and decided to upgrade the database to Oracle 10g at the same time. The installation and conversion of our version 3.4 data went very smoothly. Everything ran well for about a week. Then things began to go wrong. When we would open the Authoring Manager, it would refuse to show us any assessments (“assessments can’t be found”.) Then it would tell us administrators couldn’t be found. After a few days, the assessments disappeared from the Perception Server as well, meaning users could no longer take them. After trying numerous fixes, we reverted to a recent backup of the database from a couple of days before, and the problem disappeared. The one Oracle error that both Perception Server and Authoring Manager were giving was “ORA-03115: unsupported network datatype or representation.”

A few days later the problems came back. This time, we resolved it by recompiling the objects in the database. Meanwhile, we moved a bit-for-bit copy of the production database over to our test database server. We were very surprised when it worked perfectly. The data was completely intact.

When we decided to try restarting the database server the problem resolved. This led our DBA to discover the answer, hidden away in Oracle’s Metalink:

Subject: OCI Application Errors with ORA-3115
Doc ID: Note 460498.1
last revision date Oct. 4, 2007

Applies to Oracle net services – v. 10.2 to 11.1
This problem can occur on any platform

Symptoms
Third party application using OCI, intermittently hangs. Application log reports ORA-3115 “Unsupported network datatype or representation”. No dumps file ar produced nor are any error entries reported in the alert log.

Flushing the shared pool is known to get rid of the problem for a short while:
SQL> alter system flush shared_pool

Cause

Bug 6085665 ORA-3115 Running OCI Application After install Patcheset 10.2.0.3
Bug is not published so not visable in metalink for customers

Solution
Set the CURSOR_SHARING database parameter to EXACT.
This setting will only allow identical statements to share the same cursor.
A downside to this is that it can lead to fragmentation if the application uses literals instead of bind variables.

The solution was to set “CURSOR_SHARING parameter on the Oracle database to “Exact.” Since that was done we have had no problems.