Kazi . . . Thanks for the suggestion. I actually ended up doing it the
way you have suggested. Your reply has helped confirm that what I did
was the most beneficial way to proceed.
Brad
-----Original Message-----
From: kazi [mailto:kazimir.[Email address protected]
Sent: Thursday, April 24, 2008 11:50 PM
To: Losito, Bradley - Salt Lake City, UT
Subject: Re: Change column data type from varchar2 to number
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
What you suggest will cause changed placement of your column inside
table. If you are satisfied
with that, then ok. However I was always faced to problem how to keep
all the table structure,
just to change data type of single column without change of order of
columns or name of columns.
So:
alter table x add column x varchar2(20);
update x set x=num_field;
update x set num_field=null;
alter table x modify num_field varchar2(30);
update x set num_field=x;
alter table x drop column x;
KAzi
Losito Bradley - Salt Lake City UT wrote:
> Is there a reason / advantage to creating a new table versus just
adding
> a new column, populating it, dropping the original column and then
> renaming the new column to the original column name? I have read
> somewhere else about using the "Create table" option as well.
>
> Thanks!
>
> Brad
>
>
>
> -----Original Message-----
> From: Gombosi Andras
> [mailto:oracledba-ezmlmshield-x25029474.[Email address protected]
> Sent: Thursday, April 24, 2008 9:41 AM
> To: LazyDBA Discussion
> Subject: RE: Change column data type from varchar2 to number
>
> Create table table_new as
> (
> Select to_char(number_field, the format switches you want )
> >From table_old
> )
>
> -- check the new table, and if you are satisfied
>
> Drop table_old
>
> Create table table_old as (select * from table_new)
>
> Drop table_new
>
> _______________________________________________________
> Andras Gombosi
> Software Engineer
> Andras.[Email address protected]
> Irish Life Investment Managers, Loc. B42
> Beresford Court,Beresford Place,D1
> 353-1-7042836
>
> -----Original Message-----
> From: Losito Bradley - Salt Lake City UT
> [mailto:oracledba-ezmlmshield-x10107787.[Email address protected]
> Sent: 24 April 2008 16:37
> To: LazyDBA Discussion
> Subject: RE: Change column data type from varchar2 to number
>
>
> Oops . . . I want to change from number to varchar2. There are no
> indexes or constraints dependent on the column.
>
> Brad
>
> ********************************************
>
> -----Original Message-----
> From: Losito Bradley - Salt Lake City UT
> [mailto:oracledba-ezmlmshield-x6657100.[Email address protected]
> Sent: Thursday, April 24, 2008 9:25 AM
> To: LazyDBA Discussion
> Subject: Change column data type from varchar2 to number
>
> What's the best way to change the column data type from varchar2 to
> number? I am contemplating two options:
>
> 1) ALTER TABLE . . .
>
> 2) Create a new column, populate the new column from the old column,
> and then drop the old column.
>
> Any suggestions would be appreciated!
>
> Brad
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
> Irish Life Investment Managers winner of the Investment Management
KPMG
> Financial Services Excellence Award 2007
>
>
************************************************************************
> **********
> Irish Life Investment Managers Limited is authorised by the Financial
> Regulator. Irish Life Investment Managers Limited Registered Office:
> Beresford Court, Beresford Place, Dublin 1. Registered in Ireland
> Number 116000
>
> While Irish Life Investment Managers uses reasonable efforts to
ensure
> that the information contained in this email is current, accurate and
> complete at the date of publication, no representations or warranties
> are made (express or implied) as to the reliability, accuracy or
> completeness of such information. Irish Life Investment Managers
> therefore cannot be held liable for any loss arising directly or
> indirectly from the use of, or any action taken in reliance on, any
> information contained in this email.
>
> This material is for information only and does not constitute an
offer
> or recommendation to buy or sell any investment, or subscribe to any
> investment management or advisory service. It is intended for the use
of
> institutional and other professional investors. Past performance is
not
> indicative of future results. The value of funds we manage may fall
as
> well as rise.
>
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
are
> addressed. If you have received this email in error please notify the
> system manager. This footnote also confirms that this email message
has
> been swept for the presence of computer viruses.
>
************************************************************************
> **********
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
Oracle LazyDBA home page