Monday, October 20, 2008

Oracle 10g: Change from datatype number to boolean

I was supposed to change a field's datatype from integer to boolean. It took me quite a while to disentangle the problem...so I figure out that table alteration (scripting) in oracle 10g is quite confusing compared to SQL 2000. I kept on receiving this type of error:

*** ORA-01451: column to be modified to NULL cannot be modified to NULL ***
There are multiple solutions being suggested through out the net....but I chose to keep it stupidly simple to solve it....
  
1. Column to be modified must be empty to change datatype...What I did was:
UPDATE [table_name]
SET [column_name] = NULL

UPDATE GBL_DOCUMENT_MST
SET ISPAYMENT = NULL
  
2. Now....I can alter the table successfully...

Alter table GBL_DOCUMENT_MST
modify
(
ISPAYMENT CHAR(1) CHECK (ISPAYMENT IN ( 'T', 'F' ))
);
  
3. Time to Execute!...

2 comments:

nagakeciks said...

somebody gonna get hurt , badly

somebody..

peah @ peps said...

Take..and go...

Take-and-go..