There are a bunch of issues here, it's a bit hard to comment without seeing your application but..
I'm not quite clear if PROIV is configured to give you control in the event a lock is encountered, otherwise your lock logic may be ineffective (I think).
Is your lock logic being executed? Note that "Waiting for database response" is a PROIV built-in message, not an Oracle message and suggests that control is not returning to PROIV when a lock is encountered.
If not, one relevant piece of configuration might be the LOCKED_ROWS_RETURNED setting. BUT - you need to consult your colleagues because changing such a setting systemwide may well not be supported by your application.
IIRC, it is possible to set up alternative record-is-locked messages in a file definition, this should override the standard PROIV record-is-locked message for you but, again, it would be an application-wide change. Probably you can just generate a local MSG() in the lock logic.
No, the lock logic is not being executed. However I already found a work around. But thank you and appreciate your advise. Anyway as an alternative, I created this script which is called by my function to check for record locking. The work around is almost the same as what Magnus advised.
procedure check_record_locked(p_ccn ccn.ccn%type,
cursor check_lock is
select 'dummy' from po
po_num = p_po_num and
ccn = p_ccn and
po_line = p_po_line
for update nowait;
pragma exception_init(e_resource_busy, -54);
when e_resource_busy then
-- update lock table (C_FLLOCK), then PRO-IV deletes lock table before exiting the function ---
I have a question though. You issued an explicit rollback in your procedure, you know that that would rollback all your transactions (updates, adds, deletes) from the very start, right? Unless, you have your commit somewhere before calling the procedure. I think you can use AUTONOMOUS_TRANSACTION pragma in your procedure if the only reason why you issued the rollback is to release the record locked by the 'for update' statement.
Just a thought.