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

chonewell Nov 12 2024

My Oracle Cloud tenant, cloud account, and secure email have no issues. Why haven't I received my password reset email for Oracle Cloud? This is very strange, and our attempts have not been able to solve the problem. May I ask who I should turn to for help?

L. Fernigrini Nov 12 2024

If your account is a paid one, open a Support ticket.

If it is a Free Tier then you will have to rely on help from the community. Most probable cause that you did not receive the password reset email is that your account has been stolen and the email has been changed.

chonewell Nov 13 2024

Thank you for your reply!
But when I chatted with the online customer service, they told me that my Oracle Cloud tenant, account, and email were all fine. So, there shouldn't be a problem of theft.
I have a free account, but who can I contact on the forum? I can only post, but no one on the forum can view my account permissions, right. I am currently trying to reset MFA, I don't know if it works.
It's quite ridiculous that I have a free account and can't enjoy any services, but how can I become a paid user if I can't log in to my account.

1 - 3

Post Details

Added on Feb 5 2025
11 comments
109 views