ORACLE Error in loop, need to fill one variable per update

Question

I have a cursor that retrieve the dates that I need to filter, then the loop where I get the values and one update with those variables

  DECLARE
      TYPE two_cols_rt IS RECORD
      (
      objectid_new BIDDATA.BID_GEN.OBJECTID%TYPE,
       bidid_new BIDDATA.BID_GEN.BIDID%TYPE
      );
    
    CURSOR c IS
        SELECT x.column_value startdate, x.column_value + 6/24 enddate
        FROM TABLE(
                   prod.date_utils(i_startdate     => date '2015-4-21'
                                                   ,i_enddate       => date '2015-4-21'
                                                   ,i_date_interval => 'HH')) x
        WHERE MOD( TO_NUMBER( TO_CHAR(x.column_value, 'hh24') ), 6 ) = 0;
        
     TYPE  l_objects_t IS TABLE OF two_cols_rt;
     l_objects   l_objects_t;  
        
     BEGIN
        FOR rec IN c
        LOOP
            SELECT (SELECT prod.admin.find('UNIT'
                                               ,'unit'
                                               ,x.res_name
                                               ,TRUNC(sysdate, 'dd')
                                                ,'N')
            FROM dual) objectid_new
            ,x.BIDID  
            BULK COLLECT INTO l_objects
            FROM biddata.bid_gen x
            WHERE sced_time_stamp BETWEEN rec.startdate AND rec.enddate;  
         
            UPDATE BIDDATA.BID_GEN set objectid=l_objects.objectid_new
            WHERE bidid=l_objects.bidid_new;
          
                
        END LOOP;
    END;

But show me this error:

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 17
ORA-06512: at line 17

I dont need to return, I just need to update one row per all the ids that get the first select

Regards

Answer 1

If you want to keep the loop, you can update multiple rows

   UPDATE BIDDATA.BID_GEN_RT_ERCOT x
      set objectid=prod.object_detail_admin.find('ERCOT_UNIT'
                                       ,'ERCOT'
                                       ,x.res_name
                                       ,trunc(sysdate, 'dd')
                                        ,'N')
    WHERE x.sced_time_stamp between rec.startdate and rec.enddate;  

You could get rid of the loop entirely and just do

   UPDATE BIDDATA.BID_GEN_RT_ERCOT 
      set objectid=prod.object_detail_admin.find('ERCOT_UNIT'
                                       ,'ERCOT'
                                       ,res_name
                                       ,trunc(sysdate, 'dd')
                                        ,'N')
    WHERE exists( select 1
                    from table(
           prod.date_utils.get_listofdates_(i_startdate     => date '2015-4-21'
                                           ,i_enddate       => date '2015-4-21'
                                           ,i_date_interval => 'HH')) lod
                   where mod( to_number( to_char(lod.column_value, 'hh24') ), 6 ) = 0
                     and x.sced_time_stamp between lod.column_value 
                                               and lod.column_value + 6/24 );

If you want to keep the loop and the local variable and the separate select and update statements, you'd need to iterate over the collection in order to do the update

forall i in 1 .. l_objects.count
  update BIDDATA.BID_GEN_RT_ERCOT 
     set objectid=l_objects(i).objectid_new
   WHERE bidid=l_objects(i).bidid_new;