Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Just need some advice to disable/enable FK constraint during a procedure

Uwe AppmeierFeb 5 2025 — edited Feb 6 2025

Hi there

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 "<owner>.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 ?

Comments
Post Details
Added on Feb 5 2025
11 comments
103 views