8 replies to this topic

[William]

(I apoloize for the double post)

Good Day to all

I am using a Glovia 7, PRO-IV 4.6, Oracle 10g database.

I am trying to create a utility that will update an internal counter from a File. However, before I can do this I have to check if the record is being accessed and changed. For this case, I need to display an error message and then exit the utility if the same record is being accessed by another user. So I tried placing my commands in the File After Read Lock logic of my PRO-IV function. The problem is that Glovia is showing the Oracle message (Waiting for Database Response - Table: PO). Is there any work around so that it will display my own user message instead. I hope you can help. Thanks.

Edited by William, 06 April 2009 - 06:52 AM.

[Magnus]

I'm not really sure about this but here's what you can try:

In the ARNE logic (instead of the lock logic)...

#LOCK = 0
SQL
BEGIN
SELECT 1 (will return 1 if record is not locked)
INTO :#LOCK
FROM <TABLE>
WHERE <CONDITION>
FOR UPDATE NOWAIT;
END;
ENDSQL
IF #LOCK = 0 (record is locked)
<WRITE YOUR STATEMENTS HERE...>
ENDIF

[Magnus]

I'm not really sure about this but here's what you can try:

In the ARNE logic (instead of the lock logic)...

#LOCK = 0
SQL
BEGIN
SELECT 1 (will return 1 if record is not locked)
INTO :#LOCK
FROM <TABLE>
WHERE <CONDITION>
FOR UPDATE NOWAIT;
END;
ENDSQL
IF #LOCK = 0 (record is locked)
<WRITE YOUR STATEMENTS HERE...>
ENDIF

[Magnus]

I'm not really sure about this but here's what you can try:

In the ARNE logic (instead of the lock logic)...

#LOCK = 0
SQL
BEGIN
SELECT 1 (will return 1 if record is not locked)
INTO :#LOCK
FROM <TABLE>
WHERE <CONDITION>
FOR UPDATE NOWAIT;
END;
ENDSQL
IF #LOCK = 0 (record is locked)
<WRITE YOUR STATEMENTS HERE...>
ENDIF

[Richard Bassett]

William,

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.

[William]

William,

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,
p_po_num po.po_num%type,
p_po_line po.po_line%type)
is
cursor check_lock is
select 'dummy' from po
where
po_num = p_po_num and
ccn = p_ccn and
po_line = p_po_line
for update nowait;

e_resource_busy exception;
pragma exception_init(e_resource_busy, -54);
begin
open check_lock;
close check_lock;
rollback;
exception
when e_resource_busy then
-- update lock table (C_FLLOCK), then PRO-IV deletes lock table before exiting the function ---
end check_record_locked;

Edited by William, 09 April 2009 - 09:14 AM.

[William]

I'm not really sure about this but here's what you can try:

In the ARNE logic (instead of the lock logic)...

#LOCK = 0
SQL
BEGIN
SELECT 1 (will return 1 if record is not locked)
INTO :#LOCK
FROM <TABLE>
WHERE <CONDITION>
FOR UPDATE NOWAIT;
END;
ENDSQL
IF #LOCK = 0 (record is locked)
<WRITE YOUR STATEMENTS HERE...>
ENDIF

Thank you. I used your logic in solving my problem.

[Richard Bassett]

William,

Just FYI, I'm not sure you want an explicit rollback in your procedure. Generally, ending transactions in SQL invoked from PROIV (or indeed any scenario where PROIV won't 'know' the current transaction has been ended) is dangerous and is not supported by PROIV.

[Magnus]

William,

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,
p_po_num po.po_num%type,
p_po_line po.po_line%type)
is
cursor check_lock is
select 'dummy' from po
where
po_num = p_po_num and
ccn = p_ccn and
po_line = p_po_line
for update nowait;

e_resource_busy exception;
pragma exception_init(e_resource_busy, -54);
begin
open check_lock;
close check_lock;
rollback;
exception
when e_resource_busy then
-- update lock table (C_FLLOCK), then PRO-IV deletes lock table before exiting the function ---
end check_record_locked;

No problem.

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.