Home » SQL & PL/SQL » SQL & PL/SQL » how to give input such as 100.00 or 23.78 or 80.34
how to give input such as 100.00 or 23.78 or 80.34 [message #234211] Mon, 30 April 2007 08:18 Go to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi pals

i have created a table


SQL> create table staff(name varchar2(20),sal number(3,2));

Table created.



now i have tried to enter the data as follows


SQL> insert into staff values('&name',&sal);
Enter value for name: senthil
Enter value for sal: 100.00
old   1: insert into staff values('&name',&sal)
new   1: insert into staff values('senthil',100.00)
insert into staff values('senthil',100.00)
                                   *
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column

SQL> /
Enter value for name: senthil
Enter value for sal: 100,00
old   1: insert into staff values('&name',&sal)
new   1: insert into staff values('senthil',100,00)
insert into staff values('senthil',100,00)
            *
ERROR at line 1:
ORA-00913: too many values

SQL> /
Enter value for name: senthil
Enter value for sal: 100 00
old   1: insert into staff values('&name',&sal)
new   1: insert into staff values('senthil',100 00)
insert into staff values('senthil',100 00)
                                       *
ERROR at line 1:
ORA-00917: missing comma



so i am not able to enter the data into sal data field.




how to enter the data into sal data field.
Re: how to give input such as 100.00 or 23.78 or 80.34 [message #234215 is a reply to message #234211] Mon, 30 April 2007 08:26 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You have defined the field as number(3,2).

That means you can enter 3 digits, and two of them are behing the decimal separator.

Which means you can only insert values between -9.99 and +9.99

Re: how to give input such as 100.00 or 23.78 or 80.34 [message #234216 is a reply to message #234211] Mon, 30 April 2007 08:28 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
number(5,2) but why not just make it (11,2) to be safe?
Explicitly name columns when doing an INSERT with VALUES clause or else when you change the table structure, all of your INSERT statement will now fail.
Re: how to give input such as 100.00 or 23.78 or 80.34 [message #234219 is a reply to message #234211] Mon, 30 April 2007 08:32 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
First of all: read those manuals. Instead of what you suspect is a definition of a NUMBER(3,2) NOT a number with 3 positions left and 2 right of the comma. No, it is a number of 3 positions that uses 2 of those as decimal part. So you're left with a range of -9.99 to 9.99.

My test script:
create table yourtable(name varchar2(20),sal number(3,2))
/

insert into yourtable values('A',9.99);
Prompt A value of 10 is too large for the column:
insert into yourtable values('B',10);
Prompt A value of -9.99 will pass:
insert into yourtable values('C',-9.99);

select * from yourtable
/

drop table yourtable
/


My run:
SQL> @orafaq

Table created.


1 row created.

A value of 10 is too large for the column:
insert into yourtable values('B',10)
                                 *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


A value of -9.99 will pass:

1 row created.


NAME                        SAL
-------------------- ----------
A                          9.99
C                         -9.99


Table dropped.


MHE

Edit: Wow! I'm getting slow.

[Updated on: Mon, 30 April 2007 08:32]

Report message to a moderator

Previous Topic: UTL_FILE problem (merged)
Next Topic: nth business day
Goto Forum:
  


Current Time: Fri Dec 09 21:27:58 CST 2016

Total time taken to generate the page: 0.05915 seconds