Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DBMS_SCHEDULER.CREATE_PROGRAM with Optional input parameters

978204Feb 27 2013 — edited Mar 8 2013
I have a procedure that has a number of "optional" parameters.

procedure get_files(
file_name_in in varchar2 default 'dummy_file',
layout_in in number default 1,
client_in in number default null,
data_supplier_in in number default 99999
);

This procedure can be called with any combination of the input parameters.

I can set up program(s) using the DBMS_SCHEDULER.CREATE_PROGRAM procedure using a program_type => 'PLSQL_BLOCK' like this:

begin
sys.dbms_scheduler.create_program(
program_name => 'GET_MY_FILES',
program_action => '
declare
begin
get_files( layout_in => 11111, client_in => 2222 );
end ;',
program_type => 'PLSQL_BLOCK',
number_of_arguments => 0);
end;


My question is: Can I set up programs(s) using the DBMS_SCHEDULER.CREATE_PROGRAM procedure using a program_type => 'STORED_PROCEDURE' when I have "optional" parameters?

It appears that ALL of the program input parameters must be defined and there is no way to indicate that a parameters is "optional".
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 5 2013
Added on Feb 27 2013
8 comments
1,867 views