Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: February 10, 2020 - 2:35 pm UTC

Answered by: Chris Saxon - Last updated: February 18, 2020 - 10:49 am UTC

Category: Database Development - Version: 12c

Viewed 1000+ times

You Asked

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;

and we said...

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?

and you rated our response

  (5 ratings)

Reviews

It worked!!

February 11, 2020 - 2:43 pm UTC

Reviewer: A reader

Hi Chris,
I tried enabling job instead run_job and it worked for me.. Thanks a lot for this quick help.
Just want to get more knowledge on input collection to these jobs - will it release PGA once its SUCCEEDED, even if job log entries exists in user_scheduler_job_run_details/job_log ?

For point #c : we have concurrent existing application processing jobs (which populates certain tables)
Now we came across a new requirement where we want to load new table lets say table_x with the values of each concurrent application sessions.
DML on new table_x may take some time, hence we want existing application flow without any delay, hence looking for running separate job in other sessions.

Chris Saxon

Followup  

February 11, 2020 - 6:10 pm UTC

will it release PGA once its SUCCEEDED

Once a session ends it releases the memory it used. But why is this important to you?

February 11, 2020 - 2:49 pm UTC

Reviewer: Iudith Mentzel from Haifa, Israel

Hello All,

For point b#:
You can see the arguments for each job in USER_SCHEDULER_JOB_ARGS.

Best Regards,
Iudith Mentzel

Chris Saxon

Followup  

February 11, 2020 - 6:16 pm UTC

Ah yes, those these will be lost once the job is dropped!

store_output

February 11, 2020 - 3:18 pm UTC

Reviewer: Mikhail Velikikh from Dublin, Ireland

Hi,

>> b. Is thre any USER_* views to find input values to each job?

Provided that we are talking about getting input values of a dropped job, I would rather use DBMS_OUTPUT to log any required parameters, and then utilize %_SCHEDULER_JOB_RUN_DETAILS.OUTPUT/BINARY_OUTPUT to obtain them.

Oracle actually created an out argument table: scheduler$_job_out_args but I do not think it is used:
1. DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT.OUT_ARGUMENT accepts only FALSE.
2. there is no dependency on this table in DBA_DEPENDENCIES
3. that table is used only in one script in @?/rdbms/admin - in the one that creates it.

Regards,
Mikhail.
Chris Saxon

Followup  

February 11, 2020 - 6:16 pm UTC

I'd rather write them to a logging table!

First time Using scheduling option

February 11, 2020 - 6:13 pm UTC

Reviewer: A reader

Hi Chris,
This is the first time I am using collection input to dbms scheduler and not sure if running 100 odd concurrent sessions of collection input job may impact memory usage.
Chris Saxon

Followup  

February 11, 2020 - 6:20 pm UTC

Yes, but...

This is something that should come out in your stress testing. Unless you're loading colossal arrays or are very tight on memory I wouldn't worry about it too much.

500 records consisting of a date and two numbers is "fairly small".

Job dropped if it fails

February 14, 2020 - 2:53 pm UTC

Reviewer: A reader

Hi Chris,
I am not sure whether this query was posted earlier (I got error while submitting), hence writing it again.
Kindly ignore duplicate (if any)

Setting auto_drop => true and enabling job : I was able to achieve running job in different session.
However, jobs are getting dropped eve if they are marked as FAILED in user_job_run_details.
Could you please help, how can I restrict dropping of job if it is FAILED?

Chris Saxon

Followup  

February 18, 2020 - 10:49 am UTC

You could set auto_drop to false ;)

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database