Home » SQL & PL/SQL » SQL & PL/SQL » verry urgent..how to insert 123.45(char) to another table column of datatype number
verry urgent..how to insert 123.45(char) to another table column of datatype number [message #18605] Thu, 07 February 2002 05:28 Go to next message
ksr
Messages: 112
Registered: January 2002
Senior Member
Hi,
I have 2 tables
tab1 (col1 varchar2(10) --have some records like 123.45,4566.34
tab2 (col1 number)

Now,how to insert into tab2 from tab1.
i used insert into tab1 select * from tab2 which gives me ora-01722 invalid number.
How can i transfer the data.

I cannot change the datatype of tab2 (col1 number).
There is no precision defined for this column

Is there any other way like rounding off or something
Re: verry urgent..how to insert 123.45(char) to another table column of datatype number [message #18607 is a reply to message #18605] Thu, 07 February 2002 05:39 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
10:35:08 ==> create table tab1 (col1 varchar2(10));

Table created.

10:35:16 ==> create table tab2 (col1 number);

Table created.

10:35:27 ==> insert into tab1 values('123.45');

1 row created.

10:35:45 ==> insert into tab1 values('4566.55');

1 row created.

10:36:07 ==> insert into tab2 (col1)
10:36:19 2 (select to_number(col1,'999999.99') from tab1);

2 rows created.

10:36:51 ==> select * from tab2;

COL1
---------
123.45
4566.55
Re: verry urgent..how to insert 123.45(char) to another table column of datatype number [message #18616 is a reply to message #18605] Thu, 07 February 2002 08:02 Go to previous messageGo to next message
ksr
Messages: 112
Registered: January 2002
Senior Member
Hi,
i dont have any other data except the 2 rows
table1 (col1 varchar2(10))
----------
col1 data
--------
123.45
456.45

I have another table,table2 (col1 number)

if i say
insert into table2 (select to_number(col1) from table1) .. it is giving ora-01722 error invalid number

what is the way aroud
Re: verry urgent..how to insert 123.45(char) to another table column of datatype number [message #18620 is a reply to message #18605] Thu, 07 February 2002 09:45 Go to previous message
INTROV
Messages: 20
Registered: February 2002
Junior Member
Check the data in tab2. You might have non numeric value in tab2.
Previous Topic: Re: ORA-02291
Next Topic: Query on message at login
Goto Forum:
  


Current Time: Fri Sep 24 14:19:55 CDT 2021