Scenario is :
Table name : Table1
Columns are : Column1, Columne2, Column 3, Column 4 , Column 5.....
Column 2 and Column 3 is having not null constraint. We want to remove not null constraint from these columns.
We don't know the name of the constraint.
Query which i have wriiten :
Alter table table1 modify (Column2 null);
Alter table table1 modify (Column3 null);
Query is working fine, but the standard is you should write query in such a way that you can run that query
multiple times, like suppose if you want to add column in a table then first you will check whether table is present or not
then column is present or not. if not then execute.
Query should check whether not null constraint is present or not, if it's present then only run query.
DECLARE
v_count NUMBER(2);
BEGIN
select ?????????
IF v_count=0
THEN
EXECUTE IMMEDIATE 'Alter table table1 modify (Column1 null)';
DBMS_OUTPUT.PUT_LINE('Not null removed from column1');
END IF;
select ????????
IF v_count=0
THEN
EXECUTE IMMEDIATE 'Alter table table1 modify (Column2 null)';
DBMS_OUTPUT.PUT_LINE('Not null removed from column2');
END IF;
END;
/
Plaese help me to write query in the above mentioned format.
/*
*Sample
*Addition of column MYVIEW, MYVIEW_DATE in existing table My_BOOK
*/
DECLARE
v_count NUMBER(2);
BEGIN
select count(1) into v_count from USER_TAB_COLS where TABLE_NAME='My_BOOK' AND COLUMN_NAME='MYVIEW'
IF v_count=0
THEN
EXECUTE IMMEDIATE 'Alter table MY_BOOK ADD (MY_VIEW VARCHAR2(15))';
DBMS_OUTPUT.PUT_LINE('My_Book table altered to have MYVIEW column');
END IF;
select count(1) into v_count from USER_TAB_COLS where TABLE_NAME='My_BOOK' AND COLUMN_NAME='MYVIEW_DATE'
IF v_count=0
THEN
EXECUTE IMMEDIATE 'Alter table MY_BOOK ADD (MYVIEW_DATE VARCHAR2(15))';
DBMS_OUTPUT.PUT_LINE('My_Book table altered to have MYVIEW_DATE column');
END IF;
END;
/