I just have a procedure which do several delete jobs

Now I do need to disable one FK-Constraint first, because of a new aplication behaviour

My procedure looks like this:

  CREATE OR REPLACE PROCEDURE <owner>."PRC_GARBAGE" (p_tage NUMBER, p_flag_tage NUMBER, p_flag_pin_tage NUMBER)
IS
   CURSOR c1
   IS
      SELECT *
        FROM tbl_gc
    ORDER BY sort_index;
   v_errm          VARCHAR2 (1000);
   v_para_cnt          NUMBER (9);
   v_ignore_stmnt    VARCHAR2 (1);
   v_job_status      NUMBER (2);
   v_cnt          NUMBER;
   v_zeit          TIMESTAMP := SYSTIMESTAMP - p_tage;
   v_flag_zeit       TIMESTAMP := SYSTIMESTAMP - p_flag_tage;
   v_flag_pin_zeit   TIMESTAMP := SYSTIMESTAMP - p_flag_pin_tage;
BEGIN
   execute immediate 'ALTER TABLE FALL disable constraint <owner>.<constraint_name> validate';
   SELECT status INTO v_job_status FROM jobstatus;
   FOR crec IN c1 LOOP
    v_ignore_stmnt := SUBSTR (crec.stmnt, 0, 1);
    --falls Job disabled und TBL-Abhaengigkeit besteht exec abklemmen
    IF crec.job_awareness = 1 AND v_job_status = 0 THEN
        v_ignore_stmnt := '#';
    END IF;
    IF (v_ignore_stmnt = '#') THEN
        INSERT INTO tbl_gc_log (tablename, executed, exectime)
         VALUES (crec.tablename, 'IGNORED >>' || v_errm || crec.stmnt, SYSTIMESTAMP);
    ELSE
        v_para_cnt := LENGTH (crec.stmnt) - LENGTH (REPLACE (crec.stmnt, ':', ''));
        BEGIN
        CASE v_para_cnt
            WHEN 2 THEN
            EXECUTE IMMEDIATE crec.stmnt USING v_flag_zeit, v_flag_pin_zeit;
            WHEN 1 THEN
            EXECUTE IMMEDIATE crec.stmnt USING v_zeit;
           WHEN 0 THEN
            EXECUTE IMMEDIATE crec.stmnt;
       END CASE;
        v_cnt := SQL%ROWCOUNT;
        INSERT INTO tbl_gc_log (tablename, executed, exectime)
             VALUES (crec.tablename, '(' || v_cnt || ' rows) ' || v_errm || crec.stmnt, SYSTIMESTAMP);
        EXCEPTION
        WHEN OTHERS THEN
            v_errm := SQLERRM;
            INSERT INTO tbl_gc_log (tablename, executed, exectime)
             VALUES (crec.tablename, v_errm || '>>' || crec.stmnt, SYSTIMESTAMP);
        END;
    END IF;
   END LOOP;
   execute immediate 'ALTER TABLE FALL enable constraint <owner>.<constraint_name> validate';
END prc_garbage;
/

When I run the statement I get the error

ORA-00933: SQL command not properly ended ORA-06512: at ".PRC_GARBAGE", line 19 ORA-06512: at line 1

Line 19 is my new line with execute immediate disable constraint - but what do i miss to end the command correctly ?

I just have a procedure which do several delete jobs

Now I do need to disable one FK-Constraint first, because of a new aplication behaviour

My procedure looks like this:

  CREATE OR REPLACE PROCEDURE <owner>."PRC_GARBAGE" (p_tage NUMBER, p_flag_tage NUMBER, p_flag_pin_tage NUMBER)
IS
   CURSOR c1
   IS
      SELECT *
        FROM tbl_gc
    ORDER BY sort_index;
   v_errm          VARCHAR2 (1000);
   v_para_cnt          NUMBER (9);
   v_ignore_stmnt    VARCHAR2 (1);
   v_job_status      NUMBER (2);
   v_cnt          NUMBER;
   v_zeit          TIMESTAMP := SYSTIMESTAMP - p_tage;
   v_flag_zeit       TIMESTAMP := SYSTIMESTAMP - p_flag_tage;
   v_flag_pin_zeit   TIMESTAMP := SYSTIMESTAMP - p_flag_pin_tage;
BEGIN
   execute immediate 'ALTER TABLE FALL disable constraint <owner>.<constraint_name> validate';
   SELECT status INTO v_job_status FROM jobstatus;
   FOR crec IN c1 LOOP
    v_ignore_stmnt := SUBSTR (crec.stmnt, 0, 1);
    --falls Job disabled und TBL-Abhaengigkeit besteht exec abklemmen
    IF crec.job_awareness = 1 AND v_job_status = 0 THEN
        v_ignore_stmnt := '#';
    END IF;
    IF (v_ignore_stmnt = '#') THEN
        INSERT INTO tbl_gc_log (tablename, executed, exectime)
         VALUES (crec.tablename, 'IGNORED >>' || v_errm || crec.stmnt, SYSTIMESTAMP);
    ELSE
        v_para_cnt := LENGTH (crec.stmnt) - LENGTH (REPLACE (crec.stmnt, ':', ''));
        BEGIN
        CASE v_para_cnt
            WHEN 2 THEN
            EXECUTE IMMEDIATE crec.stmnt USING v_flag_zeit, v_flag_pin_zeit;
            WHEN 1 THEN
            EXECUTE IMMEDIATE crec.stmnt USING v_zeit;
           WHEN 0 THEN
            EXECUTE IMMEDIATE crec.stmnt;
       END CASE;
        v_cnt := SQL%ROWCOUNT;
        INSERT INTO tbl_gc_log (tablename, executed, exectime)
             VALUES (crec.tablename, '(' || v_cnt || ' rows) ' || v_errm || crec.stmnt, SYSTIMESTAMP);
        EXCEPTION
        WHEN OTHERS THEN
            v_errm := SQLERRM;
            INSERT INTO tbl_gc_log (tablename, executed, exectime)
             VALUES (crec.tablename, v_errm || '>>' || crec.stmnt, SYSTIMESTAMP);
        END;
    END IF;
   END LOOP;
   execute immediate 'ALTER TABLE FALL enable constraint <owner>.<constraint_name> validate';
END prc_garbage;
/

When I run the statement I get the error

ORA-00933: SQL command not properly ended ORA-06512: at ".PRC_GARBAGE", line 19 ORA-06512: at line 1

Line 19 is my new line with execute immediate disable constraint - but what do i miss to end the command correctly ?

• oracle19c

• CREATE PROCEDURE <owner>."PRC_GARBAGE" is not valid on line 1 and ALTER TABLE FALL enable constraint <owner>.<constraint_name> validate is also not valid. In both cases, the <> characters are invalid and need to be removed. – MT0 Commented Feb 5 at 12:36
• i have had replaced the <owner> ... in general there is the real Schema-owner And the PRC was compiled and has had worked before with this syntax My only change was to add two lines to -- disable Constraint -- enable constraint Rest of code is unchanged by now due to the last several years – uwe A Commented Feb 5 at 13:48
• Remove validate from both alter commands, or at least put it in the right place - fiddle and docs. – Alex Poole Commented Feb 5 at 14:54

1 Answer 1

FRAME CHALLNAGE

Your first three lines of the question raise massive red flags for me. If you have to disable the foreign key constraiant before doing a delete then you are at high risk of leaving orphaned records after your delete completes.

There might be a reason to do this but I have never seen a use case where you would want to disable a fK-Constraint on the fly, triggers yes but constraints no.

I would be questioning the new applciation behaviour quite closely before even attempting this.