Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE useraccounts (
  userid            NUMBER
                    GENERATED ALWAYS AS IDENTITY
                    PRIMARY KEY,
  accountcreatedate DATE
);
CREATE TABLE usertasks (
  id     NUMBER
         GENERATED ALWAYS AS IDENTITY
         PRIMARY KEY,
  userid CONSTRAINT FK_Useraccounts REFERENCES useraccounts(userid) ON DELETE CASCADE
);
BEGIN
  INSERT INTO useraccounts (accountcreatedate) VALUES (SYSDATE - 91);
  INSERT INTO usertasks (userid) SELECT userid FROM useraccounts;
END;
/
1 rows affected

DECLARE
  job_already_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(job_already_exists, -27477);

  stmt VARCHAR2(4000) := q'[
    DECLARE
      fk_already_exists EXCEPTION;
      PRAGMA EXCEPTION_INIT(fk_already_exists, -2275);
    BEGIN
      DELETE FROM useraccounts
      WHERE accountcreatedate < trunc(sysdate) - 90;
  
      DELETE FROM usertasks
      WHERE userid NOT IN ( SELECT userid
                            FROM   useraccounts
                            WHERE  userid IS NOT NULL );

      EXECUTE IMMEDIATE 'ALTER TABLE usertasks ADD CONSTRAINT FK_Useraccounts FOREIGN KEY (userid) REFERENCES useraccounts(userid) ON DELETE CASCADE';
    EXCEPTION
      WHEN fk_already_exists THEN
        DBMS_OUTPUT.PUT_LINE('Foreign Key : FK_Useraccounts on Delete Cascade in table usertasks already exists');
    END;]';
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name   => 'cleanup_job',
    job_type   => 'PLSQL_BLOCK',
    job_action => stmt,
    start_date => sysdate, 
    auto_drop  => true, 
    comments   => 'Job to cleanup user accounts whose creation date > 90 days',
    enabled    => TRUE
  );
EXCEPTION
  WHEN job_already_exists THEN
    DBMS_OUTPUT.PUT_LINE('Skipped job creation - a cleanup_job already exists for this service.');
END;
/
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 174
ORA-06512: at "SYS.DBMS_SCHEDULER", line 286
ORA-06512: at line 24

SELECT * FROM useraccounts;


USERID
ACCOUNTCREATEDATE


1
15-AUG-22


SELECT * FROM usertasks;


ID
USERID


1
1


DECLARE
  job_already_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(job_already_exists, -27477);

  stmt VARCHAR2(4000) := q'[
    DECLARE
      fk_already_exists EXCEPTION;
      PRAGMA EXCEPTION_INIT(fk_already_exists, -2275);
    BEGIN
      DELETE FROM useraccounts
      WHERE accountcreatedate < trunc(sysdate) - 90;
  
      DELETE FROM usertasks
      WHERE userid NOT IN ( SELECT userid
                            FROM   useraccounts
                            WHERE  userid IS NOT NULL );

      EXECUTE IMMEDIATE 'ALTER TABLE usertasks ADD CONSTRAINT FK_Useraccounts FOREIGN KEY (userid) REFERENCES useraccounts(userid) ON DELETE CASCADE';
    EXCEPTION
      WHEN fk_already_exists THEN
        DBMS_OUTPUT.PUT_LINE('Foreign Key : FK_Useraccounts on Delete Cascade in table usertasks already exists');
    END;]';
BEGIN
  EXECUTE IMMEDIATE stmt;
  -- The rollback simulates not committing at the end of the job.
  ROLLBACK;
END;
/
1 rows affected

dbms_output:
Foreign Key : FK_Useraccounts on Delete Cascade in table usertasks already exists

SELECT * FROM useraccounts;
SELECT * FROM usertasks;
BEGIN
  INSERT INTO useraccounts (accountcreatedate) VALUES (SYSDATE - 91);
  INSERT INTO usertasks (userid) SELECT userid FROM useraccounts;
END;
/
1 rows affected

