Re: Number datatype help?

From: <fitzjarrell_at_cox.net>
Date: Mon, 19 Nov 2007 11:18:16 -0800 (PST)
Message-ID: <667bc55a-f58c-4b53-b1a1-5824d40c7f13@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 Received on Mon Nov 19 2007 - 13:18:16 CST