1 PACKAGE BODY QP_NUMBER as
2 /* $Header: QPNUMBRB.pls 120.0.12020000.2 2012/09/25 07:20:04 dnema ship $ */
3
4 C_FORMAT constant varchar2(100) := 'FM999999999999999999999.9999999999999999999999999999999999999999';
5
6 --
7 -- Canonical functions
8 --
9
10 function canonical_to_number(
11 canonical varchar2)
12 return number IS
13 decimal_char varchar2(1);
14 begin
15 --return to_number(canonical, canonical_mask);
16 if (canonical_mask <> C_FORMAT) then
17 return to_number(canonical, canonical_mask);
18 end if;
19
20 decimal_char := substr(ltrim(to_char(.3,'0D0')),2,1);
21 return round(to_number(translate(canonical, '.', decimal_char)), 20);
22
23 end canonical_to_number;
24
25 function number_to_canonical(
26 numberval number)
27 return varchar2 IS
28 decimal_char varchar2(1);
29 begin
30 --return rtrim(to_char(numberval, canonical_mask),'.');
31 if (canonical_mask <> C_FORMAT) then
32 return rtrim(to_char(numberval, canonical_mask),'.');
33 end if;
34
35 decimal_char := substr(ltrim(to_char(.3,'0D0')),2,1);
36 return translate(to_char(round(numberval, 20)), decimal_char, '.');
37
38 end number_to_canonical;
39
40 -- use 'set serverout on;' to see the output from this test program
41
42 procedure test is
43 pi number := 3.1415;
44 my_char varchar2(20);
45 begin
46 /*
47 DBMS_OUTPUT.PUT_LINE('Decimal separator is '||qp_number.decimal_char);
48 DBMS_OUTPUT.PUT_LINE('Group separator is '||qp_number.group_separator);
49 DBMS_OUTPUT.PUT_LINE('Canonical mask is '||qp_number.canonical_mask);
50
51 DBMS_OUTPUT.PUT_LINE('Canon number is '||qp_number.number_to_canonical(pi));
52 DBMS_OUTPUT.PUT_LINE('and back is '||to_char(qp_number.canonical_to_number('3.14')));
53 DBMS_OUTPUT.PUT_LINE('Canon integer is '||qp_number.number_to_canonical(4));
54 select qp_number.number_to_canonical(pi)
55 into my_char
56 from dual;
57
58 DBMS_OUTPUT.PUT_LINE('Canon number from SQL is '||my_char);
59
60 */
61 NULL;
62 end;
63
64
65 procedure initialize is
66
67 begin
68 --canonical_mask := 'FM999999999999999999999.99999999999999999999';
69 canonical_mask := C_FORMAT;
70 decimal_char := substr(ltrim(to_char(.3,'0D0')),2,1);
71 group_separator := substr(ltrim(to_char(1032,'0G999')),2,1);
72 end ;
73
74
75 begin
76 --canonical_mask := 'FM999999999999999999999.99999999999999999999';
77 canonical_mask := C_FORMAT;
78 -- OK, this is a bit kludgey, but I can't seem to find any way to access
79 --XS the numeric characters directly.
80 decimal_char := substr(ltrim(to_char(.3,'0D0')),2,1);
81 group_separator := substr(ltrim(to_char(1032,'0G999')),2,1);
82 end QP_NUMBER;