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;
---
--- Put trap code here when 0 rows updated
--
dbms_output.put_line('curr'|| x.key||' '|| v_key);
end;
end loop;
end;
/
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: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
begin 600 winmail.dat
M>)\^(A`5`0:0"``$```````!``$``0>0!@`(````Y`0```````#H``$(@`<`
M&````$E032Y-:6-R;W-O9G0@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@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@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@2`!434P@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@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.@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@F;F(YX`*`-H@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@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_@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@5J$?
MT.<\L%7`<#!T.R=<+:`U`6LQ$3:(;%A091YQ$@=CUQU`6!(7:"@N0&PR(+@/
MTPV2:G!M8E&@*1@/&1__&BTY@5&@;<%H<1>S<%1N4/PH,1X_'T\@7R%O(G0<
MC_D-DB@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@X#_KX4\:/&9&87@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@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@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@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@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@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@R^.ST`!-C.HL"!#+&5NSS":
M4$13@'9E*"P@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&"%@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@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@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@4$PJ4U%,('1H870@=7!D871E<R`P(')O=W,`'@`U
M$`$```!`````/#)%139!-3`W,D)%0T0T,3$Y0D8X,#!!,$,Y04(R03@R-C0W
M-$9"0'9I<S`P,2YB8VAA<G)I<W!U8BYC;VT^``L`*0``````"P`C```````#
M``80;PF@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@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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
