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
Asked
Active
Viewed 520 times
1 Answers
0
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';

Jon Heller
- 34,999
- 6
- 74
- 132