Asynchronous way of calling procedures

[ponic]

Tim

I would like to execute the following procedures at a interval of 10 minutes. In order to do this I put a sys.DBMS_LOCK.sleep (<>);. This code is inside a procedure

Code: Select all

putData(empNo,EmpName);
sys.DBMS_LOCK.sleep (<>);
putData(empNo,EmpName);

But the problem I faced is my calling application page stays remain till the second procedure finishes. I would like to the close the page as soon as the user submits the page.
What is the best way to resolve this problem? Any asynchronous method of doing this?

Thanks

[Tim...]

Hi.

The best way to accomplish this is to use the scheduler. Start 3 jobs, 10 minutes apart.

Cheers

Tim...

[ponic]

Tim,

Could it be possible to provide an example as I tried with I ran into problems

Code: Select all

DBMS_JOB.SUBMIT(ln_dummy, 'begin putData('||empNo,EmpName||'); end;');

because of

Code: Select all

compilation error wrong number of arguments to call Submit.

Thanks

[ponic]

Tim

These procedures are being executed from a procedure and this procedure is being invoked from my application.

Code: Select all

putData(empNo,EmpName);
putData(empNo,EmpName);

[Tim...]

Hi.

You can see how to schedule jobs here:

http://www.oracle-base.com/articles/10g ... php#simple

Cheers

Tim...

[ponic]

Tim

I have two questions,

If I am calling this in a procedure and procedure in being invoked from Java. So will the below scheduler jobs becomes asynchronous? Does this execute the same way normal procedure executes and upon finishing the jobs call return back to user and user will have to wait till the job finishes?

My second question is how to put the below in a procedure and pass parameters to procedure my_job_procedure inside BEGIN my_job_procedure; END;' ?

Thanks

Code: Select all

DBMS_SCHEDULER.create_job (
    job_name        => 'test_full_job_definition',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN my_job_procedure; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');

[Tim...]

Hi.

When you create a job, you just create the definition and it returns to your session immediately, then the job coordinator runs your job at the scheduled time. So your session does not hang. That's the whole point of the scheduler.

Regarding the calling your procedure with parameter, you have two choices. You either define the job to call a procedure that accepts parameters, or you concatenate your call together in a string and call that.

Code: Select all

'BEGIN my_job_procedure(''' || variable1 || ''',''' || variable2 || '''); END;'

Cheers

Tim...

[ponic]

Hi.

When you create a job, you just create the definition and it returns to your session immediately, then the job coordinator runs your job at the scheduled time. So your session does not hang. That's the whole point of the scheduler.
Cheers

Tim...

Sounds good and thanks for the explanation.

Another question.

In production the time difference between two procedures will be 3 minutes. In that case as the procedure will be called from application there can be so many schedule jobs running. So first argument which is job_name might give error that job name already exists and second do I need to increase any job queue for this? Creating many schedule jobs will be a problem for database even though it is only for a short period of time? Will DBA object to this due high number of job process?

Thanks

[Tim...]

Hi.

It's up to you if you want to define a job chain in advance, or just create jobs on the fly. You can always define unique job names on the fly using the built in GENERATE_JOB_NAME functionality.

http://docs.oracle.com/cd/E11882_01/app ... m#i1011295

Cheers

Tim...

[ponic]

Tim,

What will be the impact of having many number of job queue processes in database? How much would be ideal mar range for this?

Thanks

[Tim...]

Hi.

Many job queue processes? You are defining a number of jobs, but they are not all running at the same time. If you define 1000 jobs, but they are scheduled to run at different times, you do not have 1000 sessions waiting or anything like that. The whole point is they are scheduled to run in the future. They are not sessions waiting to run...

Cheers

Tim...

[ponic]

Thanks Tim for your valuable suggestions.

[Tim...]

[ponic]

Tim,

Any particular reason why I cannot find schedule jobs in object dba_scheduler_programs, when I query I do not see any schedule programs or jobs?

Thanks

[Tim...]

Hi.

You should see them if you have scheduled any. Is it possible you scheduled them with the old scheduler using DBMS_JOB? If so, they will be displayed in the DBA_JOBS view.

Cheers

Tim...