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. prompt 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 @chg_datatype_to_varchar ---------------- End of script --------------
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.