Asynchronous way of calling procedures

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Asynchronous way of calling procedures

Postby ponic » Wed Oct 03, 2012 1:38 pm

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
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Asynchronous way of calling procedures

Postby Tim... » Wed Oct 03, 2012 2:04 pm

Hi.

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

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17970
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Asynchronous way of calling procedures

Postby ponic » Wed Oct 03, 2012 2:08 pm

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
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Asynchronous way of calling procedures

Postby ponic » Wed Oct 03, 2012 2:21 pm

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);
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Asynchronous way of calling procedures

Postby Tim... » Wed Oct 03, 2012 4:27 pm

Hi.

You can see how to schedule jobs here:

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

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17970
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Asynchronous way of calling procedures

Postby ponic » Wed Oct 03, 2012 4:47 pm

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.');
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Asynchronous way of calling procedures

Postby Tim... » Wed Oct 03, 2012 5:03 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17970
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Asynchronous way of calling procedures

Postby ponic » Wed Oct 03, 2012 7:24 pm

Tim... wrote: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
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Asynchronous way of calling procedures

Postby Tim... » Wed Oct 03, 2012 7:36 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17970
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Asynchronous way of calling procedures

Postby ponic » Wed Oct 03, 2012 7:42 pm

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
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Asynchronous way of calling procedures

Postby Tim... » Wed Oct 03, 2012 7:44 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17970
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Asynchronous way of calling procedures

Postby ponic » Wed Oct 03, 2012 7:57 pm

Thanks Tim for your valuable suggestions.
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Asynchronous way of calling procedures

Postby Tim... » Wed Oct 03, 2012 7:58 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17970
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Asynchronous way of calling procedures

Postby ponic » Thu Oct 04, 2012 10:28 am

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
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Asynchronous way of calling procedures

Postby Tim... » Thu Oct 04, 2012 4:16 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17970
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Next

Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 3 guests