Re: Query anomaly...

The data in your other database has character values in it.

SQL> create table tab1 (a varchar2(5));

Table created.

SQL> create table tab2 (a varchar2(5));

Table created.

SQL> insert into tab1 values ('12345');

1 row created.

SQL> insert into tab1 values ('54321');

1 row created.

SQL> insert into tab2 values ('12345');

1 row created.

SQL> insert into tab2 values ('AAAAA');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tab1 where a = 12345;

A

SQL> select * from tab2 where a = 12345; ERROR:
ORA-01722: invalid number

<Heikki.Karhunen_at_kone.com> wrote in message news:uk7x1el9l.fsf_at_kone.com...
> 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
>
> We have in the schema a table CUSTOMER, the definition of which is:
>
> CREATE TABLE CUSTOMER
 
> CUSTOMER_ID VARCHAR2 (16) NOT NULL,
> ...
> )
>
> This table definition is identical on DEV and PRD instances.
>
> 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
>
Received on Tue Dec 04 2001 - 02:27:15 CET