1 Reply Latest reply on May 21, 2020 8:46 AM by Mint-Innit

    Editing Scheduled Job notifications fails after database upgrade

    Mint-Innit

      Hi, after upgrading our databases from 12c to 19, creating or editing scheduled jobs with sql developer fails if you make any changes to the 'notification' tab.

       

      Error shown on attempting to apply changes:

      sqldev-error.png

      in text:

      Failed to process SQL Command

      ORA-06550: line 4, column 1

      PLS-00103: Encountered the symbol "NULL" when expecting one of the following:

      ;

      the symbol "; was inserted before "NULL" to continue.

       

      I've no idea whether this is an issue with SQL Developer, DB 19,  or with something we've done during the upgrade (I am not the dba).

      It was noticed because after upgrade the jobs all had some extra recipients and event triggers for notifications (e.g. job_succeeded) and I needed to fix them (this may well be the root cause, but still none the wiser why!)

      I can work around it at the moment by using the PL/SQL DBMS_SCHEDULAR API.

       

      Any ideas on how we can proceed to investigate further would be welcome. I have searched the support KB and on here to no avail so far.

       

      SQL Developer versions tested:

      Java(TM) Platform  1.8.0_212

      Oracle IDE         19.2.0.206.2117

      Versioning Support 19.2.0.206.2117

       

      Java(TM) Platform  1.8.0_221

      Oracle IDE         19.4.0.354.1759

      Versioning Support 19.4.0.354.1759

       

      DB version that works OK:

      Product:    Oracle Database 12c Enterprise Edition    

      Version:     12.2.0.1.0

      Status:      64bit Production

       

      (upgraded) DB Version that exhibits the issue:

      Product:     Oracle Database 19c Enterprise Edition

      Version:     19.0.0.0.0

      Version Full:     19.6.0.0.0

      Status:     Production

       

      Cheers.

        • 1. Re: Editing Scheduled Job notifications fails after database upgrade
          Mint-Innit

          Well, we still can't edit job notifications via SQLDeveloper. However in case anyone has a similar problem in the future, here's some PL/SQL I'm using to fix them:

           

          DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (job_name => '<job name>');
          
          DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION 
          (
            job_name   => '<job name>',
            recipients => '<required recipients>',
            sender     => '<sender>',
            subject    => 'Oracle Scheduler Job Notification '||sys.database_name||'@'||sys_context('USERENV','SERVER_HOST')||' - %job_owner%.%job_name%.%job_subname% %event_type%',
            events     => 'JOB_BROKEN,JOB_CHAIN_STALLED,JOB_DISABLED,JOB_FAILED,JOB_OVER_MAX_DUR,JOB_SCH_LIM_REACHED'
          );
          
          
          

          (you can omit the events parameter if you're happy with the default list in the documentation.)

           

          It's kind of a blessing in disguise I suppose because it was the final kick I needed to get around to scripting the [re] creation of scheduler jobs so they can be added to source control.

           

          Cheers.