Database Software

I'm trying to use dbms_scheduler to run a stored procedure, but I can't correctly create a program with arguments. If I try to create the program with an argument, it fails with ORA-27456 "not all arguments of program XYZ have been defined".

This code, so far as I can figure out, should work, but it doesn't:


SQL> BEGIN
2 dbms_scheduler.create_program (
3 program_name => 'SCHEDULED_DOME_STATS'
4 ,program_type => 'STORED_PROCEDURE'
5 ,program_action => 'DOME_GATHER_SCHEMA_STATS'
6 ,number_of_arguments => 1
7 ,enabled => TRUE
8 ,comments => 'Run dome stats.'
9 );
10
11 dbms_scheduler.define_program_argument (
12 program_name => 'SCHEDULED_DOME_STATS'
13 ,argument_position => 1
14 ,argument_name => 'p_threshhold_rows'
15 ,argument_type => 'NUMBER'
16 ,default_value => '1000000'
17 ,out_argument => FALSE
18 );
19 END;
20 /
BEGIN
*
ERROR at line 1:
ORA-27456: not all arguments of program "SYS.SCHEDULED_DOME_STATS" have been
defined
ORA-06512: at "SYS.DBMS_ISCHED", line 5
ORA-06512: at "SYS.DBMS_SCHEDULER", line 30
ORA-06512: at line 2

SQL>


If I remove the define_program_argument and create the program with 0 arguments, that works okay:


SQL> BEGIN
2 dbms_scheduler.create_program (
3 program_name => 'SCHEDULED_DOME_STATS'
4 ,program_type => 'STORED_PROCEDURE'
5 ,program_action => 'DOME_GATHER_SCHEMA_STATS'
6 ,number_of_arguments => 0
7 ,enabled => TRUE
8 ,comments => 'Run dome stats.'
9 );
10 END;
11 /

PL/SQL procedure successfully completed.

SQL>


I've tried numerous things, such as calling define_program_argument before calling create_program (it complains that the program doesn't exist, which is reasonable), numbering arguments from 0 instead of 1, quoting the number of arguments, etc. etc. Nothing seems to make a difference.

I'm sure there's some simple explanation which, while it would make me look like an idiot, would get this puppy working. Please don't hesitate to step up and point out any and all idiocy in what I'm flailing at here.