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;

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