troubleshooting Question

ORA-01442 Error when trying to automate a script

Avatar of Natavia Finnie
Natavia FinnieFlag for United States of America asked on
Oracle Database* Oracle DBA
15 Comments1 Solution134 ViewsLast Modified:
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;
/
ASKER CERTIFIED SOLUTION
OMC2000

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros