通过调度程序作业将参数传递给 oracle 存储过程 [英] Passing arguments to oracle stored procedure through scheduler job

问题描述

我有一个从 dbms 作业调用的存储过程.即

I have a stored procedure which is being called from a dbms job. i.e.

DBMS_SCHEDULER.RUN_JOB ('Procedure_JB', FALSE);

一个java代码存储过程，它在做一些事情后，异步启动Procedure_JB.然后这个Procedure_JB调用Procedure_PRogram，然后程序就会调用存储过程.

A java code stored procedure, which after doing some stuff, kicks off Procedure_JB asynchronously. And then this Procedure_JB calls Procedure_PRogram and then the program would call the stored procedure.

如何将参数传递给我的存储过程?

How can i pass arguments to my stored procedure?

• 我必须传递给作业的参数来自 java.

推荐答案

定义您的工作 Procedure_JB 以接受参数.然后使用 dbms_scheduler.set_job_argument_value 来定义要传递给作业将运行的程序的参数的值.一个示例(取自 https://forums.oracle.com/forums/thread.jspa?threadID=483135)

Define your job Procedure_JB to accept arguments. Then use dbms_scheduler.set_job_argument_value to define the value of the arguments you want to pass into the program your job is going to run. An example (taken from https://forums.oracle.com/forums/thread.jspa?threadID=483135)

-- create a stored procedure with two arguments
create or replace procedure myproc (arg1 in varchar2, arg2 in varchar2)
is BEGIN null; END;
/

-- create a program with two arguments and define both
begin
dbms_scheduler.create_program
(
program_name=>'myprog',
program_action=>'myproc',
program_type=>'STORED_PROCEDURE',
number_of_arguments=>2, enabled=>FALSE
) ;

dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(
program_name=>'myprog',
argument_position=>1,
argument_type=>'VARCHAR2',
DEFAULT_VALUE=>'13');

dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(
program_name=>'myprog',
argument_position=>2,
argument_type=>'VARCHAR2');

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

-- create a job pointing to a program and set both argument values
begin
dbms_scheduler.create_job('myjob',program_name=>'myprog');
dbms_scheduler.set_job_argument_value('myjob',1,'first arg');
dbms_scheduler.set_job_argument_value('myjob',2,'second arg');
dbms_scheduler.enable('myjob');
end;
/

这篇关于通过调度程序作业将参数传递给 oracle 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！