Solved

Oracle 10g Changing Default value of existing columns

Posted on 2007-11-16
2
12,516 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.

Join & Write a Comment

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…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now