Oracle generated column aliases different on two DB’s

oracleoracle-11g

We have a legacy application we cannot modify that connects to Oracle 11g and at some point runs a query and returns a result. The application however is using the "generated" column name from Oracle to read the result.

Consider the following query:

select nvl(1,0.0) from DUAL;

As this query does not specify an alias, the generated column name would be "nvl(1,0.0)"

However on another server the generated column name is "nvl(1,0)" (notice 0 and not 0.0) and the application fails.

Is there a configuration that can be changed for Oracle? I've searched for formatting and locale configurations and they are equal on both servers.

Any help would be appreciated

Best Answer

It turns out there's a parameter called cursor_sharing that was set to FORCE instead of EXACT

select nvl(1,0.0) from DUAL;

The query above returns the following depending on the value of the parameter:

FORCE=NVL(1,0)
EXACT=NVL(1,0.0)
Related Question