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

sql oracle plsql toad
user8487380 picture user8487380 · Mar 28, 2018 · Viewed 8.7k times · Source

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.

Answer

Jorge Campos picture Jorge Campos · Mar 28, 2018

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);

Related questions

Variables in TOAD scripts

I have a SQL script that is being executed in TOAD. Currently, I have it laid out with just statement after statement, thusly: select such-and-such from somewhere; delete other-thing from somewhere-else; And so on. Some of the where clauses end …

Read More
Passing varchar2 parameter to plsql procedure from the Select statement

Executing the following statement in Oracle Toad exec plsql_procedure(select 'somestring' from dual); trhows the following exception: ORA-06550: line 1, column 33: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( ) - + case mod new not null Procedure takes …

Read More
Query Help - String in where clause has & character

I am running an SQL (Oracle) statement like that select * from table where table_id in ('265&310', '266&320') While running through TOAD, it consider & as some variable placeholder and it asks for its value. If …

Read More