Spooling out DML for scheduler job that runs a scheduler program

Toad for Oracle - Beta
1

Toad 16.3.230.2084
Oracle EE 11.2.0.4

The PL/SQL block to recreate the job contains the following...

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'CRONS.CONFIRM_OLD_REJECTS_XXXXX'
     ,attribute => 'number_of_arguments'
     ,value     => 1);

When running the PL/SQL block to recreate the job it raises the error:

ORA-27488: unable to set NUMBER_OF_ARGUMENTS because PROGRAM_NAME was/were
already set
ORA-06512: at "SYS.DBMS_ISCHED", line 4467
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2851
ORA-06512: at line 40

Commenting out the call to set_attribute for number_of_arguments resolves the issue.

It appears that if the scheduler program exists when creating the job, the number_of_arguments is inherited from the program.

Regards,
Doug

2

Hi Doug,

The only way that I can reproduce this (after creating a program with an argument and a job that calls the program) is to go to the "Alter scheduler job" window for the job, and delete the row for the argument. (And similarly if I try to add/modify an argument here)

Were you in the alter scheduler job window doing something similar (changing arguments in some way), or did you just get a script from SB-Sched Jobs-Script that you were running to drop and recreate?

-John

3

This database has a number of scheduler jobs where the job_creator is someone no longer employed here. What I am doing is spooling out the DDL for these jobs and recreating them while logged in as the owner to make job_creator = owner. Then I can drop these legacy users without impacting the jobs.

The first PL/SQL block in the generated DDL is to drop the job and the second block is all of the commands to recreate the job, attributes, etc.

I am generating the DDL in Toad through the schema browser (RMB Create -> Script) and re-deployment is by running the scripts in SQL*Plus on the database server. You discovered a 2nd use case that raises the same error. :slight_smile:

Thanks for looking into this so quickly.

4

This is not an urgent issue for me. I am dealing with hundreds of jobs, so as a workaround wrote a perl script to comment out the 4 lines that set the number_of_arguments attribute in each of the generated files.

5

ok, thanks for the info. I'll try it on 11g, maybe I can reproduce it there.

If not, I'll at least log/fix it for the "alter" window.

6

I will try to make the time to test my process on a 19c database.

1 Like
9

Ah!

The trick to reproduce the bug is to not name the parameters when creating the scheduler job, referencing them only by position. Like this:

Then when you get the script from Toad it will include the call to set the number of arguments, which Oracle doesn't like. I wish I could set my number of arguments, haha.

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'TEST_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2023/03/16 22:13:12.223000 +00:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'FREQ=DAILY;INTERVAL=1'
      ,end_date        => NULL
      ,program_name    => 'TEST_PROG'
      ,comments        => NULL
    );

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'TEST_JOB'
      -- notice no argument name given here!
     ,argument_position    => 1
     ,argument_value       => '25');
END;
/
10

Awesome! Looks like you nailed it.