Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Cursor Question
A copy of this was sent to Jorge Torralba <torralba_at_europa.com> (if that email address didn't require changing) On Sun, 30 Jan 2000 13:04:40 -0800, you wrote:
>In the following cursor, if the exception is encountered, the sql
>termnates. How can I do a continue loop in order for the process to
>continue with the next value ?
>
>Thanks,
>
>JT
>
>
>DECLARE
> v_rialto_id varchar2(12);
> v_newval number;
> v_counter number;
> CURSOR C1 IS SELECT RTRIM(rialto_id) FROM ibl_rialto_doc_log FOR
>UPDATE;
>BEGIN
> v_counter := 0;
> OPEN C1;
> LOOP
> v_counter := v_counter + 1;
> FETCH C1 INTO v_rialto_id;
BEGIN> END LOOP;
> SELECT newval INTO v_newval FROM rialto_val
> WHERE RTRIM(oldval) = RTRIM(v_rialto_id);
> UPDATE ibl_rialto_doc_log
> SET rialto_id = TO_CHAR(v_newval)
> WHERE CURRENT OF C1;
exception when no_data_found then NULL; end;
> IF v_counter = 100 THEN
> COMMIT;
> v_counter := 0;
> exit;
> END IF;
You should really just simply execute a single update statement to do the whole above process.
update ibl_rialto_doclog
set rialto_id = ( select to_char(newval)
from rialto_val where rtrim(oldval) = rtrim(rialto_id) ) and exists ( select to_char(newval) from rialto_val where rtrim(oldval) = rtrim(rialto_id) )/
btw: this'll work much better if you DO NOT have to rtrim(oldval) (maybe just update all of the existing oldvals to be rtrim(oldval)) and create an index on oldval.
-- See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA Opinions are mine and do not necessarily reflect those of Oracle CorporationReceived on Sun Jan 30 2000 - 00:00:00 CST
![]() |
![]() |