Problem solve Get help with specific problems with your technologies, process and projects.

Changing the datatype of a column in 8.1.7

What's the best way (script) to alter a full table, changing the datatype of a column (number to varchar2) on Oracle 8.1.7?

It's easy if the table does NOT have any data in it, just do one or more ALTER TABLE statements to modify all number columns to varchar2 (see below for an automated script to do this).

But, if the table has data in it, it's going to be more trouble. If you have data, you won't be allowed to simply alter the table to modify the datatypes, you'll have to take the long route! That route involves exporting the table data, dropping the table, recreate the table using the new data types and import the data back in. There are actually multiple ways to get the data out and store it to put back in later, but export is the most common.

If you want to automate the creation of a script to handle the ALTER TABLE modifications, try this:

-------------------- Start of script ---------------------
set echo off
set lines 100
set pages 10000
set heading off
set feedback off

clear screen

prompt This script will spool a script to a .sql file which will list
prompt all the ALTER commands necessary to convert all number datatype
prompt columns in a table to varchar2.
accept v_table prompt 'Enter the table name=> '
accept v_owner prompt 'Enter the table owner=> '

spool chg_datatype_to_varchar.sql

select 'ALTER TABLE ' || '&v_table' || ' MODIFY ' || column_name ||
       ' VARCHAR2(' || data_precision || ');'
  from dba_tab_columns
 where table_name = upper('&v_table')
   and owner = upper('&v_owner')
   and data_type = 'NUMBER';

spool off

---------------- End of script --------------

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.