SQL & PL/SQL

alter table DWSODS01.DWT00301_ORD_DTL_OMS add (
COMB_ORD_FLG NUMBER(5,0) default 0 not nulll); 

alter table DWSODS01.DWT00301_ORD_DTL_OMS add (
COMB_ORD_NO NUMBER(12,0) default 0 not null); 

>
I tried this, but didn't work
>

Very intuitive way to describe your problem. Try this

alter table DWSODS01.DWT00301_ORD_DTL_OMS 
add (
            COMB_ORD_FLG NUMBER(5,0) not null default 0 , 
            COMB_ORD_NO NUMBER(12,0) not null default 0
       ); 

Regards

Raj

P.S : Not tested

Since you are specifying default values, it should work (regardless if table DWSODS01.DWT00301_ORD_DTL_OMS is empty or not):

SQL> select count(*) from emp1
  2  /

  COUNT(*)
----------
        14

SQL> alter table emp1 add (
  2  COMB_ORD_FLG NUMBER(5,0) default 0 not null, 
  3  COMB_ORD_NO NUMBER(12,0) 
  4  default 0 not null); 

Table altered.

SQL> desc emp1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
 COMB_ORD_FLG                              NOT NULL NUMBER(5)
 COMB_ORD_NO                               NOT NULL NUMBER(12)

SQL> 

What error are you getting?

SY.

Unfortunatly you don't tell us why it didn't work.

Assuming the table already contains data, yes, you'll have to split up.
Something like:

alter table dwsods01.dwt00301_ord_dtl_oms 
add ( comb_ord_flg number(5,0) default 0
    , comb_ord_no number(12,0) default 0 
    ); 

update dwsods01.dwt00301_ord_dtl_oms
set    comb_ord_flg = 0    
,      comb_ord_no = 0;

alter table dwsods01.dwt00301_ord_dtl_oms 
modify ( comb_ord_flg not null
       , comb_ord_no not null 
       );

That would be true if defualt values were not supplied.

SY.

Fine,

You go for this and you will be waiting for a really long time as you are first inserting 0s in all the rows to just update all the 0s back to 0s again. So in a long table you will be multiplying your times by 2.

So, better do this:


alter table dwsods01.dwt00301_ord_dtl_oms
add ( comb_ord_flg number(5,0) null
, comb_ord_no number(12,0) null
);

update dwsods01.dwt00301_ord_dtl_oms
set comb_ord_flg = 0
, comb_ord_no = 0;

alter table dwsods01.dwt00301_ord_dtl_oms
modify ( comb_ord_flg not null
, comb_ord_no not null
);


LW

Yes, you're right, just saw your example. Thanks.
Last monday I happened to have to add 2 not null columns myself, without a default, so I needed to split the operation.
That jumped immediatly into my mind. But that situation doesn't apply to OP.

Thanks, I already got the point.
I've had this before when posting untested examples... ;)

The table already has huge data.

This is working

alter table dwsods01.dwt00301_ord_dtl_oms
add ( comb_ord_flg number(5,0) not null
, comb_ord_no number(12,0) not null
);

So after that should i have to write a statement for default like this

alter table dwsods01.dwt00301_ord_dtl_oms
modify ( comb_ord_flg default 0
, comb_ord_no default 0
);

I am affraid that will not work.

Do the update for providing default values after the first alter and the finish with the last update for not null and future default values to 0.

LW

And I bet you were not patient enough to wait for ALTER TABLE tofinish.
> The above will only apply to future inserts. Existing rows still have NULL as comb_ord_flg and comb_ord_no values. After running the above, you will have to issue:

update dwsods01.dwt00301_ord_dtl_oms set comb_ord_flg = 0,
comb_ord_no = 0
/
alter table dwsods01.dwt00301_ord_dtl_oms 
modify ( comb_ord_flg  not null
     , comb_ord_no not null 
     );

SY.

I tried all the codes but the code which lawrence gave will work for my scenario i think
I ran only first statement of this code

alter table dwsods01.dwt00301_ord_dtl_oms
add ( comb_ord_flg number(5,0) null
, comb_ord_no number(12,0) null
);


update dwsods01.dwt00301_ord_dtl_oms
set comb_ord_flg = 0
, comb_ord_no = 0;


alter table dwsods01.dwt00301_ord_dtl_oms
modify ( comb_ord_flg not null
, comb_ord_no not null
);


But can i run the whole statement at a time or should i do three statements saperatley.
And one thing i didn't get is that at first he is making columns to null and setting to 0 and making not null. I didn't get logic in this

