Re: found an excellent delete statement

This is an example of optimistic locking, viz: don't lock the row on lookup , but then try to

    'delete the row I have on screen, whilst ensuring     that the corresponding row on the database has not been     changed by another user whilst I was looking at it'.

Of course, it would be better if it used bind variables.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Rino Rogomentic wrote in message
<2f8a6689.0111150257.1202bd0_at_posting.google.com>...


>Here is something amazing;

>

>We have found the following statement in a application on our

>Oracle-Box:

>

>Enjoy and better sit down before reading:

>

>delete from "xxx"."xxxx" where "CLRACI" = '2100080' and "CLETAT" = 'A'

>and

>"CLDTOU" = TO_DATE('07-NOV-1994 00:00:00', 'DD-MON-YYYY HH24:MI:SS')

>and

>"CLDTMU" = TO_DATE('30-JUN-2000 00:00:00', 'DD-MON-YYYY HH24:MI:SS')

>and

>"CLDTAN" = TO_DATE('30-JUN-2000 00:00:00', 'DD-MON-YYYY HH24:MI:SS')

>and "CLNBRM" = 3

>and "CLOPRN" = '0011385' and "CLGRPE" = '0000000' and "CLAGNT" =

>'0060310'

>and "CLCENT" = '0000001' and "CLGERA" = '0011020' and "CLETCV" IS NULL

>and "CLNOM" = 'xxxxxxx **F01**' and "CLPRNM" IS NULL

>and "CLORIG" = 'RUBRIK ""5690""' and "CLTYPE" = 'P'

>and "CLNOMC" = 'xxxxxxx R**F01**' and "CLDTNA" = TO_DATE('10-FEB-1993

>00:00:00', 'DD-MON-YYYY HH24:MI:SS')

>and "CLDTDC" = TO_DATE('30-JUN-2000 00:00:00', 'DD-MON-YYYY

>HH24:MI:SS') and "CLSRCD" = '000'

>and "CLLNGE" = 1 and "CLCDEX" IS NULL and "CLDESI" = '000' and

>"CLAD01" = 'xxxxxxxx xxxxx **F01**' and "CLAD02" = 'RUBRIK: ""5690""

>          **F01*' and "CLAD03" IS NULL

>and "CLAD04" IS NULL and "CLAD05" IS NULL and "CLAD06" IS NULL

>and "CLDOMI" = '001' and "CLREGI" = '009' and "CLNATI" = '001'

>and "CLACTI" = '990' and "CLSECT" = '001' and "CLRGMA" = '020'

>and "CLSYMP" = '000' and "CLMONE" = '001' and "CLMONP" = '001'

>and "CLTLPH" IS NULL and "CLTLEX" IS NULL

>and "CLTLFX" IS NULL and "CLSWFT" IS NULL and "CLSIC" IS NULL and

>"CLAUTR" IS NULL

>and "CLGIRO" = '000000' and "CLPROF" = '000' and "CLGEST" = 'R' and

>"CLOBJE" = 'Z'

>and "CLGRPG" = '0002100' and "CLGER2" = '0000000' and "CLGER3" =

>'0000000'

>and "CLNATU" = '000' and "CLSCTE" = '0000000' and "CLNACP" = '000'

>and "CLDOMR" = '001' and "CLSEGM" = '000' and "CLPOTN" = '999' and

>"CLCTVA" = 'Y'

>and "CLCPIN" = '000' and "CLCPRI" = '000' and "CLFILL" IS NULL and

>"CLFRES" = '01'

>and "CLTYES" IS NULL and "CLFRFI" = '04' and "CLTYFI" IS NULL and

>"CLFR03" = 'X'

>and "CLTY03" IS NULL and "CLDODS" IS NULL and "CLJU01" IS NULL and

>"CLJU02" IS NULL

>and "CLJU03" IS NULL and "CLJU04" IS NULL and "CLJU05" IS NULL and

>"CLJU06" IS NULL

>and "CLJU07" IS NULL and "CLJU08" IS NULL and "CLJU09" IS NULL and

>"CLJU10" IS NULL

>and "CLJU11" IS NULL and "CLJU12" IS NULL and "CLJU13" IS NULL and

>"CLJU14" = '0'

>and "CLJU15" = '0' and "CLDS01" = '000' and "CLDR01" = '000000' and

>"CLDS02" = '000000'

>and "CLDR02" = '000000' and "CLDS03" = '000' and "CLDR03" IS NULL and

>"CLDS04" IS NULL

>and "CLDR04" = '0' and "CLDS05" = '199610' and "CLDR05" = '000000' and

>"CLDS06" = '

>

>

>

>        "2100'

>and "CLDR06" = 'A' and "CLDS07" = '210794' and "CLDR07" = '180398' and

>"CLDS08" = '160398'

>and "CLDR08" = '2,"001' and "CLDS09" = '000000' and "CLDR09" =

>'006270' and "CLDS10" = '000000'

>and "CLDR10" = '001190' and "CLDS11" IS NULL and "CLDR11" = 'xxxxxx'

>and "CLDS12" = 'xxxxxx'

>and "CLDR12" IS NULL and "CLDS13" = 'P' and "CLDR13" = 'xxxxxx' and

>"CLDS14" = '161119'

>and "CLDR14" = '160319' and "CLDS15" = '000' and "CLDR15" = '1,"' and

>"CLFI02" = '000'

>and "CLRFAR" = 'xxxx xx' and "CLRFAN" = '   ' and "CLRMDU" IS NULL and

>"CLRMAU" IS NULL

>and "CLRMAR" IS NULL and "CLRMAN" IS NULL and "CLTENT" = '2' and

>"CLAFIL" = '00'

>and "CLDORM" = '2' and "CLDSPM" = '110' and "CLFI03" = '016' and

>"CLCRIN" = '000'

>and "CLCPDN" = '12' and "CLSQ11" = 'X' and "CLEX11" IS NULL and

>"CLSQ12" IS NULL

>and "CLEX12" IS NULL and "CLSQ13" IS NULL and "CLEX13" IS NULL and

>"CLSQ14" IS NULL

>and "CLEX14" IS NULL and "CLSQ15" IS NULL and "CLEX15" IS NULL and

>"CLSQ21" IS NULL

>and "CLEX21" IS NULL and "CLSQ22" IS NULL and "CLEX22" = '1' and

>"CLSQ23" IS NULL

>and "CLEX23" IS NULL and "CLSQ24" IS NULL and "CLEX24" = '1' and ROWID

>= 'AAABKfAARAAACXIAAA';

>

>And the best comes at the end; delete rowid ...