DECLARE
  job_already_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(job_already_exists, -27477);

  stmt VARCHAR2(4000) := q'[
    DECLARE
      fk_already_exists EXCEPTION;
      PRAGMA EXCEPTION_INIT(fk_already_exists, -2275);
    BEGIN
      DELETE FROM useraccounts
      WHERE accountcreatedate < trunc(sysdate) - 90;
  
      DELETE FROM usertasks
      WHERE userid NOT IN ( SELECT userid
                            FROM   useraccounts
                            WHERE  userid IS NOT NULL );

      BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE usertasks ADD CONSTRAINT FK_Useraccounts FOREIGN KEY (userid) REFERENCES useraccounts(userid) ON DELETE CASCADE';
      EXCEPTION
        WHEN fk_already_exists THEN
          DBMS_OUTPUT.PUT_LINE('Foreign Key : FK_Useraccounts on Delete Cascade in table usertasks already exists');
      END;
    END;]';
BEGIN
  EXECUTE IMMEDIATE stmt;
  -- The rollback simulates not committing at the end of the job.
  ROLLBACK;
END;
/
1 rows affected

dbms_output:
Foreign Key : FK_Useraccounts on Delete Cascade in table usertasks already exists

SELECT * FROM useraccounts;
SELECT * FROM usertasks;
BEGIN
  INSERT INTO useraccounts (accountcreatedate) VALUES (SYSDATE - 91);
  INSERT INTO usertasks (userid) SELECT userid FROM useraccounts;
END;
/
1 rows affected

DECLARE
  job_already_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(job_already_exists, -27477);

  stmt VARCHAR2(4000) := q'[
    DECLARE
      fk_already_exists EXCEPTION;
      PRAGMA EXCEPTION_INIT(fk_already_exists, -2275);
    BEGIN
      DELETE FROM useraccounts
      WHERE accountcreatedate < trunc(sysdate) - 90;
  
      DELETE FROM usertasks
      WHERE userid NOT IN ( SELECT userid
                            FROM   useraccounts
                            WHERE  userid IS NOT NULL );
      BEGIN
        BEGIN
          BEGIN
            EXECUTE IMMEDIATE 'ALTER TABLE usertasks ADD CONSTRAINT FK_Useraccounts FOREIGN KEY (userid) REFERENCES useraccounts(userid) ON DELETE CASCADE';
          EXCEPTION
            WHEN fk_already_exists THEN
              DBMS_OUTPUT.PUT_LINE('Foreign Key : FK_Useraccounts on Delete Cascade in table usertasks already exists');
          END;
        END;
      END;
    END;]';
BEGIN
  EXECUTE IMMEDIATE stmt;
  -- The rollback simulates not committing at the end of the job.
  ROLLBACK;
END;
/
1 rows affected

dbms_output:
Foreign Key : FK_Useraccounts on Delete Cascade in table usertasks already exists

SELECT * FROM useraccounts;
SELECT * FROM usertasks;
BEGIN
  INSERT INTO useraccounts (accountcreatedate) VALUES (SYSDATE - 91);
  INSERT INTO usertasks (userid) SELECT userid FROM useraccounts;
END;
/
1 rows affected

ALTER TABLE usertasks DROP CONSTRAINT FK_Useraccounts;
DECLARE
  job_already_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(job_already_exists, -27477);

  stmt VARCHAR2(4000) := q'[
    DECLARE
      fk_already_exists EXCEPTION;
      PRAGMA EXCEPTION_INIT(fk_already_exists, -2275);
    BEGIN
      DELETE FROM useraccounts
      WHERE accountcreatedate < trunc(sysdate) - 90;
  
      DELETE FROM usertasks
      WHERE userid NOT IN ( SELECT userid
                            FROM   useraccounts
                            WHERE  userid IS NOT NULL );

      EXECUTE IMMEDIATE 'ALTER TABLE usertasks ADD CONSTRAINT FK_Useraccounts FOREIGN KEY (userid) REFERENCES useraccounts(userid) ON DELETE CASCADE';
    EXCEPTION
      WHEN fk_already_exists THEN
        DBMS_OUTPUT.PUT_LINE('Foreign Key : FK_Useraccounts on Delete Cascade in table usertasks already exists');
    END;]';
BEGIN
  EXECUTE IMMEDIATE stmt;
  -- The rollback simulates not committing at the end of the job.
  ROLLBACK;
END;
/
1 rows affected

SELECT * FROM useraccounts;
SELECT * FROM usertasks;