Edited by: user10390682 on Dec 10, 2009 8:51 AM

Also:

Keep in mind that, depending on how huge the data is, modifying columns to not null can be time consuming as well....

Good one.

LW

Guys big mistake has taken place

I ran this code

alter table dwsods01.dwt00301_ord_dtl_oms
add ( comb_ord_flg number(5,0) null
, comb_ord_no number(12,0) null
);

And after that i made two columns default 0

ALTER TABLE DWSODS01.DWT00301_ORD_DTL_OMS
MODIFY ( COMB_ORD_FLG DEFAULT 0,
COMB_ORD_NO DEFAULT 0);

But now while running this statement

alter table dwsods01.dwt00301_ord_dtl_oms
modify ( comb_ord_flg not null
, comb_ord_no not null
);

Error report:
SQL Error: ORA-02296: cannot enable (DWSODS01.) - null values found
02296. 00000 - "cannot enable (%s.%s) - null values found"
*Cause: an alter table enable constraint failed because the table
contains values that do not satisfy the constraint.
*Action: Obvious

Now it is not changing to NOT NULL

What should i do
Please help

Did you read my reply??? I explained you will need to update existing rows. Again, run:

update dwsods01.dwt00301_ord_dtl_oms set comb_ord_flg = 0,
comb_ord_no = 0
/ 
alter table dwsods01.dwt00301_ord_dtl_oms 
modify ( comb_ord_flg  not null
     , comb_ord_no not null 
     );

SY.

I dont need to update any rows
I just need to add columns


I didn't get what you said

Edited by: user10390682 on Dec 10, 2009 9:43 AM

OK. I will chew it up for you. Your table had N rows. You issued:

alter table dwsods01.dwt00301_ord_dtl_oms 
add ( comb_ord_flg number(5,0) null
, comb_ord_no number(12,0) null
); 

Now it has same N rows with extra 2 columns and these column values are NULL. Then you issued:

ALTER TABLE DWSODS01.DWT00301_ORD_DTL_OMS
MODIFY ( COMB_ORD_FLG DEFAULT 0,
COMB_ORD_NO DEFAULT 0);

As many people already told you, this only applies to future inserts. Existing N rows still have NULL as column comb_ord_flg and comb_ord_no values. That is why you must update these N rows to set column comb_ord_flg and comb_ord_no values to 0 before you can alter them to NOT NULL. Hope I explained it now.

SY.

You need to update the rows. Do what Solomon is instructing to you and you will be fine.

The update will change the nulls for the default values of 0 for all the current rows in the new added columns.

LW

Okay i got what you said

As told by my boss i gave to do it fo rfuture inserts only not for present data
I dont need to worry abour present data.
This should work for only future inserts

Is there any way now to change them to NOT NULL

Edited by: user10390682 on Dec 10, 2009 9:47 AM

Edited by: user10390682 on Dec 10, 2009 9:51 AM

Not via NOT NULL constraint. You could create before insert or update row level trigger that will check column values for null and raise error if column is null. If table has (or you are willing to add) insert/last update column, you could add check constraint.

SY.

Guys please help.

I tried to drop those two columns and though of adding again, but i have no grants for that.

Please say me if there are ways to solve it

Yes, but in order to accomplish that, you need to make sure that not a single column value is null before issuing the 'alter table modify columns to not null'-statement...
Because: for your present data, you also have two new columns, without any value, they're null.
You need to update those records first.
You cannot have null columns for present data and not null columns for future data.

Defining null or not null columns affects ALL data, past, present and future.

So:
update first, then modify.

What are you trying to do? If you want to add NOT NULL column(s) to not empty table you must either provide column default value which will be assigned as column value for all existing rows, or you can add column as NULL, update table and set column value for existing rows to, for example, -1 to indicate this is an existing "before column was added" row, then modify column with DEFAULT 0 and NOT NULL. Obviously, -1 must not present any issues to your app logic. In fact, if your version allows, I would set existing column values to BINARY_FLOAT_NAN or BINARY_FLOAT_INFINITY.

SY.

But i cant update those two columns with any values.
I shouldn't do that

So what are you trying to achive? You want to add two columns to anon-empty tableand make sure future inserts/updates would not be able to insert NULLs as column values while existing rows continue with NULL column values? IF so, as I already meantioned, instead of setting columns to NOT NULL, create a trigger that will check the requirements and will raise an error if new row value is null.

SY.

I understood what you have said.

But inorder to make difference in those two columns between the present data and future data i shouldn't update.

