| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: :00000 is stored in NUMBER(12) field, Oracle 7.3.3 Workgroup Server for Windows NT
Anna Sotnichenko <annasony_at_home.com> wrote in message
news:37718211.F0B35BE5_at_home.com...
>Did anyone experience something of the kind?
>
>Application written in Delphi 4 + BDE 5.01 is running on Windows NT 4.0
>workstation and communicating through SQL*Net with Oracle 7.3.3
>workgroup server running under Windows NT 4.0 server.
>Sometimes very strange values are stored in the fields of type
>NUMBER(12)
>In SQL*Plus they look like :00000 (colon followed by 5 zeros) and the
>only way to display the rows containing these values is to issue the
>query:
>
>select * from mytable
>where to_char(myfield) = ':00000';
We find that this problems exists for 1000000 (1 million), 2000000 (2 millions), 3000000 (3 millions),
4000000 (4 millions), 5000000 (5 millions), 6000000 (6 millions)
7000000 (7 millions), 8000000 (8 millions), 9000000 (9 millions)
The attached file DORAGLUK.ZIP includes export of table FFF with column
KOL_KG
Table has 2 rows loaded from DBF file FFF.DBF
First row was loaded with Delphi (BatchMove ?)
Second with SQL*Loader.
---
Archive DORAGLUK.ZIP contents:
FFF.CTL - SQL*Loader command file
FFF.DBF - Source DBF file
FFF.DMP - Export file (Export v7.3.3. USER=G Table=FFF)
(Sorry, user G had DBA role... :-( And now I have not Oracle 7.3.3 to
Rexport)
FFF.SQL - Create table FFF script
---
---EXECUTE SOME SELECTS---
SQL*Plus: Release 3.3.2.0.2 - Production on Thu Jun 24 18:17:40 1999
^^^^^^^^^^
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
Connected to:
SQL> SELECT kol_kg,SUBSTR(DUMP(kol_kg),1,30) FROM fff 2 /
KOL_KG SUBSTR(DUMP(KOL_KG),1,30)
--------- ------------------------------
:00000 Typ=2 Len=2: 195,101
1000000 Typ=2 Len=2: 196,2
SQL> SELECT kol_kg,SUBSTR(DUMP(kol_kg),1,30) FROM fff
2 WHERE kol_kg > 999999.9999999999999999993 AND kol_kg < 1000000
KOL_KG SUBSTR(DUMP(KOL_KG),1,30)
--------- ------------------------------
:00000 Typ=2 Len=2: 195,101
This means that its value less then 1 Million but greater then
999999.999999999999999999
SQL*Plus: Release 8.0.5.0.0 - Production on Thu Jun 24 18:20:34 1999
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
SQL> SELECT kol_kg,SUBSTR(DUMP(kol_kg),1,30) FROM fff
2 WHERE kol_kg > 999999.9999999999999999993 AND kol_kg < 1000000
KOL_KG SUBSTR(DUMP(KOL_KG),1,30)
--------- ------------------------------
000000 Typ=2 Len=2: 195,101
There is no colon (:) and six (not five) zeroes!
---
The same problem can be in Delphi form where we have some
field wich value calculated as production of to other fields
F = F1 * F2
>Being sent to a client application these values are represented by a
>very large float numbers (e+308) and cause "value out of bounds" error.
>
>Hexadecimal representation of this value is FFFF FFFF FFFF EF7F
>I must admit that the same application running under Win95/98
>workstation communicating with the same Oracle server doesn't cause this
>
>problem.
>I don't know who is to blame: Oracle server (very unlikely), Oracle
>SQL*Net, Oracle Call Interface, Borland Oracle SQL Link, BDE, ...?
>I would greatly appreciate your help and advice
I think both.
One Stupid (Delphi+BDE) put stupid data
and second Stupid (Oracle) get stupid data... :-)
This error not occure every time.
It is not so simple to find conditions when this error raised ... :-(
Valery Yourinsky
---
Softservice, Moscow
ORACLE PARTNER
tel/fax (095) 333-63-10, 128-18-21
http://www.softexpress.ru
ICQ# 368 97 94
second E-mail: vsur_at_usa.net
 |
 |