Oraperl question: How to avoid re-parsing "for update" cursors

Hi oraperl users,

I have an application which selects a row for update, does some work then commits in a loop. If the row cannot be locked it is by-passed.

Works like this

foreach row
  {
    lock the row; (select .... for update nowait)     if (lock succeeded)

       {
        do some work 
        commit
       }

Problem is, because the commit closes for update cursors I have to re-open the cursor and tkprof shows me that this is causing a re-parse for each time. The repeated parsing is becoming the most expensive part of the whole job!

If it wasn't a FOR UPDATE cursor I could just issue another &ora_bind but if i do this with the FOR UPDATE cursors I get an invalid cursor error.

I'd issue a dummy UPDATE statement except I need to use NOWAIT option.

Any ideas?

Thanks in advance,

Guy

Guy Harrison                        gharriso_at_werple.apana.org.au
ORACLE  Database Consultant         gharriso_at_vitgwms1.telecom.com.au