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 :
execute this => PAC_JOURNAL_PROCESS_NEW.PROCESS_DAILY_JOURNAL
Condition after :
– 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.