Re: Cursor Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 2000/01/30
Message-ID: <35b99so69t4e5hm4088q760sfl5pglt2n8@4ax.com>#1/1

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


>         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;

> END LOOP;
 
>END;
>/

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 Corporation

Received on Sun Jan 30 2000 - 00:00:00 CST