0

I have a master table.It contains 1300000 records.I want to change a column type from VARCHAR(100 CHAR) to VARCHAR2(300 CHAR).

I planned a way to do this. I will create a new column with a new name then I will copy data from old column to new column then I will drop old column and rename new column with old column name.

But master table contains a trigger. It adds data to master_history table for update and delete processes. So, how will the trigger affect my way? If I do process above , Is it okey?

My trigger is : 

ALTER TRIGGER "TRG_MASTER_" ENABLE;
CREATE OR REPLACE TRIGGER "MASTER" AFTER
    UPDATE OR
    DELETE ON MASTER FOR EACH ROW 
BEGIN
    INSERT
    INTO MASTER_HISTORY
       (
       ORDER_ID,
       CUSTOMER_FIRST_NAME,
       CUSTOMER_LAST_NAME,
       CUSTOMER_EMAIL,
       CUSTOMER_ADRESS      
       )
    VALUES
       (
       :OLD.ORDER_ID,   
       :OLD.CUSTOMER_FIRST_NAME,
       :OLD.CUSTOMER_LAST_NAME,
       :OLD.CUSTOMER_EMAIL,
       :OLD.CUSTOMER_ADDRESS,           
       );
END;
GMB
  • 216,147
  • 25
  • 84
  • 135
hagi10
  • 23
  • 7
  • 1
    Internally Oracle treats both `VARCHAR` and `VARCHAR2` as same, however, VARCHAR2 is recommend by Oracle. Even if you create it as VARCHAR, you could describe the table and see Oracle would consider it as VARCHAR2. – Lalit Kumar B Apr 14 '20 at 22:12
  • 1
    Keeping in mind the other comments, but only addressing your concern about the trigger - if it comes down to it, just disable the trigger during your conversion operation, the re-enable it after. – EdStevens Apr 14 '20 at 23:35

1 Answers1

3

As far as concerns, there is no need for this complex logic. Oracle lets you extend the width of a varchar column with the following command:

ALTER TABLE master_history MODIFY mycol VARCHAR2(300);
                               --^-- modify this to your real column name

Extending the width of the column does not affect existing data.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Exactly. As long as you are making it bigger you can apply the ```ALTER TABLE```. Other type conversions may require a process similar to what OP describes. – alexherm Apr 14 '20 at 22:14
  • 1
    @alexherm: if you're making a column shorter all you need to do is to ensure that the data in the column is less than or equal to the new length in all rows, then use ALTER TABLE. [db<>fiddle here](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=9b3ab562e0b42bf7b42ba7ffabf6d1b2) – Bob Jarvis - Слава Україні Apr 14 '20 at 22:30
  • Right, but if data is longer then fail. – alexherm Apr 14 '20 at 22:41