Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help on DBMS_ALERT !!!!!!!!!!!!!

640571Jun 30 2010 — edited Jun 30 2010
By the following anonymous block, I am trying to execute 2 procedures in parallel with the help of submitting DBMS_JOB. Inside the job i am using DBMS_ALERT.SIGNAL to acknowledge the success/Failure in the parent anonymous block. When I execute this block, it stucks. Is there anything wrong with my usage approach of DBMS_ALERT?

Finidings:
1 - There is no job in user_job data dictionary view.
2 - In session wait events I am getting one "pipe get" event waiting indefinitely and the wait time is increasing gradually.

Can anybody out there help me in this regard?

DECLARE
job1 NUMBER;
job2 NUMBER;
msg1 VARCHAR2 (500);
msg2 VARCHAR2 (500);
status1 NUMBER;
status2 NUMBER;
BEGIN

DBMS_ALERT.REGISTER ('PR_CREDIT_BOOK_ENTRY_DAY_NB');
DBMS_ALERT.REGISTER ('PR_CREDIT_BOOK_ENTRY_DAY_B');

DBMS_JOB.submit
(job1,
'pkg_bgc_stage2_cr.pr_credit_book_entry_day_nb ('
|| p_dm_cycle_no
|| ',''T_CREDIT_BOOK_ENTRY_DAY'''
|| ',''CR''); DBMS_ALERT.signal (''PR_CREDIT_BOOK_ENTRY_DAY_NB'',''Msg: Non-BGI for PR_CREDIT_BOOK_ENTRY_DAY Completed''); COMMIT;'
);

DBMS_JOB.submit
(job2,
'pkg_bgc_stage2_cr.pr_credit_book_entry_day_b ('
|| p_dm_cycle_no
|| ',''T_CREDIT_BOOK_ENTRY_DAY'''
|| ',''CR''); DBMS_ALERT.signal (''PR_CREDIT_BOOK_ENTRY_DAY_B'',''Msg: BGI for PR_CREDIT_BOOK_ENTRY_DAY Completed''); COMMIT;'
);

DBMS_ALERT.waitone ('PR_CREDIT_BOOK_ENTRY_DAY_NB', msg1, status1);
DBMS_ALERT.waitone ('PR_CREDIT_BOOK_ENTRY_DAY_B', msg2, status2);

IF status1 <> 0 OR status2 <> 0
THEN
DBMS_OUTPUT.put_line ('Either status1 or status2 is non zero.');
pkg_bgc_utility.gv_success_flag := 'N';
END IF;

DBMS_ALERT.remove ('PR_CREDIT_BOOK_ENTRY_DAY_NB');
DBMS_ALERT.remove ('PR_CREDIT_BOOK_ENTRY_DAY_B');

END;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 28 2010
Added on Jun 30 2010
3 comments
1,220 views