Oracle: Dynamically set all NOT NULL columns in a Table to allow NULL

Question

I have a table with 75+ columns in it. Almost all of the columns have the NOT NULL constraint.

If do a giant alter table modify statement (with every column in there), I get an error saying something along the lines of "You can't set this field to NULL, because it already is NULL"

I have to do this for several tables, and so would prefer to have a dynamic solution.

Can I dynamically find all of the columns that are NOT NULL, and set them to NULL?

I've seen several similar questions like this, but can't find a solution for Oracle SQL. Modify all columns in a table to 'not null' no matter what

Answer 1

Here is a test table, with two not null columns, and one null column:

create table zzz_mark_test_me (
     cust_id varchar2(20) not null, 
     cust_name varchar2(20) null,  
     cust_phone varchar2(20) not null
);

table ZZZ_MARK_TEST_ME created.

desc zzz_mark_test_me

Name       Null     Type         
---------- -------- ------------ 
CUST_ID    NOT NULL VARCHAR2(20) 
CUST_NAME           VARCHAR2(20) 
CUST_PHONE NOT NULL VARCHAR2(20) 

Now invoke this SQL:

select 'alter table ' || table_name || 
    ' modify (' || column_name || ' null );' 
from user_tab_columns 
where table_name='ZZZ_MARK_TEST_ME' and nullable='N' 
order by column_id;

Which yields this:

alter table ZZZ_MARK_TEST_ME modify (CUST_ID null );
alter table ZZZ_MARK_TEST_ME modify (CUST_PHONE null );

Copy/paste the output into SQL*Plus etc. and invoke:

alter table ZZZ_MARK_TEST_ME modify (CUST_ID null );
table ZZZ_MARK_TEST_ME altered.

alter table ZZZ_MARK_TEST_ME modify (CUST_PHONE null );
table ZZZ_MARK_TEST_ME altered.

And now, no more NOT NULL:

desc zzz_mark_test_me
Name       Null Type         
---------- ---- ------------ 
CUST_ID         VARCHAR2(20) 
CUST_NAME       VARCHAR2(20) 
CUST_PHONE      VARCHAR2(20)