?
Solved

Oracle 10g Changing Default value of existing columns

Posted on 2007-11-16
2
Medium Priority
?
12,579 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

801 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