AnsweredAssumed Answered

Error in Stored Procedure Exact Fetch returns more than requested number o

Question asked by Lowell on Apr 30, 2010
Latest reply on May 1, 2010 by Dave
I have a stored procedure that now errors out with the error message:   Execution Failed: ORA-2000:   Error in Proc ORA-01422: exact fetch returns more than requested number of rowsIs there another way to write this that would work?PROCEDURE TRG_BEELINE_RESVND_SP
ASv_count number;
v_name srm_resources.unique_name%type;cursor c_resource_id is
  select BEELINE_RESOURCE_ID from TRG_BEELINE_RESVND;
 
BEGIN
   
      for c_resource_id_rec in c_resource_id loop
     
      select count(*) into v_count from SRM_RESOURCES R
      where substr(R.unique_name,2,6) = c_resource_id_rec.beeline_resource_id and R.is_active = '1';
 
      case           when (v_count = 1) then
          --Selects the Unique_Name (employee ID) from SRM_Resources when there is 1 active resource
          select R.unique_name into v_name from srm_resources R
          where substr(R.unique_name,2,6) = c_resource_id_rec.beeline_resource_id and R.is_active = '1';
          update TRG_BEELINE_RESVND
          set RESOURCE_ID = v_name
          where beeline_resource_id = c_resource_id_rec.beeline_resource_id;
                 
          when (v_count > 1) then
          --Concatenates the 'r' with the Resource ID passed from Beeline if there is more than 1 active resource
          Select R.unique_name into v_name from SRM_RESOURCES R;
          if v_name = 'r' || c_resource_id_rec.beeline_resource_id
          then
          update TRG_BEELINE_RESVND          
          set RESOURCE_ID = 'r' || c_resource_id_rec.beeline_resource_id
          where beeline_resource_id = c_resource_id_rec.beeline_resource_id;
          end if;           when (v_count = 0) then
          null;     end case;     end loop;     commit;
   
EXCEPTIONWHEN OTHERS
    THEN
          RAISE_APPLICATION_ERROR (-20000, 'Error in Proc ' || SQLERRM);END TRG_BEELINE_RESVND_SP;

Outcomes