Re: To_char problem

From: Cedric Calentier <cedric.calentier_at_caramail.com>
Date: 11 Feb 2005 01:15:30 -0800
Message-ID: <682e5942.0502110115.1fd64fca_at_posting.google.com>


Version : Oracle 8i Enterprise Edition Release 8.1.7.4.0

This table contains more tha 37000 records but the problem occurs with only 2 records : obj_id0=225936449496832965 and obj_id0=226851243171165952.
Is it possible these 2 records were badly inserted in database (conversion or length problem...)??

I executed this request :
SQL> select count(*) from invoice_formats_buf_t;

  COUNT(*)


     37377

This request indicates there are 37377 records in invoice_formats_buf_t table, but in reality there are 37377+2=37379 records (I'm sure).
I don't understand why these 2 records are not taken into account by the "select count(*)". Does it mean these 2 records are not refered into index??

DA Morgan <damorgan_at_x.washington.edu> wrote in message news:<1108092848.503475_at_yasure>...
> Cedric Calentier wrote:
>
> > Hello,
> >
> > I've got a probleme when I try to access some data by using "to_char"
> > function in the "WHERE" clause of my request.
> >
> > For example, when I write the following request, it works :
> > SQL> select ';' || to_char(obj_id0) || ';' from invoice_formats_buf_t
> > where obj_id0=225936449496832965;
> > The result is :
> > ';'||TO_CHAR(OBJ_ID0)||';'
> > ------------------------------------------
> > ;225936449496832965;
> >
> > But with this one, it doesn't :
> > SQL> select ';' || to_char(obj_id0) || ';' from invoice_formats_buf_t
> > where to_char(obj_id0)='225936449496832965';
> > The result is :
> > no rows selected
> >
> >
> > For information, the type of the selected field is NUMBER. You can
> > find here a description of the table:
> > SQL> desc invoice_formats_buf_t
> > Name Null? Type
> > ----------------------------------------- --------
> > ----------------------------
> > OBJ_ID0 NUMBER
> > REC_ID NUMBER
> > BUFFER_BUF BLOB
> >
> > Has anyone an idea concerning the origin of this strange behaviour.
> >
> > Thanks,
> >
> > Cédric Calentier.
>
> SQL> create table invoice_formats_buf_t (
> 2 obj_id0 NUMBER,
> 3 rec_id NUMBER,
> 4 buffer_buf BLOB);
>
> Table created.
>
> SQL> insert into invoice_formats_buf_t
> 2 (obj_id0)
> 3 values
> 4 (225936449496832965);
>
> 1 row created.
>
> SQL> insert into invoice_formats_buf_t
> 2 (obj_id0)
> 3 values
> 4 (999999999999999999);
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select ';' || to_char(obj_id0) || ';' from invoice_formats_buf_t
> 2 where obj_id0=225936449496832965;
>
> ';'||TO_CHAR(OBJ_ID0)||';'
> ------------------------------------------
> ;225936449496832965;
>
> SQL> select ';' || to_char(obj_id0) || ';' from invoice_formats_buf_t
> 2 where to_char(obj_id0)='225936449496832965';
>
> ';'||TO_CHAR(OBJ_ID0)||';'
> ------------------------------------------
> ;225936449496832965;
>
> SQL>
>
> Works just fine in 10.1.0.3. What version do you have?
Received on Fri Feb 11 2005 - 10:15:30 CET

Original text of this message