how to make Asynchronous procedure calls

451810

how to make Asynchronous procedure calls

Dear All,

The situation goes like below.

I am going to call n number of procedure inside a procedure.
Each one is independant with respect to the parent transaction.
Each one procedure should not wait for previous statements.
How to achieve this.
Please help.

Regards,
Balaji

Toine

You could create one once-off job for each procedure call. Check out the DBMS_JOB (pre-V10) or DBMS_SCHEDULE (V10) package.

451810

Hi Toine,

Thanks for the reply. I feel job can be assigned to a schedule. Schedule will execute only during some frequency. I need that procedure has to be carried out on a different thread and has to execute on its own, rather than waiting for the scheduler.
Please correct me if said anything wrong in my above statements.

Regards,
Balaji

450441

You can set them to only execute once and the time of execution as now, so it's a reasonable solution.

Toine

You can create schedule that executes every second....will that suffice?

451810

Will my database face any performance issue for that? In that case i have to look out for different method. Any way, i take this note and will work further.
I will share the results with all of you.
Thanks again.

Balaji

Boneist

You'll just have to make sure your job_queue_processes isn't set too large (or too small!) - that limits how many jobs can run at any one time, so if it's set to 5, and you have 6 jobs to be run, 5 will start, and the 6th will wait until one of the 5 running jobs has completed.

It's no different to 5 (or whatever) users running the queries at once though, for load on the db, so you just have to size everything appropriate to the work you're expecting your db to do!

Rnr-Oracle

Hi Balaji,

dbms_scheduler (and dbms_job in pre v10) is the Oracle recommended way of running procedures asynchronously. There should be no performance issues with this method.

For example if you want to run 'my_stored_proc();' asynchronously you would simply do

  dbms_scheduler.create_job(

       job_name=>'asynch_my_stored_proc',

       job_action => 'my_stored_proc();',

       job_type => 'plsql_block',

       enabled => true ) ;

And your stored procedure will immediately run in the background.

Hope this helps,
Ravi.

615129

could this be done inside a trigger?

if so, should i create the job outside of the trigger and just call dbms_scheduler.set_job_argument_value() and dbms_scheduler.enable() or can i just something similar to the above example and rely on auto_drop to be used (auto_drop defaults to enabled right?)?