To_char problem

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.

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?
--
Daniel A. Morgan
University of Washington
damorgan.DeleteThis@x.washington.edu
(replace 'x' with 'u' to respond)<!-- ~MESSAGE_AFTER~ -->