I have a doubt.
If i add those two columns as NOT NULL and default it to 0, only future data will show 0 as you said. Then how will all the old and present data will be for those two columns.
Will it be lik NOT NULL in all the rows for present and old data?

No, you didn't, and this is why I think so:
No, since your old/present data has NULL values for your new columns, you'll never be able to enable a NOT NULL constraint on those column unless you update them with some value before making the new columns not null.
Constraints (not null) don't use 'time'. All data is affected.

Solomon has provided you several ways to deal with it.
So: go get some coffee or a soda, take a deep breath and reread your thread again.

In a way this reminds me of the 'chicken and egg' story ;)

So use the NOVALIDATE option:

SQL> create table t (a integer primary key, b integer)
/
Table created.

SQL> insert into t values (1, null)
/
1 row created.

SQL> insert into t values (2, null)
/
1 row created.

SQL> alter table t modify b  default 0 not null novalidate
/
Table altered.

SQL> insert into t values (3, null)
Error at line 16
ORA-01400: cannot insert NULL into ("MICHAEL"."T"."B")

SQL> insert into t (a) values (3)
/
1 row created.

SQL> select * from t
/
         A          B
---------- ----------
         1           
         2           
         3          0

3 rows selected.

I am adding those two new columns just today.
If i write a code like this (before this statement those two columns doesn't exist at all)

ALTER TABLE DWT00301_ORD_DTL_OMS
ADD( COMB_ORD_FLG NUMBER(5,0) NOT NULL,
COMB_ORD_NO NUMBER(12, 0) NOT NULL);

Then the table will be altered (even though it takes a lot of time). Here comes my doubt. So now those two columns are created, will all the rows of those two columns will look like NOT NULL?
If i write as NULL all rows will look like NULL. But if i write as NOT NULL wil all the rows be populated by 'NOT NULL'

After that i will again run the below code

ALTER TABLE DWT00301_ORD_DTL_OMS
MODIFY( COMB_ORD_FLG DEFAULT 0,
COMB_ORD_NO DEFAULT 0);

So the default code will set to 0 now. As he said only future inserts of that columns wil be shown as 0 not old and present data.

Last attempt. If table is not empty
will fail because new column values will be set to NULL. If you do:
it will succeed becuase new column values will be set to default value of 0. In your original email yiou said:
but you never provided an error it failed with despite of many people told you it should not have failed. So show us the error you are getting when runing your original statement.

SY.

OK. You're mixing up things.
NOT NULL isn't providing any 'default' value at all for your present data.
It's not a 'column value'. It's nothing, it's unknown.
NULL = unknown to Oracle, Oracle cannot do anything with the column value.
NOT NULL = known to Oracle,*some value is, has to be present* present in the column.
See:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/datainte.htm#CNCPT1641

So, if you want to make a column NOT NULL, values have to be there for ALL RECORDS.
Past records, future records etc.
After that, you can make the column NOT NULL.

Ok so if NOT NULL then there should be a value.

So when i define those two columns as NOT NULL will only future data should be NOT NULL.
What about old and present data if i define as NOT NULL

When i run as
ALTER TABLE DWT00301_ORD_DTL_OMS
ADD( COMB_ORD_FLG NUMBER(5,0) NOT NULL,
COMB_ORD_NO NUMBER(12, 0) NOT NULL);

With what values will the two new columns be populated?

In the link you gave it is written "You can only add a column with a NOT NULL constraint if the table does not contain any rows or if you specify a default value."

So will it return an error if i run the above code?

Should i run like this keeping two statements together

ALTER TABLE DWT00301_ORD_DTL_OMS
ADD( COMB_ORD_FLG NUMBER(5,0) NOT NULL,
COMB_ORD_NO NUMBER(12, 0) NOT NULL);
ALTER TABLE DWT00301_ORD_DTL_OMS
MODIFY ( COMB_ORD_FLG DEFAULT 0,
COMB_ORD_NO DEFAULT 0)

Edited by: user10390682 on Dec 10, 2009 12:52 PM

You can think about using a CHECK constraint instead: "Old" rows will have NULL in the new column while future data must provide NOT NULL data:

SQL> create table emp2 as select * from emp
/
Table created.

SQL> alter table emp2 add new_col integer check (new_col is not null)
/
Table altered.

SQL> insert into emp2(empno, new_col) values (1, null)
/
Error at line 13
ORA-02290: check constraint (MICHAEL.SYS_C0050953) violated

SQL> insert into emp2(empno, new_col) values (1, 2)
/
1 row created.