Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle 10g Changing Default value of existing columns

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

604 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