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 ?