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.

It might be useful to see how you've restructured it. Your initial single block doesn't have a `commit`, so it's relying on the `alter` implicitly committing. If your modified code now doesn't hit the `alter` - maybe you've added a check to see if it exists? - then the deletes won't stick. — Alex Poole, Nov 14 '22 at 17:49
You are missing a semi-colon `;` after the penultimate `END` in the `stmt` string literal. — MT0, Nov 14 '22 at 18:06
Also, rather than catching `OTHERS` and testing `SQLCODE` you can create a user-defined exception `DECLARE fk_already_exists EXCEPTION; PRAGMA EXCEPTION_INIT(fk_already_exists, -2275);` and then catch that in the exception block. — MT0, Nov 14 '22 at 18:09
@Alex : I've re-structured the PL SQL Block as below by adding a commit, DELETE FROM usertasks WHERE userid NOT IN (SELECT userid FROM useraccounts u where u.userid=userid); DBMS_OUTPUT.PUT_LINE(''Commit start''); commit; DBMS_OUTPUT.PUT_LINE(''Committed''); BEGIN However, I still do not see any change. Also do not see any statements in DBMS_OUTPUT being printed. — explorer, Nov 14 '22 at 19:49

score 0 · Answer 1 · answered Nov 14 '22 at 21:46

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.

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

1 Answers1