CODEWITHSUNDEEP
asynchronousparametersplsql
0xDEAD BEEF
0xDEAD BEEF

Reputation: 2104

PL/SQL Oracle DBMS_JOB/DBMS_SCHEDULER passing parameters

How do I pass parameters to DBMS_JOB/SCHEDULER async call. I have long running PL/SQL procedure, which takes few parameters, including SQL table of integers (ids). How do I create procedure which calls this PL/SQL long running procedure asynchronosly and passes parameters to it?

PROCEDURE "RUN_ARCHIVING_SEARCH"
(
    a_arch_rule_code_id_list IN INLISTNUMBERS,
    a_lang IN VARCHAR2,
    a_session_id IN NUMBER
)

Thnx, Beef

Upvotes: 1

Views: 2463

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

Depending on the maximum number of numbers you might need to pass to the procedure, you're probably better off storing the numbers in a table, then the job will query the table to get the numbers to process.

Upvotes: 1

Adam Paynter
Adam Paynter

Reputation: 46888

Instead of creating a program whose program_type is 'STORED_PROCEDURE', try creating jobs where each job's job_type is 'PLSQL_BLOCK'. This way, your job_action is actually the PL/SQL block necessary to call your stored procedure with the appropriate arguments. You could theoretically fabricate the PL/SQL block on-the-fly with the code necessary to re-create a_arch_rule_code_id.

Upvotes: 1

Related Questions