Help reverting oracle DB column from NUMBER(10) to NUMBER(5) via a script

Natavia Finnie
Natavia Finnie used Ask the Experts™
on
I had to previously write a script that updated a field to NUMBER(10)  from NUMBER(5) on all tables that had a column_name = EMPLOYER_CODE in every schema. The now wants to revert that back to NUMBER(5)l.  I was able to do one table at an time using:

ALTER TABLE ep_pay_stubs ADD(employer_code_copy NUMBER(5));
ALTER TABLE ep_pay_stubs MODIFY(employer_code NUMBER(10) NULL);
UPDATE ep_pay_stubs SET employer_code_copy = employer_code;
UPDATE ep_pay_stubs SET employer_code = NULL;
ALTER TABLE ep_pay_stubs MODIFY(employer_code NUMBER(5));  
UPDATE ep_pay_stubs SET employer_code = employer_code_copy;
ALTER TABLE ep_pay_stubs MODIFY(employer_code NUMBER(5) NOT NULL);  -- only need to do this if should not be nullable
ALTER TABLE ep_pay_stubs DROP COLUMN employer_code_copy;

Open in new window


I would like to put this into a script but I also need to get pass pr disable/enable indexes and constraints.

What is the "Work smart not hard" way of doing this in oracle?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark GeerlingsDatabase Administrator

Commented:
Use a simple one-command approach like this:
alter table ep_pay_stubs modify employer_code number(5);

That would have worked for all of your tables with no problem when going from a length of 5 to 10.  Now to go back to a shorter length, this will cause an error if any records have values longer than five characters.  What should be done now if any records have values longer than five digits?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
@Mark,

You'll get an error when reducing length as long as here is any value in the column.

drop table tab1 purge;
create table tab1(col1 number(10));
insert into tab1 values(1);
commit;

alter table tab1 modify col1 NUMBER(5)
                        *
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale

Open in new window

Most Valuable Expert 2011
Top Expert 2012
Commented:
Unfortunately, the "simple" suggestion above will not work for a table with data in it.
Use a simple one-command approach like this:
alter table ep_pay_stubs modify employer_code number(5);

ORA-01440: column to be modified must be empty to decrease precision or scale

That's exactly why all the hoop jumping was done originally.
You have to save the data, purge the column, alter the column and then you can put the data back and get rid of the copy.


There is no "good" way to loop through schemas doing this.
The easiest way is probably to disable all of the constraints and then reenable all of them when done.

If you want your script to be smarter and do the least work you need to confirm constraints begin enabled and if any are not, then you need to keep track of those so you don't reenable something that shouldn't be.  You also need to check on verified status.  When you reenable do you verify or not?  For constraints on a column it's easy to restrict your list to just those.  For FK constraints you'll need to refer to indexes that point to parents.  To make things easier I would still recommend doing all of the needed disables first, then all of the re-enables after all changes.  If you don't, you'll also need to make sure to sort your table modifications in FK order to ensure you don't leave a child record orphaned.

You shouldn't need to modify indexes as they will change with the tables.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

johnsoneSenior Oracle DBA

Commented:
My question is why?

Is there some tool that has an issue with it being a NUMBER(10)?

Is it that you aren't allowed to have data that large in the field?

What about using a simple check constraint to limit the data to -99999 to 99999?  That would effectively make it a NUMBER(5), and be a simple table alter.
Natavia FinnieSenior Analyst Technical Focus Applications

Author

Commented:
I don't know if this is possible but it works to a point.  The code below may go thru 8 - 10 tables then it stops after the line:  dbms_output.put_line(x.table_name || ' this here - second '|| x.nullable);

Can anyone offer help.  

It disables the primary keys and constraints but if there is an index in the object window of PL/SQL Developer window it does not drop/disable it. If I manually drop the index and keep the primary key disabled...I can continue to run.

HELP!!!!!!!!!!!


DECLARE    
  sql_disable_pk varchar2(1024);
  sql_enable_pk varchar2(1024);
  get_rc varchar2(250);
  init_null_value varchar2(5);
    
BEGIN 
    --FOR cur_rec IN (select table_name from sys.all_tab_columns t where t.OWNER = '&schema_name' and t.COLUMN_NAME = 'EMPLOYER_CODE' and t.DATA_PRECISION = 10)
  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 != 'EP_PAY_LEAVE_BALANCE') 
    LOOP 
      
      FOR y 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_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
	  init_null_value := x.nullable;
      
        execute immediate 'ALTER TABLE &schema_name'||'.'||x.table_name||' ADD employer_code_copy NUMBER(5)';
        
        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;  
        
        -- -- --Does not execute below here-- -- --
        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
        execute immediate 'ALTER TABLE &schema_name'||'.'||x.table_name||' MODIFY employer_code NUMBER(5)';

      end if; 
      
    
    END LOOP;   
  
END;
/

Open in new window

johnsoneSenior Oracle DBA

Commented:
And the error you get when it stops would be ........

Based on the statement it is stopping on, best guess would be that you have values that don't fit into a NUMBER(5).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial