We help IT Professionals succeed at work.
Get Started

ORA-01442 Error when trying to automate a script

132 Views
Last Modified: 2020-01-07
I get ORA-01442 when trying to automate a script.  The column is NULLABLE =N

And everything works perfect until I get that ORA-01442 Error and then my script stops immediately right there and cannot continue.

Is there a way to continue and move past it?

ORA-01442: column to be modified to NOT NULL is already NOT NULL
ORA-06512: at line 51

ALTER TABLE schema_name.table_name MODIFY employer_code NUMBER(10) NULL;

DECLARE    
  sql_disable_pk varchar2(1024);
  sql_enable_pk varchar2(1024);
  get_rc varchar2(250);
  init_null_value varchar2(5);
  allready_null EXCEPTION;
  PRAGMA EXCEPTION_INIT(allready_null, -01451);

BEGIN
FOR x in (select * from sys.all_tab_columns t where t.OWNER = '&schema_name' and data_precision = 10 and t.COLUMN_NAME = 'EMPLOYER_CODE' and data_precision is not null and data_type = 'NUMBER' and table_name not in ('EP_PAY_LEAVE_BALANCE', 'EP_PAY_STUBS')) 
    LOOP 
      
	  --select nullable into l_nullable from sys.all_tab_columns where table_name = x.table_name and column_name = 'EMPLOYER_CODE' and owner = '&schema_name';
	  
      FOR y in (SELECT constraint_name, status FROM sys.all_constraints WHERE table_name = x.table_name AND owner = '&schema_name')
      LOOP
        sql_disable_pk := 'ALTER TABLE &schema_name'||'.'||x.table_name||' DISABLE CONSTRAINT ' || y.constraint_name;
        --dbms_output.put_line(sql_disable_pk);
        execute immediate sql_disable_pk;
      END LOOP; 
      
      --select Count(*) into get_rc from &schema_name'||'.'||x.table_name||';
      execute immediate 'select count(*) from &schema_name'||'.'||x.table_name into get_rc;
      
      if get_rc > 0 then
	  dbms_output.put_line('ROWS:  '|| get_rc);
	  init_null_value := x.nullable;
      
        execute immediate 'ALTER TABLE &schema_name'||'.'||x.table_name||' ADD employer_code_copy NUMBER(5)';
		
		-----------RIGHT HERE IS THE PROBLEM----------

		 if init_null_value = 'N' then
			dbms_output.put_line(x.table_name ||'  '|| x.nullable);	
			execute immediate 'ALTER TABLE &schema_name'||'.'||x.table_name||' MODIFY employer_code NUMBER(10) NULL';			
			dbms_output.put_line(x.table_name || ' this here - second '|| x.nullable); 
               end if; 
      ---------------------------------------------------------------------------------------------------------------------------------------------------
		/* if init_null_value = 'N' then
			dbms_output.put_line(x.table_name ||'  '|| x.nullable);
			execute immediate 'ALTER TABLE &schema_name'||'.'||x.table_name||' MODIFY VALIDATED DEFAULT NULL NULL';			
			dbms_output.put_line(x.table_name || ' this here - second '|| x.nullable); 
        end if;  */		
		
        execute immediate 'UPDATE &schema_name'||'.'||x.table_name||' SET employer_code_copy = employer_code';
        execute immediate 'UPDATE &schema_name'||'.'||x.table_name||' SET employer_code = NULL';
        execute immediate 'ALTER TABLE &schema_name'||'.'||x.table_name||' MODIFY employer_code NUMBER(5)';
        execute immediate 'UPDATE &schema_name'||'.'||x.table_name||' SET employer_code = employer_code_copy';    
                
        
        if init_null_value = 'N' then
          execute immediate 'ALTER TABLE &schema_name'||'.'||x.table_name||' MODIFY employer_code NUMBER(5) NOT NULL';
        end if;
        
          execute immediate 'ALTER TABLE &schema_name'||'.'||x.table_name||' DROP COLUMN employer_code_copy';
        
        FOR d in (SELECT constraint_name, status FROM sys.all_constraints WHERE constraint_type in ('P', 'C') and table_name = x.table_name AND owner = '&schema_name')
        LOOP
          sql_enable_pk := 'ALTER TABLE &schema_name'||'.'||x.table_name||' ENABLE CONSTRAINT ' || d.constraint_name;
          --dbms_output.put_line(sql_enable_pk);
          execute immediate sql_enable_pk;
        END LOOP; 		
		
      else
        
		if init_null_value = 'N' then
			execute immediate 'ALTER TABLE &schema_name'||'.'||x.table_name||' MODIFY employer_code NUMBER(5) NOT NULL';
		else
			execute immediate 'ALTER TABLE &schema_name'||'.'||x.table_name||' MODIFY employer_code NUMBER(5) NULL';
        end if;

      end if;     
    
    END LOOP;   
END;
/

Open in new window

Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 15 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE