Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Is this ANSI ? (Was: Querying a number column.)

Re: Is this ANSI ? (Was: Querying a number column.)

From: Peter van Rijn <pgm.van.rijnRM_at_THIShccnet.nl>
Date: Fri, 24 Jan 2003 14:04:53 +0100
Message-ID: <b0rdte$p3o$1@news.hccnet.nl>


Matthias,

Supposing "mynumber" is a NUMBER, this query will also fail in 7.3.4.

Peter

"Matthias Rogel" <rogelREMOVE_at_THISweb.de> schreef in bericht news:b0rcoo$snskn$2_at_ID-86071.news.dfncis.de...
> (to continue Robert's example)
>
> SQL> select *
> 2 from t
> 3 where mynumber='a';
> where mynumber='a'
> *
> FEHLER in Zeile 3:
> ORA-01722: Ungültige Zahl
>
>
> As I understand from the discussion so far, in Oracle7 and lower this
> didn't throw an exception (cannot verify here), whereas in >=8
> throws an exception.
>
> I doubt this behaviour is ANSI-SQL
>
> matthias
>
>
> robertsboyle wrote:
> > Mattias is correct..running under 9.2 i get the following..
> >
> > Elapsed: 00:00:00.02
> > robert_at_DEV1>create table t (mynumber number);
> >
> > Table created.
> >
> > Elapsed: 00:00:00.00
> > robert_at_DEV1>create index x_t_mynumber on t(mynumber);
> >
> > Index created.
> >
> > Elapsed: 00:00:00.00
> > robert_at_DEV1>insert into t values (1);
> >
> > 1 row created.
> >
> > Elapsed: 00:00:00.00
> > robert_at_DEV1>insert into t values (2);
> >
> > 1 row created.
> >
> > Elapsed: 00:00:00.00
> > robert_at_DEV1>set autotrace on explain
> > robert_at_DEV1>select *
> > 2 from t
> > 3 where mynumber = '1';
> >
> > MYNUMBER
> > ----------
> > 1
> >
> > Elapsed: 00:00:00.00
> >
> > Execution Plan
> > ----------------------------------------------------------
> > SELECT STATEMENT Optimizer=CHOOSE
> > 1 0 INDEX (RANGE SCAN) OF 'X_T_MYNUMBER' (NON-UNIQUE)
> >
> >
> >
> > robert_at_DEV1>select *
> > 2 from t
> > 3 where mynumber = 1;
> >
> > MYNUMBER
> > ----------
> > 1
> >
> > Elapsed: 00:00:00.00
> >
> > Execution Plan
> > ----------------------------------------------------------
> > SELECT STATEMENT Optimizer=CHOOSE
> > 1 0 INDEX (RANGE SCAN) OF 'X_T_MYNUMBER' (NON-UNIQUE)
> >
> >
> >
> > robert_at_DEV1>select *
> > 2 from t
> > 3 where mynumber = to_number('1');
> >
> > MYNUMBER
> > ----------
> > 1
> >
> > Elapsed: 00:00:00.00
> >
> > Execution Plan
> > ----------------------------------------------------------
> > SELECT STATEMENT Optimizer=CHOOSE
> > 1 0 INDEX (RANGE SCAN) OF 'X_T_MYNUMBER' (NON-UNIQUE)
> >
> > Robert.
> >
> > --
> > Posted via http://dbforums.com
>
Received on Fri Jan 24 2003 - 07:04:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US