need to modify the column datatype from varchar to number [message #649801] |
Wed, 06 April 2016 08:39 |
|
saipavan.plsql
Messages: 17 Registered: February 2015 Location: chennai
|
Junior Member |
|
|
Hi guys,
i have an issue while modifing the column datatype from varchar2 to number
while altering the column i am facing the ORA-01439 column to be modified must be empty to change datatype.
alter table EQTY_DIST_NET_PROCEEDS modify TAX_REQUEST_UId number(38,0);
but the data in the column are in numbers only.
i know this way by --> creating the other column and updating the column with this data and renaming the column.
but i cant do this way.
is there any other way of altering the column datatype from varchar2 to number without doing the above process in a single quary.
tnx guys
|
|
|
|
|
Re: need to modify the column datatype from varchar to number [message #650219 is a reply to message #649812] |
Tue, 19 April 2016 13:23 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
There is an easy way to do it. See the following steps. This method allows you to change the column type without changing the layout of the table, but change the column type.
>create table whb as
2 select owner,object_name,to_char(object_id) object_id
3 from all_objects;
Table created.
>desc whb
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
OBJECT_ID VARCHAR2(40)
>alter table whb add work number;
Table altered.
>update whb set work = to_number(object_id);
13392 rows updated.
>update whb set object_id = null;
13392 rows updated.
>alter table whb modify object_id number;
Table altered.
>update whb set object_id = work;
13392 rows updated.
>alter table whb drop column work;
Table altered.
>desc whb
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
OBJECT_ID NUMBER
>
[Updated on: Tue, 19 April 2016 13:26] Report message to a moderator
|
|
|