Solved

Oracle 10g Changing Default value of existing columns

Posted on 2007-11-16
2
12,536 Views
Last Modified: 2013-12-19
I want to change the default value of the columns in OPracle 10g database. I want to be able to set the value to " " and 0 if the dependant on the column datatype. I want to be able to generate a script to alter the columns of the table.

Thanks
0
Comment
Question by:eryckop
2 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 250 total points
ID: 20302125
Let's start with creating a basic table:

SQL> create table fred (col1 number, col2 varchar2(5));

Table created.

We have no default values at this stage:

SQL> select table_name, column_name, data_type, data_default
  2  from dba_tab_columns
  3  where table_name = 'FRED';

TABLE_NAME COLUMN_NAM DATA_TYPE  DATA_DEFAULT
---------- ---------- ---------- --------------------
FRED       COL1       NUMBER
FRED       COL2       VARCHAR2

SQL>

The manual way to do this would be to do:

SQL> alter table fred modify col1 default 0;

Table altered.

SQL> alter table fred modify col2 default ' ';

Table altered.

Checking our default values again, you would see the following:

SQL> select table_name, column_name, data_type, data_default
  2  from dba_tab_columns
  3  where table_name = 'FRED';

TABLE_NAME COLUMN_NAM DATA_TYPE  DATA_DEFAULT
---------- ---------- ---------- --------------------
FRED       COL1       NUMBER     0
FRED       COL2       VARCHAR2   ' '

SQL>

We now have default values defined.

Now, to generate a script that did that, you could do something like:


SQL> select 'alter table '||table_name||' modify column '||column_name||' default '||decode(data_type,'NUMBER','0','VARCHAR2',''' ''')||';'
  2  from dba_tab_columns
  3  where table_name = 'FRED';

'ALTERTABLE'||TABLE_NAME||'
--------------------------------------------------------------------------------
alter table FRED modify column COL1 default 0;
alter table FRED modify column COL2 default ' ';

SQL>

You'd need to edit the decode statement to include options for other datatypes (CHAR, VARCHAR, etc) and then spool the output to a text file and edit it carefully before running it to ensure you don't set up any DATE fields etc.

See below:

SQL> alter table fred add (col3 date);

Table altered.

SQL> l
  1* alter table fred add (col3 date)
SQL> select 'alter table '||table_name||' modify column '||column_name||' default '||decode(data_type,'NUMBER','0','VARCHAR2',''' ''','*EDIT ME*')||';'
  2  from dba_tab_columns
  3  where table_name = 'FRED';

'ALTERTABLE'||TABLE_NAME||'
--------------------------------------------------------------------------------
alter table FRED modify column COL1 default 0;
alter table FRED modify column COL2 default ' ';
alter table FRED modify column COL3 default *EDIT ME*;

SQL>

Doing something like that lets you trap the unaccounted for fields that you can edit out before running the script.

You could also edit the decode to set up a default for DATE's as well:

SQL> select 'alter table '||table_name||' modify column '||column_name||' default '||decode(data_type,'NUMBER','0','VARCHAR2',''' ''','DATE','sysdate','*EDIT ME*')||';'
  2  from dba_tab_columns
  3  where table_name = 'FRED';

'ALTERTABLE'||TABLE_NAME||'
--------------------------------------------------------------------------------
alter table FRED modify column COL1 default 0;
alter table FRED modify column COL2 default ' ';
alter table FRED modify column COL3 default sysdate;

SQL>

You just need to edit the script to find what you want and you're off.  As always, carefully review any script you run against a production database where it's generated in that way and test it first!

Hope that helps!
0
 

Author Closing Comment

by:eryckop
ID: 31409664
Thanks sjwales, you are super.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question