Query anomaly...

Good morning, all...

We have two Oracle database instances, one for development (DEV) and one for production (PRD). Now the schemas of these two database instances are as identical as I can see, but we seem to have managed to find an interesting query anomaly in them.

The OS platform where these instances run is HP-UX 11.00, and the version of the Oracle is:

	Oracle8i Enterprise Edition Release 8.1.5.0.0, 64 bit - Producti
	PL/SQL Release 8.1.5.0.0 - Production                           
	CORE Version 8.1.3.0.0 - Production                             
	TNS for HPUX: Version 8.1.5.0.0 - Production                    
	NLSRTL Version 3.4.0.0.0 - Production                           

At some time in the past the application that uses this database was of the opinion, that the CUSTOMER_ID field only contained numbers. Hidden somewhere in the depths of the application there was found the following query:

        SELECT * FROM CUSTOMER WHERE CUSTOMER_ID = 12345 ; Now remember, that the type of the field CUSTOMER_ID is VARCHAR2, not a NUMBER (it was, but that is no longer the case). This kind of a query *should* always result in a nice little error message from the Oracle, like:

        ORA-01722: invalid number

But! On the DEV database instance the Oracle does not complain at all, it just acts as if the number 12345 was enclosed in single quotes. But on the PRD database instance I do indeed get the error message.

Could someone explain this behaviour to me in great detail as it is stretching my credibility a bit too far to actually believe that these two instances could behave this dissimilarily? Is there a way to get the Oracle database server to believe that single quotes are not a necessary requirement for query restrictions involving numbers?

Best regards,

        HTK

-- 
Heikki.Karhunen_at_kone.com
Heikki.Karhunen_at_iki.fi