Oracle Stored Procedure Compilation error during alter table

Question

I wrote a stored procedure:

CREATE OR REPLACE PROCEDURE test1
AS
  strSchema VARCHAR2(20);

BEGIN

EXECUTE IMMEDIATE
    'ALTER TABLE TABLE_A DISABLE CONSTRAINT TABLEA_CONTRAINT ';
    'ALTER TABLE TABLE_A DISABLE CONSTRAINT TABLEB_CONTRAINT ';
    'ALTER TABLE TABLE_C DISABLE CONSTRAINT TABLEC_CONTRAINT ';
COMMIT;

END test1;

But I'm getting the following error during compilation, not sure why.

PLS-00103: Encountered the symbol "ALTER TABLE " when expecting one of the following:

( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

The symbol "(" was substituted for "ALTER TABLE " to continue.

You need to think again before (ab)using EXECUTE IMMEDIATE. You should do it in pure SQL, what is the need to do it in PL/SQL at all? On a side note, 1. You declared a variable and never use it. 2. no need to use COMMIT for DDL. — Lalit Kumar B
hi, i did it in store procedure is because i need to schedule a queue to run it(i dont run it manually hence i dont do it in pure sql), for the variable, i did use but didnt show the code here. — hades

hol hol · Accepted Answer · 2015-12-10T08:22:47

This should work. There is no need to COMMIT actually. ALTER TABLE is a DDL Statement which does not need to be committed.

CREATE OR REPLACE PROCEDURE test1
AS
  strSchema VARCHAR2(20);

BEGIN

EXECUTE IMMEDIATE
    'ALTER TABLE TABLE_A DISABLE CONSTRAINT TABLEA_CONTRAINT ';
EXECUTE IMMEDIATE
    'ALTER TABLE TABLE_A DISABLE CONSTRAINT TABLEB_CONTRAINT ';
EXECUTE IMMEDIATE
    'ALTER TABLE TABLE_C DISABLE CONSTRAINT TABLEC_CONTRAINT ';
COMMIT;

END test1;

Oracle Stored Procedure Compilation error during alter table

2 Answers