Convert Datatype

[safedba]

Hi, I need help from u.

I want to convert a data type of one column in a Table. Currently that column is “NUMBER (6)” to be converted as “VARCHAR2 (6)”. Which is the best way u suggests doing it.

Details abt Table
Total No of Records: 50,000
Total No of Columns: 25
Only Varchar, Char, Number & Date data types in that table.

Expecting ur reply

Safedba

[SANJAY_G]

If the column is empty then you can directly use ALTER TABLE MODIFY command. Otherwise you will have to create a new table and insrt the data.

HTH

[safedba]

Mr.Sanjay ,

The column is not empty and moreover this is master table linked with two detail tables.
Actually i am looking, is there any way to covert datatype while doing Export or Import.

[SANJAY_G]

I don;t think you can do it by export/import.
The work around could be.

1. Create a temp table as
create table as select * from orig_table

2. Truncate or delete all rows from orig table.

3. Use alter table modify to change datatype.

4. Re-insert all rows into orig table.

5. drop the temp table.

You may need to disable the RI constraints before deleting. You can enable them again after inserting back the rows.

HTH

[jmodic]

How about

- adding new_column with datatype VARCHAR2(6)
- UPDATE TABLE SEt new_column = TO_CHAR(old_column)
- droping old_column from your table

[safedba]

Thanks to Sanjay & jmodic.

jmodic, my database version is 8.0.6.

[jmodic]

Originally posted by safedba
jmodic, my database version is 8.0.6.

GENERAL RULE: each and every technical question in this forums should have at least OS and Oracle release specified.
GENERAL RULE: each and every technical question in this forums should have at least OS and Oracle release specified.
GENERAL RULE: each and every technical question in this forums should have at least OS and Oracle release specified.
GENERAL RULE: each and every technical question in this forums should have at least OS and Oracle release specified.
GENERAL RULE: each and every technical question in this forums should have at least OS and Oracle release specified.
GENERAL RULE: each and every technical question in this forums should have at least OS and Oracle release specified.
GENERAL RULE: each and every technical question in this forums should have at least OS and Oracle release specified.
GENERAL RULE: each and every technical question in this forums should have at least OS and Oracle release specified.
GENERAL RULE: each and every technical question in this forums should have at least OS and Oracle release specified.
GENERAL RULE: each and every technical question in this forums should have at least OS and Oracle release specified.
GENERAL RULE: each and every technical question in this forums should have at least OS and Oracle release specified.