Is it possible to pass a custom type of argument in Oracle dbms_scheduler?

Question

I have a collection (associative array or nested table) of NUMBER variables. I want to initiate a job which invokes a stored procedure which in turn receives this very custom type and does something for each element. Now my job takes a program where I set arguments. How do I set the argument of my custom data type (associative array or table of Number) into the scheduler program? Or any alternative ways? Thanks in advance

Answer 1

Custom types can be passed to scheduler programs using ANYDATA. But the process is complicated, so it might be easier to create a new job with a hard-coded PL/SQL block instead of re-using a program with arguments.

--Create a nested table of NUMBERs.
create or replace type number_nt is table of number;

--Create a procedure that accepts a nested table of numbers.
create or replace procedure test_procedure(p_numbers in number_nt) is
begin
    if p_numbers is not null then
        for i in 1 .. p_numbers.count loop
            dbms_output.put_line(p_numbers(i));
        end loop;
    end if;
end;
/

--Create a PROGRAM to run the stored procedure.
begin
    dbms_scheduler.create_program
    (
        program_name        => 'TEST_PROGRAM',
        program_type        => 'STORED_PROCEDURE',
        program_action      => 'TEST_PROCEDURE',
        number_of_arguments => 1
    );
end;
/

--Define the argument that will be passed into the stored procedure and enable the program.
begin
    dbms_scheduler.define_anydata_argument
    (
        program_name      => 'TEST_PROGRAM',
        argument_position => 1,
        argument_type     => 'SYS.ANYDATA',
        default_value     => null
    );

    dbms_scheduler.enable('TEST_PROGRAM');
end;
/

--Create the nested table of numbers, convert it into an ANYDATA, create a job,
--pass the ANYDATA to the job, and then enable the job.
declare
    v_numbers number_nt := number_nt(1,2,3);
    v_anydata anydata;
begin
    v_anydata := anydata.convertCollection(v_numbers);

    dbms_scheduler.create_job
    (
        job_name     => 'TEST_JOB',
        program_name => 'TEST_PROGRAM',
        enabled      => false
    );

    dbms_scheduler.set_job_anydata_value
    (
        job_name          => 'TEST_JOB',
        argument_position => 1,
        argument_value    => v_anydata
    );

    dbms_scheduler.enable('TEST_JOB');
end;
/

--Check the results.
--STATUS = "SUCCEEDED", OUTPUT = "1 2 3"
select status, output
from dba_scheduler_job_run_details where job_name = 'TEST_JOB';