Error PLS-00103 when trying to run script to change column type-CodePudding

I'm trying to execute the following query:

BEGIN
FOR i in (SELECT CONSTRAINT_NAME, TABLE_NAME FROM user_constraints WHERE TABLE_NAME = 'DATA_MAPP') LOOP 
EXECUTE IMMEDIATE 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';
END LOOP;
END;

ALTER TABLE MAPP_SOA.DATA_MAPP ADD (DATA_MAPP_ID_RESERVA NUMBER(10)); 
UPDATE MAPP_SOA.DATA_MAPP SET DATA_MAPP_ID_RESERVA = DATA_MAPP_ID; 
UPDATE MAPP_SOA.DATA_MAPP SET DATA_MAPP_ID = NULL;
ALTER TABLE MAPP_SOA.DATA_MAPP MODIFY DATA_MAPP_ID VARCHAR(30 CHAR);
UPDATE MAPP_SOA.DATA_MAPP SET DATA_MAPP_ID = DATA_MAPP_ID_RESERVA;
ALTER TABLE MAPP_SOA.DATA_MAPP DROP COLUMN DATA_MAPP_ID_RESERVA; 

BEGIN
FOR i in (SELECT CONSTRAINT_NAME, TABLE_NAME FROM user_constraints WHERE TABLE_NAME = 'DATA_MAPP') LOOP 
EXECUTE IMMEDIATE 'alter table '||i.table_name||' enable constraint '||i.constraint_name||'';
END LOOP;
END;

To change the type of the column DATA_MAPP_ID keeping the columns order. But it only works if executed in 3 blocks. When I try to execute the entire script I get the following error:

ORA-06550: linha 7, coluna 1: PLS-00103: Encontrado o símbolo "ALTER" 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

How do I "fix" it to run the entire script?

CodePudding user response：

End the PL/SQL blocks with a slash /

  ....
END;
/

CodePudding user response：

I would firstly like to say it's bad practice. Tables are intended to remain pretty static and if there is a field you sometimes need and sometimes don't, you simply make it nullable. Or in your case, do not disable a constraint temporarily as they are not meant to be bypassed.

It didn't work :( I get the error "table or view does not exist"

To respond to the above: The error is pretty much self explanatory. The table or view does not exist. Check for typos.

Since you did not provide the DDL for the table, I will have to go off of assumption, and assume this should fix your issue (Wrapped the whole thing in an anonymous block and added an anonymous block to the ALTER TABLE part):

BEGIN
    BEGIN
      FOR i in (SELECT CONSTRAINT_NAME, TABLE_NAME FROM user_constraints WHERE TABLE_NAME = 'DATA_MAPP') LOOP 
      EXECUTE IMMEDIATE 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';
      END LOOP;
    END;
    
    BEGIN
      ALTER TABLE MAPP_SOA.DATA_MAPP ADD (DATA_MAPP_ID_RESERVA NUMBER(10)); 
      UPDATE MAPP_SOA.DATA_MAPP SET DATA_MAPP_ID_RESERVA = DATA_MAPP_ID; 
      UPDATE MAPP_SOA.DATA_MAPP SET DATA_MAPP_ID = NULL;
      ALTER TABLE MAPP_SOA.DATA_MAPP MODIFY DATA_MAPP_ID VARCHAR(30 CHAR);
      UPDATE MAPP_SOA.DATA_MAPP SET DATA_MAPP_ID = DATA_MAPP_ID_RESERVA;
      ALTER TABLE MAPP_SOA.DATA_MAPP DROP COLUMN DATA_MAPP_ID_RESERVA;
    END;
    
    BEGIN
      FOR i in (SELECT CONSTRAINT_NAME, TABLE_NAME FROM user_constraints WHERE TABLE_NAME = 'DATA_MAPP') LOOP 
      EXECUTE IMMEDIATE 'alter table '||i.table_name||' enable constraint '||i.constraint_name||'';
      END LOOP;
    END;
END;
/