CODEWITHSUNDEEP
sqloracle-databaseplsqlexecutealter
Ricardo Valenzuela
Ricardo Valenzuela

Reputation: 33

multiple alter table in execute immediate

BEGIN
  execute immediate '
    BEGIN
        ALTER TABLE EMPRESA ADD CONSTRAINT pk_empresa_id PRIMARY KEY (EMPRESA_ID);

        ALTER TABLE CLIENTE ADD CONSTRAINT pk_cliente_id PRIMARY KEY (CLIENTE_ID);
    END';
END;

I want to do something like this in PL/SQL, but it throws me an error.

How should I do it?

Upvotes: 2

Views: 3819

Answers (2)

Pavn
Pavn

Reputation: 332

Try this, should work

SET DEFINE OFF;
SET SQLBLANKLINES ON;
SET SERVEROUTPUT ON;

DECLARE
  column_exists exception;
  pragma exception_init (column_exists, -01430);

begin
  DBMS_OUTPUT.PUT_LINE ('ALTER TABLE STUDENT TO ADD HISTORY_MKS, ENGLISH_MKS, MATH_MKS');
  execute immediate 'alter table STUDENT add(
    HISTORY_MKS NUMBER,
    ENGLIST_MKS NUMBER,
    MATH_MKS    NUMBER
    )';
  exception when column_exists then null;
end;
/

kindly change as per your needs, should work

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

Problem is that you can't run DDL directly in PLSQL. You can run them in separate execute immediates:

BEGIN
    execute immediate 'ALTER TABLE EMPRESA ADD CONSTRAINT pk_empresa_id PRIMARY KEY (EMPRESA_ID)';
    execute immediate 'ALTER TABLE CLIENTE ADD CONSTRAINT pk_cliente_id PRIMARY KEY (CLIENTE_ID)';
END;
/

Upvotes: 3

Related Questions