Re: Number datatype help?

From: shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 21 Nov 2007 12:08:53 +0100
Message-ID: <474411cf$0$245$e4fe514c@news.xs4all.nl>

<fitzjarrell_at_cox.net> schreef in bericht news:667bc55a-f58c-4b53-b1a1-5824d40c7f13_at_f13g2000hsa.googlegroups.com...

> On Nov 19, 12:31 pm, "CK" <c_kettenb..._at_hotmail.com> wrote:


>> > You first number has 8 digits of precision and a scale of 2 so you

>> > need to declare it as (8,2) rather than (6,2). A format of (6,2)
>> > would support only digits to the left of the decimal point
>>
>> Close but it still accepts 654321.000 and it should not. Any other ideas?
>> Thanks for your help.
>> ~ck
>>
>> "Mark D Powell" <Mark.Pow..._at_eds.com> wrote in
>> messagenews:9bdd1d64-c093-45ae-b048-c4f46739d47e_at_n20g2000hsh.googlegroups.com...
>>
>>
>>
>> > On Nov 19, 12:26 pm, "CK" <c_kettenb..._at_hotmail.com> wrote:
>> >> New to Oracle. How can I declare a variable NUMBER that can be
>> >> assigned:
>>
>> >> a. 654321.00
>>
>> >> b. Not 654321.000
>>
>> >> c. Not 7654321.00
>>
>> >> ?
>>
>> >> I am trying this code, but I am getting precision errors. Any ideas?
>> >> Is
>> >> my
>> >> understanding of precision and scale incorrect? I want a number that
>> >> can
>> >> be
>> >> 6 digits and 2 decimal places. What am I missing here? Thanks!
>>
>> >> --***********
>>
>> >> SET SERVEROUTPUT ON
>> >> SET VERIFY OFF
>> >> DECLARE
>> >> v_number NUMBER(6,2) := &sv_number;
>>
>> >> BEGIN
>> >> DBMS_OUTPUT.PUT_LINE(v_number);
>>
>> >> END;
>> >> /
>>
>> >> --************
>>
>> >> Cheers,
>> >> ~ck
>>
>> > You first number has 8 digits of precision and a scale of 2 so you
>> > need to declare it as (8,2) rather than (6,2). A format of (6,2)
>> > would support only digits to the left of the decimal point.
>>
>> > HTH -- Mark D Powell --- Hide quoted text -
>>
>> - Show quoted text -

>
> Code an exception:
>
> DECLARE
>   v_number NUMBER(8,2) := &&sv_number;
>   too_many_decimals exception;
>
>   pragma exception_init(too_many_decimals, -20001);
> BEGIN
>   if length(substr(&&sv_number,instr(&&sv_number,'.')+1)) = 3 then
>           raise too_many_decimals;
>   else
>           DBMS_OUTPUT.PUT_LINE(v_number);
>   end if;
> EXCEPTION
>   when value_error then
>           raise_application_error(-6502, 'Number too large');
>   when too_many_decimals then
>                raise_application_error(-20001, 'Too many decimal
> places');
>   when others then
>           raise_application_error(SQLCODE, SQLERRM);
>
> END;
> /
>
> This is not intended to be an exhaustive example, merely a starting
> point to get you going.
>
>
> David Fitzjarrell

And take care of language settings... this will fail when the decimal seperator becomes a ','.

Shakespeare Received on Wed Nov 21 2007 - 05:08:53 CST