Re: Help on simple script

In article <RoLX7.17344$4x4.1948134_at_news2-win.server.ntlworld.com>, "Richard says...
>
>
>Hi - I know it's Christmas, but could some one please help me with the
>simple script below. it seemed to work ok on the test db, but on live I got
>a no data found error...any tips would be gratefully received! The ITEMS
>table contains about one million items, the cursor retrieves about 1078.
> thank!
>

that just means there are some

(dmc,nc,iin)

values in TEMP_ITEMS that are not in ITEMS

you can see these values with:

select dmc, nc,iin
  from temp_items
 where record_id in (select record_id from validation_exceptions

                     where exception_id = 4)
   and NOT EXISTS ( SELECT NULL
                      from ITEMS
                     where dmc = temp_items.dmc
                       and nc = temp_items.nc
                       and iin = temp_items.iin );

These are the ones giving you the NO_DATA_FOUND exception on the select into.

I can say that this procedural code is more easily implemented as:

UPDATE TEMP_ITEMS
   SET ( SMBI, SMBS ) = ( SELECT SMBI, SMBS

                            from ITEMS
                           where dmc = temp_items.dmc
                             and nc = temp_items.nc
                             and iin = temp_items.iin )
 where record_id in (select record_id 
                       from validation_exceptions
                      where exception_id = 4)
   and EXISTS ( SELECT SMBI, SMBS
                  from ITEMS
                 where dmc = temp_items.dmc
                   and nc = temp_items.nc
                   and iin = temp_items.iin );

that single update does the work you are doing procedurally....
>declare
>
>V_SMBI items.smbi%TYPE;
>V_SMBS items.smbs%type;
>
>cursor invalid_items_cur is
>
> select dmc, nc,iin
> from temp_items
> where record_id in (select record_id from validation_exceptions
> where exception_id = 4);
>
> invalid_items_rec invalid_items_cur%ROWTYPE;
>
>BEGIN
> FOR invalid_items_rec in invalid_items_cur LOOP
>
> SELECT SMBI, SMBS INTO V_SMBI, V_SMBS
> from ITEMS
> where dmc = invalid_items_rec.dmc
> and nc = invalid_items_rec.nc
> and iin = invalid_items_rec.iin;
>
> UPDATE TEMP_ITEMS
> SET SMBI = v_SMBI,
> SMBS = v_SMBS
> where dmc = invalid_items_rec.dmc
> and nc = invalid_items_rec.nc
> and iin = invalid_items_rec.iin;
>
> END LOOP;
> END;
>/
>
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp