?
Solved

Oracle 10g Changing Default value of existing columns

Posted on 2007-11-16
2
Medium Priority
?
12,628 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 23

Accepted Solution

by:
Steve Wales earned 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

571 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