Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> CODE to trap an update statement in PL*SQL that updates 0 rows

CODE to trap an update statement in PL*SQL that updates 0 rows

From: Thompson, Todd <tthompso_at_bcharrispub.com>
Date: Thu, 09 Aug 2001 16:12:30 -0700
Message-ID: <F001.00366715.20010809145614@fatcity.com>

Hello everyone.......... I'm trying to trap the case where an update in PL*SQL updates 0 rows. I've tried using the "returning into" clause hoping it would put null in the returning field - but it seems to retain the previous updates value. Can anyone help??  

SQL*Plus: Release 8.1.7.0.0 -    

drop table test;
create table test(col1 number);

insert into test values (1);
insert into test values (3);
insert into test values (5);
insert into test values (7);
insert into test values (9);

declare
v_key pls_integer;
cursor c is select 1 as key from dual
            union
            select 2 as key from dual
            union
            select 3 as key from dual;

begin
for x in c loop
begin
v_key:=null;
Update test set col1=x.key
where col1=x.key
returning col1 into v_key;
---

Table dropped.  

Table created.  

1 row created.  

1 row created.  

1 row created.  

1 row created.  

1 row created.  

curr1 1
curr2 1
curr3 3  

PL/SQL procedure successfully completed.  

Todd Thompson  

Architecture and Technology Team
Harris Publishing
6363 Center Drive, Norfolk VA, 23502
Email: tthompso_at_bcharrispub.com <mailto:tthompso_at_bcharrispub.com>  

begin 600 winmail.dat

