OraFAQ Forum: SQL & PL/SQL » use of variable type NUMBER

Home » SQL & PL/SQL » SQL & PL/SQL » use of variable type NUMBER

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

use of variable type NUMBER [message #15867]

Thu, 28 October 2004 22:17

Vrinda V
Messages: 17
Registered: October 2004

Junior Member

When you give the variable type as NUMBER what is the
maximum size?

Thanks in advance

Report message to a moderator

Re: use of variable type NUMBER [message #15890 is a reply to message #15867]

Fri, 29 October 2004 12:30

Edward Stoever
Messages: 58
Registered: August 2002

Member

If you create a table, the limit is number(38) or 38 digits. Lets give it a try:

CREATE table mytable1 (col1 NUMBER(39) tablespace tools;

ORA-01727: numeric precision specifier is out of range (1 to 38)

Numbers in PL/SQL appear to be a little more forgiving. I can run this:

DECLARE
   x   NUMBER (42);
BEGIN
   x := 4;
END;
PL/SQL procedure successfully completed

But, not this:

DECLARE
   x   NUMBER (43);
BEGIN
   x := 4;
END;
ORA-06550: line 3, column 16:
PLS-00216: NUMBER precision constraint must be in range (1 .. 38)

Which is odd, because the error tells us the limit is 38, but it looks like the limit for PL/SQL is actually 42! Go figure! (do you really need a number that has more than 38 digits? What are you counting? Grains of sand on the beach?)

Have fun!
Edward Stoever -- www.database-expert.com

Report message to a moderator

Re: use of variable type NUMBER [message #15898 is a reply to message #15867]

Sat, 30 October 2004 00:16

Soham
Messages: 29
Registered: June 2004
Location: Mumbai

Junior Member

hi!
Use a number variable where yer data contains only numbers(fixed or floating point). number as large as to the power of 124 can be stored.
Define as
number(p,s) p: length of data
s: scale (maximum of 38)

refer to SQL & PL/SQL using ORACLE 8i & 9i
ivan bayross.
bpb publication.

Report message to a moderator

Re: use of variable type NUMBER [message #15901 is a reply to message #15890]

Sat, 30 October 2004 02:30

Vrinda V
Messages: 17
Registered: October 2004

Junior Member

Actually my doubt was

why do you use number (with no parenthesis)
when you use it as input/output parameters in procedure

Why is number(8,2) not working when used as input/output parameter in procedure

Thanks

Report message to a moderator

Re: use of variable type NUMBER [message #15915 is a reply to message #15901]

Mon, 01 November 2004 03:00

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

From the documentation (PL/SQL User's Guide and Reference, "PL/SQL Subprograms", Understanding PL/SQL Procedures):
----------------------------------------------------------------------

You cannot constrain the datatype of a parameter. For example, the following declaration of [i]acct_id[/i] is illegal because the datatype [i]CHAR[/i] is size-constrained:

[i]PROCEDURE reconcile (acct_id CHAR(5)) IS ...  -- illegal[/i]

----------------------------------------------------------------------
The same rules apply to parameters having the NUMBER datatype.

Report message to a moderator

Previous Topic:	Converting data into XML page
Next Topic:	procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 02 13:53:53 CDT 2025