ORA-54, Locking problem

From: Heinz Juergens <juergens_at_acm.org>
Date: 1997/09/04
Message-ID: <01bcb985$1cacb200$720101c8@tempo2>#1/1

Hello

I have a problem with locked records (ORA-54). I have to do an update on some different
Tables for set and unset an active-flag. The job should run on working hours and ignore
locked records.
The problem is, that the record-pointer after an unsuccesful fetch (like ORA-54) is not
moved to the next record. My question: Is there some knowledge about an undocumented feature
to move the pointer manually.
For more information look at the following code. This is dynamic-sql, but the problem is
the same with traditional cursors and with cursor-for loops. I know, that I can write a fetch-loop and inside another fetch for each row to find the
ORA-54. But this is not nice and its a big performance problem. The used tables have up
to 5000000 rows and the job set about 3000 rows on each table.

Please leave your comments here in the newsgroup or send me a mail to juergens_at_computer.org

Thanks Heinz

/******** CODE ************/
set serveroutput on
whenever sqlerror exit 8;

DECLARE   Rec_Locked exception;
  pragma exception_init(Rec_Locked, -54);

  vHostName varchar2(60) := sys_allg_pa.GetSysInfo('hostname');   vLogName varchar2(60) := 'wwssb105.sql';

  vDatTmp varchar2(60) := '&1';
  vVerArbDat date;

  vPal            pls_integer  := 0;
  vPav            pls_integer  := 0;
  vSpe            pls_integer  := 0;

  FUNCTION SetAktivFlag(pPerDatum in date,pTabelle in varchar2) return number

    /*ABOUT
    || Setzen, bzw. Aufheben des Aktiv-Flags auf "pTabelle"     || 01.09.1997 hj erstellt
    */
  is
    vCurSel integer;
    vCurUpd integer;
    vSelCount number(10);
    vUpdCount number(10) := 0;
    vRowId varchar2(50);
    vErster boolean := true;
  begin

    vCurSel := dbms_sql.open_cursor;
    dbms_sql.parse(vCurSel,'select rowidtochar(rowid) myrowid from
'||pTabelle||

                           ' where
to_date('''||to_char(pPerdatum,'YYYYMMDD')||''',''YYYYMMDD'') '||
                                      'between gueltig_ab and gueltig_bis


'||

                           '   and aktiv = 0 '||                -- zu
aktivierende
                           '   or 
to_date('''||to_char(pPerDatum,'YYYYMMDD')||''',''YYYYMMDD'') '||
                                      'not between gueltig_ab and
gueltig_bis '||
                           '   and aktiv = 1 '||
                           '   for update of aktiv

nowait',dbms_sql.native); -- zu deaktivierende

    dbms_sql.define_column(vCurSel,1,vRowId,50);     <<MeinLoop>>
    loop

      begin
        if vErster then
          vErster := false;
          vSelCount := dbms_sql.execute(vCurSel);
        end if;
        vSelCount := dbms_sql.fetch_rows(vCurSel);
        dbms_sql.column_value(vCurSel,1,vRowId);
        if vSelCount = 1 then
          vCurUpd := dbms_sql.open_cursor;
          dbms_sql.parse(vCurUpd,'update '||pTabelle||
                                 '  set aktiv = abs(sign(aktiv-1)) '||
                                 ' where rowid =
'''||vRowId||'''',dbms_sql.native);
          vSelCount := dbms_sql.execute(vCurUpd);
          dbms_sql.close_cursor(vCurUpd);
          vUpdCount := vUpdCount + vSelCount;
        else
          exit MeinLoop;
        end if;
      exception
        when rec_locked then
          wws_allg_pa.writeSyslog(vHostName, vLogName, 'W Tabelle:


'||pTabelle||', Datensatz: '||vRowId||' gelockt, aktiv nicht gesetzt!');

      end;

    end loop;
    dbms_sql.close_cursor(vCurSel);

    return(vUpdCount);
  end SetAktivFlag;

BEGIN   wws_allg_pa.writeSyslog(vHostName, vLogName, '>>> ------------- Start Programm ------------- <<<');

  BEGIN
     vVerArbDat :=
to_date(nvl(vDatTmp,to_char(sysdate,'dd.mm.yyyy')),'dd.mm.yyyy');   EXCEPTION

     WHEN others then
        wws_allg_pa.writeSyslog(vHostName, vLogName,'==> Ungültiges
Verarbeitungsdatum '||vDatTmp);
        raise_application_error(-20000,'E Ungültiges Verarbeitungsdatum

'||vDatTmp);

  END;   wws_allg_pa.writeSyslog(vHostName, vLogName, '>>> Verarbeitungsdatum
'||to_char(vVerArbDat,'dd.mm.yyyy'));

  vPal := SetAktivFlag(vVerArbDat,'WWS_PARTNER_ARTIKEL');   commit;

  vPav := SetAktivFlag(vVerArbDat,'WWS_PARTNER_ART_VARIANTEN');   commit;

  ....... to be continued for more tables

END;
/
exit 0;
/*********** END CODE ***/ Received on Thu Sep 04 1997 - 00:00:00 CDT