M>)\^(A`5`0:0"``$```````!``$``0>0!@`(````Y`0```````#H``$(@`<`
M&````$E032Y-:6-R;W-O9G0_at_36%I;"Y.;W1E`#$(`06``P`.````T0<(``D`
M$0`V``H`!``^`0$@@`,`#@```-$'"``)`!$`-@`.``0`0@$!"8`!`"$````P
M.4(Q.#,S-S`Q.$1$-3$Q.4)&1#`P03!#.4%",D$X,@`;!P$$@`$`0````$-/ M1$4@=&\@=')A<"!A;B!U<&1A=&4@<W1A=&5M96YT("!I;B!03"I344P@=&AA
M="!U<&1A=&5S(#`@<F]W<P"+%0$-@`0``@````(``@`!`Y`&`'05```T````
M`P`)60(````>`.F`""`&``````#`````````1@`````XA0```0````$`````
M`````@%Q``$````6`````<$A'=>&8J?S=HS&$=6/5`!0VFO]&````P#>/Z]O
M```#`*N`""`&``````#`````````1@````!2A0``?AX``!X`RX`((`8`````
M`,````````!&`````%2%```!````!````#@N-0`+`-.!""`&``````#`````
M````1@`````&A0````````,`S(`((`8``````,````````!&``````&%````
M````"P"F@`@@!@``````P````````$8``````X4````````+`-6`""`&````
M``#`````````1@`````.A0````````,`J(`((`8``````,````````!&````
M`!"%`````````P#6@`@@!@``````P````````$8`````$84````````#`-B`
M""`&``````#`````````1@`````8A0```````!X`H(`((`8``````,``````
M``!&`````(.%```!````$P```#@W,3`P,S,R,2TP.3`X,C`P,0``"P!1_at_0L@
M!@``````P````````$8``````(@````````+`%.!"R`&``````#`````````
M1@`````%B`````````(!"1`!````/P\``#L/``"33@``3%I&=2"!SL\#``H` M<F-P9S$R-8(R`T-H=&UL,0,P/P$#`?<*@`*D`^,"`&-HP0K`<V5T,"`'$P*` M_Q`#`%`$5_at_A5![(1U0Y1`P'=$-<R!@`&PQ'5,P1&$-GY$N]F-!!O$781XPCO
M"??V.QJ_#C`U&]\;L1'A#&#.8P!0"PD!9#,V$6`+I9480&,!,"`0`BI<#K(%
M`9!G()`S(#PA1`!/0U194$4_at_2`!434P_at_4%5"3`!)0R`B+2\O5T0S0R.01%1$
M(J0TDBX18%1R`')T:0(@PP=`(Y!%3B(^$>,A1^<A\`JC)?PQ.2(`(K(E[N(T
M*$%%040E[0[Q)P\+*O\GU#8.\#Q-150<02`%H`(P"?!T/2(#!>`BLS4N-3`N M-`$.0#(N,3_at_P,"(F("5@!X`]1R6P15)0051/4B7M-"W!+S\IWR&Q+&\AH2]@ M(@!"3V!$62!B9PA0&E$]?B,!(#8")>`A4P`A`S!V20B0=VL+@&0U(01B70#0 M:PG`"&`W@"```'.<:'`A(CCA"X!S=#C!U3D!<P._at_9@,0;#63`I$H<W8@+:`W M.V`R,?8U&=`YR$8Z80F`.N49T!\]43:&(.T@$3//9SDV\2(`1$E6-FD``#VW M(2F,-C1`[SX/,30X(@#P1D].5#I``-`P<`<3OT#;&#`#,$//1-T`D'HP<%XR M1FP+\4=?(64X*:%3.%!!3BXP"V`$$#TX)#<Q,``S,SN0+3#(.3`X`=`P,4#? M2T-T2&4Z<&\@_$=!/T-ERG8$D'D"(&4N4@=+GC_at_F;F(YX`*`-H_at_G80,!0$L' M($DG;2!T.U&P"X!G5<!0$%7087"Y5<!H92XP31!6X'=6T(4=0"`#D75P9&$N M<`X@"X!0+S]#4$PJ4_Q13#^<%/`R0$RB-FD]G_]3#U0?7)],3TU?3F]?=%?D MQP0@$6`#8'=S+E6!49#?5<$(D#B_at_6(\_4G4`D%82]5;1(AU`=`AP`P!6$0N` MGU9`,"!A,6<05N!H;SEQ*VA!!4!W"&!L.*!P=?T%0&YI\`,@6%%6PF?(9<_O M/U(Z4$_@.*`M-6!J06FA^1$P96T$(%9!9\$+<5:SOG`=0#<@"&`$(&06=@=`
M[PI04I]=_U2\0P.1`'!1PH=KSS]#5M!L<#\_6C_76T]?/R?%-4!Q+T5R=W__
M>(]YGWJN(2D!P#9W"J)Z^/L*@"=<,"FA(_!`NWK_/J__/[]`SWX?0N^#/T4/
M1A]'+_^)WTE/2E]?KV"V"J-A/V)/_XCO=F^"/Y4O?&^7/YA/F5__FF]Q/W)/
M?B]_/X!/@5^<K_^#?X2/A9^&KX>_E)^-CXKO_XO_JY^.'X\OKN]@/Y*OD[^3 MJ1E:`2I0"D!S.G1?5:;34D__at_95<2."_0+O8W)*`DH2V5?Y:/LH^;?_^[_[T/
MOA]]CZ#?H>^B_Z0/_[]?IB^G/ZA/PG^J;\>?K(__K9^NK\X_L,^QW]&/D;^T
MO_^US]3/NP_&W[__P0_;W]SO_]W_WP^>?Y^/PL_#W\3OQ?__X4_('\DORC_+
M3\Q?V3_2+__/C]"?\#_2O]//\X_5[];__]@/[X_:+]L_^\_@+_W?_N____\!
M#^*OX[_MW^7?YN_G__\#3^H?ZR_L/^U/[E_[/_0O[_&,]6_S!?:M9#A_at_5J$? MT.<\L%7`<#!T.R=<+:`U`6LQ$3:(;%A091YQ$@=CUQU`6!(7:"@N0&PR(+@/ MTPV2:G!M8E&@*1@/&1__&BTY_at_5&@;<%H<1>S<%1N4/PH,1X_'T\@7R%O(G0< MC_D-DB_at_S(O\D#R4?)B\B=;XU*0\J'RLO+#\B=3<MSS\NWR_O,/0GGPV2,5\H M.><RCS.?&AYD9?E!;J`XOX,YSQH\=E]K97D$CT,%GU3+<&QS7S=A93YG4:`X MKSTO&BYK8'-OO_DA6$`G?PUTN:"Y<&-MP/L<8/E@(#]!$_!DL%6P1N_Y#9)D M=7!P0R]$/QH\/W__0(]?'TYO3W]0CU&?4J]3O_]4SU7?5N]7_UD/6A];+UP_ M_UU/7E]?;V!_88]BGV.O9+]_9<]FWV?O:/]!C$F/#9)U_&YI="!+'TPO33]K
M#VP?_W'?<N]S_W4/=A]W+W@_>4__>E][;WQ_?8]^GW^O@+^!S_^"WX/OA/^&
M#X<?B"^)/XI/_XM?C&^-?VT?1T](4_T`2,__2=]*[W!/<5^/+Y`_CF^9G_^:
MKYN_G,^=WY[OG_^A#Z(?_Z,OI#^E3Z9?IV^H?ZF/JI__JZ^LOZW/KM^O[[#_
ML@^S'_^T+Y$_E.]O#Y;_F`^UW[;O_[4?O;^^S[_?P._!_\,/Q!__Q2_&/\=/ MR%_);\I_RX_,G__-K\Z_S\_0W]'OTO_4#]4?O]8OUS_83[?ON/]'^3.3Z]^6 M`D,?NY\:+1X`9QH_at_X#_KX4\:/&9&87_at_UD4:!W4_Y#9)L;Q<PXX_DG^)?XV^G MZ;\^+S\R.CT=T&S@'^/M#T4N57!D&T':7]MO'T&;)M-(,"<`'#(]>"['/T[P MK_&_('=H'A`;8'_G/PV2]K_WS_C?V>4;('3S1C"Z4&YG]J,Q1#\C[\WZ-?V1 M+Q.R"R\''P_at_O"3__"D\"[PQO#7\.CP^?$*\1O\'S:SQ34$%.&P`[X"!S<STX M-P=`,#,`,S(Q+3`Y,#@Y%D`P,0O/#O\"`#0X!P=P`G+F<&%C93U!C')I)S`* M%'-I>A4_at_RC(2*V8H869S$>#^@/T2]RT84`%/`E\(CPKFD]#_!X`04AG_`\\$ MWP7O!O\<[_\)'PHO"S\,3PU?$J\/?Q"/_Q&?*&\;CQR?+1\4+Q4U%?__%P\8 M$RE/*E,D(RJO*[\F:A$84"!0=4B`=')A]G#VH3NP(/IC^E'F\/^!W&]W1L^Y MY?+C9"V/+I__+Z\8_SZ_'=\>[Q__(0]!O_\C+R0_)4\F7R=O++\U'S9__S>/ M33\]CSZ?4>\UCT]_4(__49]2KU._65\Q'S(O,S\T3_]5CU:?5Z\XC5F?6J]; MO_T?`_XO9]5D8FUS7V]5.:!P.:`N:\%?Y8(HA"=C_]!R)WQ\YK#S_&)L\"<@ M;.([CTD"[Q/>*>_/:4_EG3K_at_9&^O<+_Y<<]D(.B"<N]S_W4/<M_[=W_ECB]Y
MSWK?O+]`/T%/_TMO0V]$?T6/@4]_CTB_2<__9&]+[XJO7%]=;UY_7X^-G_^`
MKX;?\U_T;XL?@Q^$+X4__X9/E+^(;XE_C*^+GY_OC;^/CL^/WY#OHM1486+>
ML-/?T-^@<'`]("Z27Y-O_X'_F&^9?YJ/FY^BOYV_GL__H?^@[[,_E/^6#Y<?
MJX^LG_^MKZZ_K\^PW['OLO^Y#[4?_[XOH]^D[Z7_D:]]/WY/ISS^8_^@/0*H
MCZF?JJ^YS[K?_[OOO/_(G[\?P"_#7\)/UY__U+^W;[A_S^_0_](/TQ_4+__5
M/]9/UU_=;]E_XH_%/\9/_\=?R&_)?\J/[0<`<#LQS'__S8_AS]V_WL_?W^#O
M\S_C#__D'^4OYC_G3^A?VU_<;_SO__3O]?_W#_@?_S_Z/_M/_%___6_^?^DO
MZC_K3^Q?#5_N?__OC_"?\:_ROPKO`N\#_P4/_Q=?$;\(/PE/#'\+;R"__W__
M`(\!GQD/&A\;+QP_(U\>7_\?;R*?(8\NCRNO#E\/;Q!__S%_$I\3KQ2_%<\6
MWQ?O)P__*!\I+RH_-=\L7RUO+G\OC_\PGT'_)*\EOSTO/C\_3T!?_T%O0G]#
MCT2?2J]&OT_/,G__,X\TGS6O-K\WSSC?.>\Z__]//TLO3#]-3TY?3V]0?U&/ M_U*?8/]4OV8?2,])WV%/8E__8V]D?V6/9I]GKVB_:<]JW_]K[U:?5Z]8OUG/ M6M];[US__UX/7Q]@+WA?<%]Q;W)_A,__=)]UKW:_=\]XWWGO;.]M__^.?X9_ MAX^(GXFOD,^+SXS?_Y`/CO^;_WJ_>\]\WWWOGN308W5R<H)0,7]?@&][RTRC MPC*D'Z4OIC^CT3/\(#.#/X1/A5^4?Y6/EI__EZ^>SYG/FM^>#YS_M;^1#_^2 M'Y,OK@^O'[`OL3^R3[-?_[1OM7^[C[>?P*^?[Z#_H@\!HQE03"]344P_at_5G"" M<,BP9*/09<DP=0)CR+!S<V9U;&P.><$,RH'"$V-O;7#\;&6"\K_?N\^\W[WO
MOO__SZ^K':=PK";!'\(OPS_$3__%7\9OQW_(C\F??E^K+ZP__^%?N8^ZG]!O
MT7_2C].?XY__P/_87]EOVG_;C]R?W:_>O('.T&QO<CTC,/5C\]^/X)5C9J=P
MX/_-G^WA_^N/YW_F-NE/[F_QG_"/_I__Y)'V7_=O\L_SW_8_`Y\$K0_UW^"A
M`T_D^SQ35%(5XS!'_GMBHQE4;V2*9`Y0:,[A<V]N^=__!Z_BWP^<#/\2[0_/
M$-\1[_\2_PJ_"#_MLPE/"E\:/P4__P9/'9\6CQ>?(:\BOR//_7C_UL(,?Q@? M^C_[3^COZ?_K#_\FO^TO_5_^;RLO`(\8;P*O_S`_'K_?+^`_.#\D_R]//1__ MY8_FGS[_*U\L;RU_+H]#3_\PKS&_,L\SWS3O-?\W#SR?_SDO.C\[3U"O/6\^ M?TC/4A_O'X\@GUCO",LQ'%VH_at_5P8D5-P8VAI@O!C=,QR,_B/2H)A;@Z18*!H M;LWU`6?-8&*@86U6/U=/_UA?8_]E#V8?77]>CUP/9L__9]]"STSO1.]%_T</ M;C]L?_]*/TM/3G]-;WC?3X]0GVDOOUH/6Q]]KVF_:L]?74AW`*53@'/+D'5B MJ:!S8'#\;F=LSVW?=4^&[X?_B0__&O^#;VO/B6^*?WMO<`]Q'_]R+W,_=$^/ M;W9O=W]XCWF?_WJO>[]\SYC??N]__X$/H:\G_at_R^.ST`!-C.HL"!#+&5NSS": M4$13@'9E*"P_at_3O4@9O4`:R`<5D&IP&$?FD(R,S7\,#*0#Y$?F(^LKZV_DH__ MG*^4KY6_EL^PCZ[/F?^;#_^>/YTONR^?3Z!?I1^B?_3O!Z:_OPE?>T5M86EL M4CI`+SQ!C81HS(!F!#TBQD)T;SIT=%D.Q$!B8&"%_at_W"%\"Z%SM$BP^QI96QD MN&(&9LN`AE!S='M(60!015),24Y+(#/(?\F.?7U4X<N`<G/CO*"_,3%<=5.P MO^C-C]_*!;</L;_`+R_at_50==-K^__T__67]=ONZ^SK[2_M<^VW__8K][OW_^X M7[EONG^[C[R?__(O0)]!K^=/W(_=G^%OXG\%Z9XUP7$O0D]$60G6'3(W\`%( M5$U,%]8=J.#DI'WV(``#`"8```````,`-@``````"P`"``$````#`/T_Y`0`
M`!X`Z(`((`8``````,````````!&`````#>%```!`````0`````````>`'``
M`0```$````!#3T1%('1O('1R87`@86X@=7!D871E('-T871E;65N="`@:6X@
M4$PJ4U%,('1H870@=7!D871E<R`P(')O=W,`'@#G@`@@!@``````P```````
M`$8`````-H4```$````!`````````$``.0#POM?1'2'!`0,`\3\)!```'@`Q
M0`$````)````5%1(3TU04T\``````P`:0``````>`#!``0````D```!45$A/ M35!33P`````#`!E```````,`@!#_____"P#R$`$````"`4<``0```#0```!C M/553.V$](#MP/4)#2$%24DE34%5".VP]5DE3,#`Q+3`Q,#@P.3(Q-30Q,%HM M-S0P-S,``@'Y/P$```!+`````````-RG0,C`0A`:M+D(`"LOX8(!```````` M`"]//4)#2$%24DE34%5"+T]5/458+T-./5)%0TE0245.5%,O0TX]5%1(3TU0 M4T\``!X`^#\!````#P```%1H;VUP<V]N+"!4;V1D```>`#A``0````D```!4 M5$A/35!33P`````"`?L_`0```$L`````````W*=`R,!"$!JTN0@`*R_A@@$` M````````+T\]0D-(05)225-054(O3U4]15_at_O0TX]4D5#25!)14Y44R]#3CU4
M5$A/35!33P``'@#Z/P$````/````5&AO;7!S;VXL(%1O9&0``!X`.4`!````
M"0```%142$]-4%-/`````$``!S"(IM#1'2'!`4``"#!,(Y?4'2'!`1X`/0`!
M`````0`````````>`!T.`0```$````!#3T1%('1O('1R87`@86X@=7!D871E
M('-T871E;65N="`@:6X_at_4$PJ4U%,('1H870@=7!D871E<R`P(')O=W,`'@`U M$`$```!`````/#)%139!-3`W,D)%0T0T,3$Y0D8X,#!!,$,Y04(R03_at_R-C0W M-$9"0'9I<S`P,2YB8VAA<G)I<W!U8BYC;VT^``L`*0``````"P`C```````# M``80;PF_at_G0,`!Q"Q`P```P`0$``````#`!$0`0```!X`"!`!````90```$A% M3$Q/159%4EE/3D5)351264E.1U1/5%)!4%1(14-!4T572$5214%.55!$051% M24Y03"I344Q54$1!5$53,%)/5U-)5D544DE%1%5324Y'5$A%(E)%5%523DE. M1TE.5$\B0TP``````@%_``$```!`````/#)%139!-3`W,D)%0T0T,3$Y0D8X M,#!!,$,Y04(R03_at_R-C0W-$9"0'9I<S`P,2YB8VAA<G)I<W!U8BYC;VT^`/PR `
end
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Thompson, Todd
  INET: tthompso_at_bcharrispub.com
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists

--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Aug 09 2001 - 18:12:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US