OraFAQ Forum: SQL & PL/SQL » need to modify the column datatype from varchar to number

Home » SQL & PL/SQL » SQL & PL/SQL » need to modify the column datatype from varchar to number

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

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

Report message to a moderator

Re: need to modify the column datatype from varchar to number [message #649802 is a reply to message #649801]

Wed, 06 April 2016 10:25

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You have 2 choices (assuming you want to keep the data):
- create a new column with correct data type, copy the data to the new column and remove the previous one
- create a stage table with the data of the column, empty it, change the data type and move back the data.

Also, the least respect to those who help you is to feedback when they spend time for you.
So please review your previous topics.

Report message to a moderator

Re: need to modify the column datatype from varchar to number [message #649812 is a reply to message #649802]

Wed, 06 April 2016 23:52

saipavan.plsql
Messages: 17
Registered: February 2015
Location: chennai

Junior Member

Sure Michel ,

Thanks for your replay

Report message to a moderator

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

Previous Topic:	TO_DATE formating
Next Topic:	Ids of unique error type

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 10:49:55 CDT 2024