2

I have this migration script;

ALTER TABLE table_name MODIFY (column_name NULL);

How do I make it idempotent?

alpanT
  • 23
  • 3
  • Use tools like Liquibase or Flyway –  Jan 22 '20 at 14:25
  • since I have this kind of problems (https://stackoverflow.com/questions/59666601/flyway-repair-throws-flywaysqlexception-with-oracle-db) I dont prefer – alpanT Jan 22 '20 at 14:32

1 Answers1

5

Either you can use an exception handler:

DECLARE
    CANNOT_MODIFIY_TO_NULL EXCEPTION;
    PRAGMA EXCEPTION_INIT(CANNOT_MODIFIY_TO_NULL, -1451);
BEGIN

    EXECUTE IMMEDIATE 'ALTER TABLE TABLE_NAME MODIFY (column_name  NULL)';
exception
    when CANNOT_MODIFIY_TO_NULL then
        NULL;
END;

or check NULLABLE in view USER_TAB_COLUMNS:

DECLARE

    CURSOR Cols IS
    SELECT COLUMN_NAME 
    FROM USER_TAB_COLUMNS 
    WHERE TABLE_NAME = 'TABLE_NAME' 
        AND NULLABLE = 'N' 
        AND COLUMN_NAME = 'COLUMN_NAME';

BEGIN
    FOR aCol IN Cols LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE TABLE_NAME MODIFY ('||aCol.COLUMN_NAME||' NULL)';
    END LOOP;
END;
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • 1
    Just wanted to add that if it's a migration script being run in SQL*Plus, you could also use `WHENEVER SQLERROR CONTINUE` before the command as a rudimentary exception handler. – kfinity Jan 22 '20 at 15:13
  • 1
    @kfinity yes, however then it ignores any error including those you may want to see. That's the reason why I did not use `exception when OTHERS then null;` – Wernfried Domscheit Jan 22 '20 at 17:59