| 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: VARRAY Object types for Numbers, how to avoid roundoff?
A copy of this was sent to "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
(if that email address didn't require changing)
On Thu, 22 Jul 1999 19:48:15 +0100, you wrote:
>
>You'll love this:
>
>First - the thing behaves on my 8.0.4 exactly as it behaves on
>your 8.0.5 - output is rounded. Had to eliminate the ORDER
>function, and write a create table, of course, to emulate your
>example.
>
>Then I ran it on 8.1.5; the result of the select was .....
>
> NO ROWS RETURNED !!!!!
>
>even when I dropped the precision of the values back
>to number(6). I even did a DUMP(tdi_params) to
>demonstrate that the data stored wasn't causing
>a problem because of conversion rounding.
>
>
>On a simple select without a WHERE the values
>displayed showed the precision at which they
>had been entered.
>
>have we got bugs here, or what ?
>Come back T Kyte - all is forgiven.
I'm back....
this is a sqlplus issue that is fixed in 8.1. the numbers are OK -- the following block can be used to see that:
declare
x tdi_parameters_t;
begin
SELECT TDI_param into x
from TDI_parameters
WHERE TDI_param =
TDI_parameters_t(TDI_param_array_t(1.1, 2.2, 3.3, 4.4, 5.5, 6.6));
for i in 1 .. x.tdi_param_array.count loop
dbms_output.put_line( x.tdi_param_array(i) );
end loop;
sqlplus is defaulting the number format to 9 when displaying numbers in the varray. the following example (run in 8.0.3) demonstrates the issue and shows the workaround:
SQL> CREATE TYPE TDI_param_array_t AS VARRAY(100) OF NUMBER(6,2); 2 /
Type created.
SQL> CREATE TYPE TDI_parameters_t AS OBJECT ( 2 TDI_param_array TDI_param_array_t, 3 ORDER MEMBER FUNCTION isEqual (TDI_obj TDI_parameters_t) RETURNINTEGER
Type created.
SQL>
SQL> create or replace type body TDI_parameters_t
2 as
3 order member function isEqual(TDI_obj TDI_parameters_t) RETURN
INTEGER
4 is 5 begin 6 return 0; 7 end;
Type body created.
SQL>
SQL> create table tdi_parameters
2 ( x int, tdi_param TDI_parameters_t, y int, z number );
Table created.
SQL>
SQL> INSERT INTO TDI_parameters VALUES
2 ( 3, TDI_parameters_t(TDI_param_array_t(1.1, 2.2, 3.3, 4.4, 5.5, 6.6)), 1,
12.23);
1 row created.
SQL> SQL> SQL> SELECT TDI_param
TDI_PARAM(TDI_PARAM_ARRAY)
SQL> SQL> set numformat 999999.99999 SQL> SQL> SELECT TDI_param
TDI_PARAM(TDI_PARAM_ARRAY)
A client application (or plsql) will not need the numformat -- its just sqlplus. I ran in 8.1.5 and got this as the result:
SQL> CREATE TYPE TDI_param_array_t AS VARRAY(100) OF NUMBER(6,2); 2 /
Type created.
SQL> CREATE TYPE TDI_parameters_t AS OBJECT ( 2 TDI_param_array TDI_param_array_t, 3 ORDER MEMBER FUNCTION isEqual (TDI_obj TDI_parameters_t) RETURNINTEGER
Type created.
SQL>
SQL> create or replace type body TDI_parameters_t
2 as
3 order member function isEqual(TDI_obj TDI_parameters_t) RETURN
INTEGER
4 is 5 begin 6 return 0; 7 end;
Type body created.
SQL>
SQL> create table tdi_parameters
2 ( x int, tdi_param TDI_parameters_t, y int, z number );
Table created.
SQL>
SQL> INSERT INTO TDI_parameters VALUES
2 ( 3, TDI_parameters_t(TDI_param_array_t(1.1, 2.2, 3.3, 4.4, 5.5, 6.6)), 1,
12.23);
1 row created.
SQL> SQL> SQL> SELECT TDI_param
TDI_PARAM(TDI_PARAM_ARRAY)
SQL> SQL> set numformat 999999.99999 SQL> SQL> SELECT TDI_param
TDI_PARAM(TDI_PARAM_ARRAY)
Here, this shows sqlplus picks up on the scale correctly but still will obey a numformat if set. I did not have any issues with regards to not being able to select the data though, i could not reproduce that.
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jul 22 1999 - 15:56:56 CDT
 |
 |