CODEWITHSUNDEEP
sqloracle-databaseplsqltoad
user8487380
user8487380

Reputation:

How to set all column from null to not null in table in oracle

I have 1 table having 40 columns. Out of 40 columns only 5 columns are NOT NULL and rest of the columns are set as NULL. How can i set all NULL column to NOT NULL in one time or in TOAD. Is there any possibility to do this except manually set as NOT NULL.

Upvotes: 17

Views: 46155

Answers (3)

AConsumer
AConsumer

Reputation: 2791

Alter table will work .

alter table [nameOfYourTable] modify [nameOfColumn] [dataType] not null

Upvotes: 7

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

You could loop through USER_TAB_COLUMNS ( ALL_TAB_COLUMNS with owner = 'schema') and ALTER using EXECUTE IMMEDIATE.

You could first update the table containing existing NULLs with appropriate value before running this.

SET SERVEROUTPUT ON

BEGIN
    FOR q IN (
            SELECT 'ALTER TABLE ' || table_name || ' MODIFY ' || column_name || ' NOT  NULL' AS query
            FROM user_tab_columns
            WHERE table_name = 'YOURTABLE'
                AND NULLABLE = 'Y'
            ) 
   LOOP
     DBMS_OUTPUT.PUT_LINE(q.query);
     EXECUTE IMMEDIATE q.query;
   END LOOP;
END;

Upvotes: 3

Jorge Campos
Jorge Campos

Reputation: 23381

You can use the Alter Table command to do so. This way:

ALTER TABLE table_name
  MODIFY (column_1 column_type NOT NULL,
          column_2 column_type NOT NULL,
          ...
          column_n column_type);

This will accomplish the changes in all columns at once. Also if your table already has data with null values on those columns, you will have to define default values as well. Here is a sample:

ALTER TABLE table_name
  MODIFY (column_1 varchar2(100) DEFAULT 'some default' NOT NULL,
          column_2 varchar2(75) DEFAULT 'Some Value' NOT NULL);

Upvotes: 20

Related Questions