Hi Connor, Chris,
Could you please have a look at below scenario related to dbms_scheduler program and job setu:
-------------- Start Use Case Setup --------------
---------------------------------------------------------------------------------------------------
-- 1. Create Record and Collection
create or replace TYPE table_rec as object (create_dt DATE
,dept_id NUMBER(11,0)
,emp_id NUMBER(11,0)
)
/
show errors;
/
create or replace TYPE t_table_coll as table of table_rec
/
show errors;
/
---------------------------------------------------------------------------------------------------
-- 2. Create Procedure
create or replace procedure populate_coll_into_table(p_job_id in varchar, p_t_table_coll in t_table_coll, p_no_of_reties in number)
is
begin
dbms_output.put_line('Inside Procedure - p_job_id='||p_job_id||'- p_t_table_coll='||p_t_table_coll.count||'- p_no_of_reties='||p_no_of_reties);
for rec in p_t_table_coll.first .. p_t_table_coll.last
loop
dbms_output.put_line('Iteration #'||rec);
dbms_output.put_line('create_dt='||p_t_table_coll(rec).create_dt||'#emp_id='||p_t_table_coll(rec).emp_id||'#dept_id='||p_t_table_coll(rec).dept_id);
end loop;
exception
when others then
dbms_output.put_line('Error=>'||SQLERRM);
end populate_coll_into_table;
/
show errors;
/
---------------------------------------------------------------------------------------------------
-- 3. Create Program
set serveroutput on;
begin
dbms_scheduler.create_program(program_name => 'PRG_POPULATE_COLL_INTO_TABLE',
program_action => 'POPULATE_COLL_INTO_TABLE',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 3,
enabled => false
);
dbms_output.put_line('Program: PRG_POPULATE_COLL_INTO_TABLE created..');
dbms_scheduler.define_anydata_argument(program_name => 'PRG_POPULATE_COLL_INTO_TABLE',
argument_position => 1,
argument_name => 'P_JOB_ID',
argument_type => 'VARCHAR2',
default_value => null
);
dbms_output.put_line('Set Program: PRG_POPULATE_COLL_INTO_TABLE | Parameter 1.');
dbms_scheduler.define_anydata_argument(program_name => 'PRG_POPULATE_COLL_INTO_TABLE',
argument_position => 2,
argument_name => 'P_T_TABLE_COLL',
argument_type => 'T_TABLE_COLL',
default_value => NULL);
dbms_output.put_line('Set Program: PRG_POPULATE_COLL_INTO_TABLE | Parameter 2.');
dbms_scheduler.define_anydata_argument(program_name => 'PRG_POPULATE_COLL_INTO_TABLE',
argument_position => 3,
argument_name => 'P_NO_OF_RETIES',
argument_type => 'NUMBER',
default_value => null
);
dbms_output.put_line('Set Program: PRG_POPULATE_COLL_INTO_TABLE | Parameter 3.');
dbms_scheduler.enable('PRG_POPULATE_COLL_INTO_TABLE');
dbms_output.put_line('Enable Program: PRG_POPULATE_COLL_INTO_TABLE.');
end;
/
-------------- End Use Case Setup --------------
My requirement is to call a procedure with collection as an input (for some application processing logic).
This procedure should be running independently (i.e. in different session) other than running one.
Hence i have used dbms_scheduler (below is the setup for the same).
I have used dbms_scheduler.run_job (job_name => l_job_name, use_current_session => false); to run the procedure in differnt session,
but with this approach jobs is not getting auto dropped even if its gets succesfully completed.
Could you please help me with below concerns:
1. How can I achieve auto drop in this scenario
2. Since set_job_anydata_value is used to input collection to scheduler:
a. Where these values gets stored like in SGA, PGA ?
b. Is thre any USER_* views to find input values to each job?
c. Since my collection count could be around 500 records and expected concurrent jobs can run simultaneously (around 100)
Any suggestion whether below scheduling approach can be implemented using better approach?
------------ create and execute scheduler ------
----------
declare
l_job_name varchar2(30 char) := 'SCOTT_1234';
gv_t_table_coll t_table_coll := t_table_coll();
gv_table_rec table_rec := table_rec(NULL,NULL,NULL);
gv_no_of_reties number := 2;
begin
gv_table_rec.create_dt := trunc(sysdate-1);
gv_table_rec.emp_id := 1001;
gv_table_rec.dept_id := 100;
gv_t_table_coll.extend;
gv_t_table_coll(1) := gv_table_rec;
gv_table_rec.create_dt := trunc(sysdate-2);
gv_table_rec.emp_id := 1002;
gv_table_rec.dept_id := 100;
gv_t_table_coll.extend;
gv_t_table_coll(2) := gv_table_rec;
dbms_scheduler.create_job(l_job_name, program_name => 'PRG_POPULATE_COLL_INTO_TABLE', start_date => systimestamp, enabled => false);
dbms_scheduler.set_job_anydata_value(l_job_name, 1, sys.anydata.convertvarchar2(l_job_name));
dbms_scheduler.set_job_anydata_value(l_job_name, 2, sys.anydata.convertcollection(gv_t_table_coll));
dbms_scheduler.set_job_anydata_value(l_job_name, 3, sys.anydata.convertnumber(gv_no_of_reties));
dbms_scheduler.run_job (job_name => l_job_name, use_current_session => false);
gv_t_table_coll.delete;
exception
when others then
dbms_output.put_line('Error=>'||SQLERRM);
end;
1. Just enable the job and it'll run in a background session. Then drop:
declare
l_job_name varchar2(30 char) := 'SCOTT_1234';
gv_t_table_coll t_table_coll := t_table_coll();
gv_table_rec table_rec := table_rec(NULL,NULL,NULL);
gv_no_of_reties number := 2;
begin
gv_table_rec.create_dt := trunc(sysdate-1);
gv_table_rec.emp_id := 1001;
gv_table_rec.dept_id := 100;
gv_t_table_coll.extend;
gv_t_table_coll(1) := gv_table_rec;
gv_table_rec.create_dt := trunc(sysdate-2);
gv_table_rec.emp_id := 1002;
gv_table_rec.dept_id := 100;
gv_t_table_coll.extend;
gv_t_table_coll(2) := gv_table_rec;
dbms_scheduler.create_job(l_job_name, program_name => 'PRG_POPULATE_COLL_INTO_TABLE', start_date => systimestamp, enabled => false, auto_drop => true);
dbms_scheduler.set_job_anydata_value(l_job_name, 1, sys.anydata.convertvarchar2(l_job_name));
dbms_scheduler.set_job_anydata_value(l_job_name, 2, sys.anydata.convertcollection(gv_t_table_coll));
dbms_scheduler.set_job_anydata_value(l_job_name, 3, sys.anydata.convertnumber(gv_no_of_reties));
gv_t_table_coll.delete;
exception
when others then
dbms_output.put_line('Error=>'||SQLERRM);
end;
/
select start_date from user_scheduler_jobs
where job_name = 'SCOTT_1234';
START_DATE
11-FEB-2020 13.42.28 +00
exec dbms_scheduler.enable ( 'SCOTT_1234' );
select start_date from user_scheduler_jobs
where job_name = 'SCOTT_1234';
no rows selected
2.
a. Variables are stored in PGA
b. I'm not aware of one. They don't appear in user_scheduler_job_run_details/job_log
c. I don't know enough about what you're trying to do. What's the goal here? Why are you submitting so many concurrent jobs?