ORA-01442 Error when trying to automate a script

Hi,
It is Data issue. You can also use query to check exactly which row is leading to error.

actially you are getting error message by statement at line 51. Try with the following changes to supress this exception

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
begin
          execute immediate 'ALTER TABLE &schema_name'||'.'||x.table_name||' MODIFY employer_code NUMBER(5) NOT NULL';
exception When Others THEN 
  IF ABS (SqlCode) = 01442 THEN Null;
  ELSE Raise;
  END IF;
end;
        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;
/

Select allOpen in new window

OMC2000, What is "ABS"?

And I still get the ORA-01451: column to be modified to NOT NULL is already NOT NULL
ORA-06512: at line 35

what do I do here: IF ABS (SqlCode) = 01451 THEN 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);	
begin
			execute immediate 'ALTER TABLE &schema_name'||'.'||x.table_name||' MODIFY employer_code NUMBER(10) NULL';			
exception When Others THEN 
  IF ABS (SqlCode) = 01451 THEN Null;
  ELSE Raise;
  END IF;
end;
			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
begin
          execute immediate 'ALTER TABLE &schema_name'||'.'||x.table_name||' MODIFY employer_code NUMBER(5) NOT NULL';
exception When Others THEN 
  IF ABS (SqlCode) = 01442 THEN Null;
  ELSE Raise;
  END IF;
end;
        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;
/

Select allOpen in new window

It could be a problem, to change table definition in the procedure. This is not a good practice.

It could be a problem, to change table definition in the procedure. This is not a good practice.

That is NOT true! This seems to be some kind of "helper" script to perform this change in batch style (I suppose, because there are just way too many tables to be altered).
This is best practice, in fact!! Imagine, you'd do this manually for let's say 1000 tables.... You get the point, don't you?!

what do I do here: IF ABS (SqlCode) = 01451 THEN Null;

ABS is a built-in function that returns the absolute value of a certain number.

Within the exception handler, the above code snippet just means: "ignore the ORA-01451 error and go on"

I guess there is really no way around this error because I get it on the very first table that table has no rows. It should have went to the else and just changed the table to NUMBER(5)

And I still get the ORA-01451: column to be modified to NOT NULL is already NOT NULL
ORA-06512: at line 81

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
  
    begin
    
      --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;
    
    exception
      when others then
        if ABS(sqlcode) = 01451 then
          null;
        else
          raise;
        end if;
    end;
  
  end loop;
end;
/

Select allOpen in new window

This thing is tripping. Now it goes through and 98% of the NULLABLE was equal to 'N' so the script did partial. It added the employer_code_copy field and DID NOT change the NUMBER(10) to NUMBER(5) and then it did it for some.

I attached an image...

And it stopped at row 16...

And since it did not finish I am going to have to go in in manually enable the fields that it a partial (where data_precision = 10 and NULLABLE = 'Y').
Capture.PNG

One of the alter's is suppose to be 10 because it is setting the current field of size 10 to NULL to allow further updates to that column. What is the difference between 01442 and 01451?

01442 - is about already existsing not null option for a column
01451 - is about already existsing null option for a column

01442 - is about already existsing not null option for a column
01451 - is about already existsing null option for a column

Maybe you need to ignore both then?!

change

exception When Others THEN 
  IF ABS (SqlCode) = 01451 THEN Null;
  ELSE Raise;
  END IF;
end;

Select allOpen in new window

to

exception When Others THEN 
  IF ABS (SqlCode) in (01451, 01442) THEN Null;
  ELSE Raise;
  END IF;
end;

Select allOpen in new window

You guys rock!!!! Thank you so much.....