Forum Stats

  • 3,827,925 Users
  • 2,260,839 Discussions


how to make Asynchronous procedure calls

451810 Member Posts: 125
edited Aug 25, 2008 5:04PM in SQL & PL/SQL
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.



  • Toine
    Toine Member Posts: 121
    You could create one once-off job for each procedure call. Check out the DBMS_JOB (pre-V10) or DBMS_SCHEDULE (V10) package.
  • 451810
    451810 Member Posts: 125
    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.

  • 450441
    450441 Member Posts: 2,525
    You can set them to only execute once and the time of execution as now, so it's a reasonable solution.
  • Toine
    Toine Member Posts: 121
    You can create schedule that executes every second....will that suffice?
  • 451810
    451810 Member Posts: 125
    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.

  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    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
    Rnr-Oracle Member Posts: 1,269 Employee
    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
    job_action => 'my_stored_proc();',
    job_type => 'plsql_block',
    enabled => true ) ;
    And your stored procedure will immediately run in the background.

    Hope this helps,
  • 615129
    615129 Member Posts: 18
    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?)?
This discussion has been closed.