Run Oracle Procedure or Package in Background Process


I had a form that created in form builder. It’s run a procedure that always take some times (15-30 minutes). So users always have to wait until the procedure finish but he can’t do anything else.

With this condition, user complain how waste of time to use that form. Then i was thinking if that procedure take long time to execute while run from form applet, why not execute it in background process and ask oracle to do it directly from server. So i was thinking to use oracle job to execute the procedure. Because oracle job run in database server, not at the client process, then time to finish must be shorter. So users didn’t have to wait the procedure finish, and he can do other input, edit, print report or other action.

How can it be done ?

Condition before :

My_Button.WHEN-BUTTON-PRESSED

execute this => PAC_JOURNAL_PROCESS_NEW.PROCESS_DAILY_JOURNAL

Condition after :

My_Button.WHEN-BUTTON-PRESSED :

– INSERT_REQ_JOBS_JOURNAL      (procedure) : insert job to table TMP_REQUEST_JOBS_JOURNAL

– TMP_REQUEST_JOBS_JOURNAL  (table)          : trigger DBMS_JOB.SUBMIT (                                                                                                                                                    JOB      => X,                                                                                                                                                        WHAT  => ‘REQ_JOBS_JOURNAL_SIM’,                                                                                                                  NEXT_DATE => SYSDATE + 1 / 86400,                                                                                                                INTERVAL => NULL,                                                                                                                                            NO_PARSE => TRUE );

– REQ_JOBS_JOURNAL_SIM  (procedure)           : then finally do the                                                                                                                                                    PAC_JOURNAL_PROCESS_NEW.PROCESS_DAILY_JOURNAL

On the other words, you have to accomplish these steps

1. make 1 procedure to insert job to table A 2. make trigger B after insert on that table A 3. the trigger B have to create oracle job C (using DBMS_JOB)                                                                             4. the oracle job C execute a procedure D 5. optionally, the procedure D execute the original procedure/package E

Try it, if you have any idea about oracle background process for procedure or package, please contact me or leave some comment.

Use DBMS_SCHEDULER instead of DBMS_JOB as 10g and up recommends DBMS_SCHEDULER

2 Responses

  1. Great site…keep up the good work.

  2. Excellent site, keep up the good work

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: