ORA-32034 with typed-in SQL after 10.2 upgrade

Technote (troubleshooting)


Problem(Abstract)

Reports being upgraded to Cognos 10.2 that contain typed-in SQL (either in the report or in the FM model) may encounter this error if the typed-in SQL contains a SQL With clause, and isn't flagged as "Pass Through".

Symptom

ORA-32034: unsupported use of WITH clause



Cause

Cognos 10.2 may try to take typed-in SQL that is flagged as "Native" (the default), and use it within a derived table so that we can make the database perform the joins required for the report. But if the typed-in SQL use the SQL With clause already, this will cause an error (since you can't nest derived tables).

Environment

Cognos 10.2 using reports designed in previous versions of Cognos and running against an Oracle database. Similar errors will be seen against other database's that support derived tables, but will have errors specific to those databases.

Resolving the problem

Switch the SQL setting on the query subject or report query object from "native" to "Pass Through"

The difference between "Native" and "Pass-through" relates to Cognos's expectation of being able to take the supplied SQL and embed it with our own generated SQL for better performance. If the typed in SQL contains query or processing hints, it should always be flagged as "Pass Through", even if the SQL appears to work using "Native", otherwise future changes to the Cognos query engine could cause the SQL to begin to fail.

Note that using typed-in SQL requires that the report and query objects be maintained by the developers; Cognos does not have the ability to analyze user-supplied SQL, and cannot guarantee the functionality or performance of the product when using these objects.

Related information

Cognos Upgrade Central

Rate this page:

(0 users)Average rating

Document information


More support for:

Cognos Business Intelligence
Lifecycle Manager

Software version:

10.2

Operating system(s):

AIX, HP Itanium, HP-UX, Linux, Solaris, Windows

Reference #:

1632795

Modified date:

2013-03-29

Translate my page

Machine Translation

Content navigation