Nested PL/SQL Block assigned to variable doesn't execute

Question

The DBMS_SCHEDULER job "cleanup_job" is as below.

DECLARE
  stmt VARCHAR2(4000) := '
    BEGIN
      DELETE FROM useraccounts WHERE accountcreatedate < trunc(sysdate) - 90;
      DELETE FROM usertasks WHERE userid NOT IN (SELECT userid FROM useraccounts u where u.userid=userid);
                    
      BEGIN
        EXECUTE IMMEDIATE ''ALTER TABLE usertasks ADD CONSTRAINT FK_Useraccounts FOREIGN KEY (userid) REFERENCES useraccounts(userid) ON DELETE CASCADE'';
      EXCEPTION WHEN OTHERS THEN
        IF SQLCODE = -02275 THEN
          /*ORA-02275: such a referential constraint already exist*/
          DBMS_OUTPUT.PUT_LINE(''Foreign Key : FK_Useraccounts on Delete Cascade in table usertasks already exists'');
        ELSE
          RAISE;
        END IF;
      END
    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 OTHERS THEN
    IF SQLCODE = -27477 THEN
      DBMS_OUTPUT.PUT_LINE('Skipped job creation - a cleanup_job already exists for this service.');
    ELSE
      RAISE;
    END IF;
END;

The job perfectly works if I give all the SQL statements in a single PL SQL Block like below,

stmt varchar(4000) ='
  BEGIN
    ---All SQL Statements here----
  END;'

But if I have nested PL SQL Block, there are no errors when I run the job but the statements doesn't get executed (I don't see any changes in the tables associated).

stmt varchar(4000) ='
   BEGIN
     ---SQL Statements----
      BEGIN
         ----SQL Statements-----
      END;
    END;'

Is it not allowed to have nested PL/SQL blocks assigned to a variable ?

I'm quite new with this & any help would be much appreciated.

Answer 1

Is it not allowed to have nested PL/SQL blocks assigned to a variable?

It is allowed; you can nest blocks inside blocks without issue.

And block inside blocks inside blocks, etc. works.

See fiddle

I don't see any changes in the tables associated

The queries work in the fiddle above regardless of how many blocks they are nested inside.

Note: although the fiddle uses slightly different queries they are effectively identical as SELECT userid FROM useraccounts u WHERE u.userid = userid will take userid from the local scope rather than any outer scope so it is the same as SELECT userid FROM useraccounts WHERE userid IS NOT NULL.