Home » SQL & PL/SQL » SQL & PL/SQL » convert DataTypes
convert DataTypes [message #230539] Thu, 12 April 2007 03:24 Go to next message
craftman1
Messages: 15
Registered: April 2007
Location: Pakistan
Junior Member
Oracle version: 8.0.5 For NT/XP/2000
...
i wan to modify the data types which is not empty. when i want to modify its datatype. it gigve mne error. that colums in nopt empty.pls advised me how i can modify the data type of colums which is not empty.??????
Current Data Type: --> Want to Convert Into this
Field: Datatype:
Sarsai number(5) --> Varchar2(40)

regards
Shahzad

Re: convert DataTypes [message #230544 is a reply to message #230539] Thu, 12 April 2007 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.
There are many ways to workaroung this.

1/
add a new column of correct datatype and fill it with the values, then rename the table and create a view upon it to hide the old column and rename the new one to the old name. You have to recreate all grants.

2/
create a new table (create table as select) with the correct datatypes, drop the old table, rename the new one. You have to recreate all constraints, indexes, grants...

3/ ...

Regards
Michel

Re: convert DataTypes [message #230554 is a reply to message #230544] Thu, 12 April 2007 03:45 Go to previous messageGo to next message
craftman1
Messages: 15
Registered: April 2007
Location: Pakistan
Junior Member
but i am using Oracle.8.0.56 i have a bulk data .so i cannot get or create the new table.pls help me/.
regards
Re: convert DataTypes [message #230562 is a reply to message #230554] Thu, 12 April 2007 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another way:

create a table with only the data you have in the field and the associated rowid. Empty the original field, change its type and refill the field.

Regards
Michel
Re: convert DataTypes [message #230569 is a reply to message #230562] Thu, 12 April 2007 04:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Thu, 12 April 2007 10:58
Another way:

create a table with only the data you have in the field and the associated rowid. Empty the original field, change its type and refill the field.
Are ROWIDs fixed?

MHE
Re: convert DataTypes [message #230586 is a reply to message #230569] Thu, 12 April 2007 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Most of the time, yes. They are exceptions with IOT, partitions, alter table shrink compact...
But you can assure it if you have a maintenance operation (like this one) to do on a table.

Regards
Michel

Re: convert DataTypes [message #230591 is a reply to message #230586] Thu, 12 April 2007 04:41 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I can live with that, yes.

MHE
Previous Topic: Hours:MM:Seconds
Next Topic: give the sql query for the below scenario
Goto Forum:
  


Current Time: Thu Dec 08 18:45:47 CST 2016

Total time taken to generate the page: 0.22881 seconds