Hi Tom,

I have a question about number (usng 817 and 9i)

create table t1(c1 number, c2 number(6,3));

insert into t1(c1, c2)
values(1, 0.5);

insert into t1(c1, c2)
values(2, 0.500);

SQL> select * from t1;

        C1         C2
---------- ----------
         1         .5
         2       .5

I understand that Oracle does not store leading or trailing zeros.  Our VB developers want me to store 0.500 as 0.500 in the database, so they don’t need to change any code.  Here are the reasons:
(1)    They want VB front end show 0.5 instead of .5.
(2)    The VB code checks the trailing zero.  If it is 0.5, the result of this calculation of 0.5 * 0.111 will be 0.1.  It is 0.500, the result of this calculation of 0.500 * 0.111 will be 0.056.

Here are the solutions I can provide:
(1)    Use varchar2 for column c2  (I don’t like it).
(2)    For trailing zero, I can ask them to use to_char(c2, 999.999), but they have to modify the query code.

My questions are:
(1)    Since the scale of the column is 3, is it true that 0.5 and 0.500 are treated the same in the database (which is 0.500)? 
(2)    How do I add leading zero to the data and pass it to the VB?
(3)    Are there other ways to pass 0.500 to VB besides using to_char function?

Thanks so much for your help.


 

how about solution 3)  get coders that do their job?  that write software to specifications?  that fix bugs in their code?


A number is a number, it is not a string.  leading and trailing zeros are MEANINGLESS in a number.  they are "formats", for display (you know, the things the VB coders are responsible for)

If vb really says taht 0.5 * 0.111 = 0.1, then vb is not a language that is usable in any environment -- there has to be something else going on there.. that doesn't even begin to make sense

ops$tkyte@ORA920LAP> select 0.5 * 0.111 from dual
  2  /

 0.5*0.111
----------
     .0555

ops$tkyte@ORA920LAP>

my solution is and will be "tell the coders to do their jobs, they have a bug in their code.  they need to fix this bug". 

Thanks so much.

I still provided the query solution to VB developer in case they need it.

create table t1(c1 number, c2 number(6,3));

insert into t1(c1, c2)
values(1, 0.500);

insert into t1(c1 ,c2)
values(2, 100.50);


select (decode(instr(c2, '.'), 1,
concat('0', trim(to_char(c2, .999))),
to_char(c2, 9999.999))) from t1;

Hello Tom

I have a one line of record in a variable named vs_line.

I am sending the value along with this mail.

========================================================================================================================

35YYYXXXX ZZ 20041206124301168673E049 YG494030000MOHAMAD KAMAL B.ABD SAMAD 0000000000000000000028500129022824 WME7301 PCS5061053 0000LOT 4488 BATU 1 1/2 JLN KOTA RAJA 41000 KLANG SELANGOR CASH *
========================================================================================================================

The above is one line of data.

This is how I extract the data inside my procedure.

vc_strbnkprodfile := Trim(SUBSTR(vs_line,1,2 ));
vc_strcustabbvcd := Trim(SUBSTR(vs_line,3,3 ));
vc_strcustname := Trim(SUBSTR(vs_line,6,14 ));
vc_strtransdate := Trim(SUBSTR(vs_line,20,8 ));
vc_strtransbranch := Trim(SUBSTR(vs_line,28,4 ));
vc_strpayinnbr := Trim(SUBSTR(vs_line,32,8 ));
vc_strtellerid := Trim(SUBSTR(vs_line,40,8 ));
vc_strtellerterminal := Trim(SUBSTR(vs_line,48,4 ));
vc_strtranscd := Trim(SUBSTR(vs_line,52,4 ));
vc_strapplcname := Trim(SUBSTR(vs_line,59,40 ));
vc_stracctnbr := Trim(SUBSTR(vs_line,99,14 ));
vn_damnt := TO_NUMBER(trim (leading '0' from SUBSTR(vs_line,113,9)||'.'||SUBSTR(vs_line,122,2)));
dbms_output.put_line('Amount is ' ||vn_damnt );
vc_strrefnbr1 := Trim(SUBSTR(vs_line,124,30 ));
vc_strrefnbr2 := Trim(SUBSTR(vs_line,153,20 ));
vc_strsrccode := Trim(SUBSTR(vs_line,174,3 ));
vc_strnewicnbr := Trim(SUBSTR(vs_line,177,14 ));
vc_straddress1 := Trim(SUBSTR(vs_line,195,40 ));
vc_straddress2 := Trim(SUBSTR(vs_line,235,40 ));
vc_straddress3 := Trim(SUBSTR(vs_line,275,40 ));
vn_nchqdepday := Trim(SUBSTR(vs_line,315,2 ));
vc_cpmtmode := Trim(SUBSTR(vs_line,317,14));

=========================================================================================================

After extraction,in the variable named vn_damnt I am expecting 2850.01.

Where as the value getting stored is only 285. Where am I going wrong ? Do let me know.

Thanking you in anticipation

Hi Tom,

is it possible to limit the scale number at instance level:

example

SQL> select 1/3 from dual;

       1/3
----------
.333333333

in this case I have 9 digit after comma

I want 4 digit after comma without using the function "round",

there is a parameter to set ?


this problem is related to the Microsoft OracleDataAdapter
and the error is OracleException with Code=22053

OCI-22053: overflow error caused when retreiving valid data inserted using System.Data.OracleClient

thanks and best regards

Mauro

Thank you Tom,
they changed their adapter and all works well

best regards
Mauro

Hi Tom,
In SQL Developer, if you do this query:
select (7/3)*3 from dual;
You get:
6.99999999999999999999999999999999999999
In SQL*Plus you get:
(With default formatting):

SQL> select (7/3)*3 from dual;

   (7/3)*3
----------
         7

SQL> col test for 99999.999999999999999999999
SQL> r
  1* select (7/3)*3 test from dual

                        TEST
----------------------------
     7.000000000000000000000

SQL> col test for 0000000.00000000000000000
SQL> r
  1* select (7/3)*3 test from dual

                      TEST
--------------------------
 0000007.00000000000000000

SQL>

In PL/SQL one of our developer was having this issue with a pricing calculation, he "lost" one cent because we was doing truncate even when the formula was supposed to give back the same number at the beginning.

Is there a way to avoid this behavior in Oracle? It seems like SQL Developer shows the "real" output as it is calculated in PL/SQL.

At the end the developer was asked to first perform the multiplication and then the division and it worked. But i just don't like Oracle missing a simple math operation :S
(Working in AIX with 11gR1)

ops$tkyte%ORA10GR2> set numformat 99.99999999999999999999999999999999999999999999

ops$tkyte%ORA10GR2> select (7/3)*3 from dual;

                                         (7/3)*3
------------------------------------------------
  6.99999999999999999999999999999999999999000000

ops$tkyte%ORA10GR2> select (1/3)*3 from dual;

                                         (1/3)*3
------------------------------------------------
   .99999999999999999999999999999999999999990000