Modify a column to NULL - Oracle
oracle11g
Solution
You can use the column NULLABLE in USER_TAB_COLUMNS. This tells you whether the column allows nulls using a binary Y/N flag.
If you wanted to put this in a script you could do something like:
declare
l_null user_tab_columns.nullable%type;
begin
select nullable into l_null
from user_tab_columns
where table_name = 'CUSTOMER'
and column_name = 'CUSTOMER_ID';
if l_null = 'N' then
execute immediate 'ALTER TABLE Customer
MODIFY (Customer_ID nvarchar2(20) NULL)';
end if;
end;
It's best not to use dynamic SQL in order to alter tables. Do it manually and be sure to double check everything first.
Problem
I have a table named `CUSTOMER`, with few columns. One of them is `Customer_ID`. Initially `Customer_ID` column `WILL NOT` accept `NULL` values. I've made some changes from code level, so that `Customer_ID` column will accept `NULL` values by default. Now my requirement is that, I need to again make this column to accept `NULL` values. For this I've added executing the below query: ``` ALTER TABLE Customer MODIFY Customer_ID nvarchar2(20) NULL ``` I'm getting the following error: ``` ORA-01451 error, the column already allows null entries so therefore cannot be modified ``` This is because already I've made the `Customer_ID` column to accept `NULL` values. Is there a way to check if the column will accept `NULL` values before executing the above query...??