Spooling out DML for scheduler job that runs a scheduler program

Oracle EE

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

     ,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.


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?


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.

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.

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.

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

1 Like


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.

       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

    ( job_name             => 'TEST_JOB'
      -- notice no argument name given here!
     ,argument_position    => 1
     ,argument_value       => '25');

Awesome! Looks like you nailed it.