Problem: DBMSCURSOR does not respond - waits until table is unlocked
Solution or Workaround
Why does DBMSCURSOR hang when another cursor is in use?
When users are trying to update the same table, one of them must wait until the first has finished and released the table. Use the NOWAIT option on the DBMSCURSOR SELECT statement:
DBMSCURSOR CUR1 DECLARE ORACLE1 SELECT FROM TABLE1 FOR UPDATE OF XX NOWAIT
If a cursor is already opened on this table or if somebody is updating from within Oracle, using NOWAIT in the UPDATE statement prevents hanging. Instead, a message is returned:
"ORA-00054: resource busy and acquire with NOWAIT specified".
Warning: the NOWAIT option in Oracle is valid only when a SELECT FOR UPDATE command is run. NOWAIT cannot be used on 'straight' SELECT statements.
Most DBMS products (Oracle, Sybase, Informix, ...) currently support row level locking. This means that waits caused by locks on DBMS data are less likely to occur.