oracle - 是否可以在 Oracle dbms_scheduler 中传递自定义类型的参数？

Question

我有 NUMBER 个变量的集合（关联数组或嵌套表）。我想启动一个调用存储过程的作业，该存储过程反过来接收这种非常自定义的类型并为每个元素做一些事情。现在我的工作需要一个我设置参数的程序。如何将自定义数据类型（关联数组或数字表）的参数设置到调度程序中？或者有什么替代方法？提前致谢

标签： oraclestored-procedurestypesjobsdbms-scheduler

Answer 1

自定义类型可以使用ANYDATA. 但是这个过程很复杂，因此使用硬编码的 PL/SQL 块创建新作业可能比重新使用带有参数的程序更容易。

--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';