title: Oracle - Calling Stored Procedure in a Loop in another Stored Procedure
body: <p>I reviewed the answers to similar inquiries but continue to get another error that cannot seem to resolved by adding in the schema the proc was created under.</p>
  
  <p>This is the code I am attempting to run:</p>
  
  <pre><code>create or replace procedure prcReturnDVD
  -- define the parameters that this proc will accept
  (memberid_in integer, dvd_in integer)
   is
   -- Define local variables
    vNumRem number(2);
  BEGIN
  -- Update the rental table with the current date
    update rental set rentalreturneddate = current_date where memberid = memberid_in and dvdid = dvd_in;
  -- update DVD quantityonhand to reflect the return
    update dvd set dvdquantityonhand = dvdquantityonhand + 1 where dvdid = dvd_in;
  -- Check to see how many DVDs are available for rent for this member
    select get_rentalsremaining(memberid_in) into vNumRem from dual;
  -- Evaluate the next action depending on number of remaining DVD rentals available
    if vNumRem &gt;= 1 then
    -- need to write a for loop in order to cycle through the DVDs that need to be shipped out
      while vNumRem &gt;= 0 
      loop
        SYSTEM.prcShipNextDVD(memberid_in);
        vNumRem := vNumRem - 1;
      end loop;
    elsif vNumRem = 0 then
    -- message that no rentals are allowed
      dbms_output.put_line('No more rentals allowed.');
    end if;
  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('No Data Returned, process failed.');
    WHEN TOO_MANY_ROWS THEN
      dbms_output.put_line('Too many rows returned into variable, check data and try again.');
    WHEN OTHERS THEN
      dbms_output.put_line('An unidentified error has occured. Please research issue with procedure.');
  END;
  /
  </code></pre>
  
  <p>Inside the LOOP statement I am attempting to call another procedure written previous by the same user (I'm using SYSTEM only because it's only a homework assignment and it simplifies permissions):</p>
  
  <pre><code>create or replace procedure prcShipNextDVD
  -- define the parameters that this proc will accept
  (memberid_in integer)
   is
   -- Define local variables
    vCountOut number(2);
    vAllowedOut number(2);
    vNextDvd number(16);
    vAddedInQueue date;
    vPlaceInQueue number(5);
  BEGIN
  -- ensure the member is eligible to take out another DVD at this time
  -- See how many movies the member currently has out
    select count(*) into vCountOut from rental where memberid = memberid_in and rentalreturneddate is null;
  -- See how many movies the member is allowed to have out
    select membershiplimitpermonth into vAllowedOut from membership m, member p where m.membershipid = p.membershipid and p.memberid = memberid_in;
    IF vCountOut &lt; vAllowedOut then
      -- If the number out currently is less than the number allowed out currently
      -- Get next DVD in queue available for shipment
      select get_nextdvd(memberid_in) into vNextDVD, datedaddedinqueue into vAddedInQueue, rentalpriority into vPlaceInQueue from rentalqueue where memberid = memberid_in;
      -- create new record in rental table for this shipment
      insert into rental (rentalid, memberid, dvdid, rentalrequestdate, rentalshippeddate) values (rental_seq.nextval, memberid_in, vNextDVD, vAddedInQueue, current_date);
      -- decrement dvdquantityon hand in dvd table
      update dvd set quantityonhand = quantityonhand - 1 where dvdid = vNextDVD;
      -- remove dvd from queue 
      delete from rentalqueue where memberid = memberid_in and dvdid = vNextDVD ;
      -- manage remaining rentalpriority records by decrementing them properly (like in 2.1)
      update rentalqueue set rentalpriority = rentalpriority - 1 where memberid = memberid_in and rentalpriority &gt;= vPlaceInQueue;
    ELSE
      -- If the member already has the maximum number of movies out at this time
      RAISE_APPLICATION_ERROR(-20101, 'Maximum number of movies out reached. You are not allowed to check out another movie at this time.');
    END IF;
  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('No Data Returned, process failed.');
    WHEN TOO_MANY_ROWS THEN
      dbms_output.put_line('Too many rows returned into variable, check data and try again.');
    WHEN OTHERS THEN
      dbms_output.put_line('An unidentified error has occured. Please research issue with procedure.');
  END;
  /
  </code></pre>
  
  <p>I am getting the following error even after adding the schema name to the procedure call. I've also tried running it with "execute" and "call" but know those do not work properly.</p>
  
  <pre><code>PROCEDURE PRCRETURNDVD compiled 
  Errors: check compiler log 
  19/7  PL/SQL: Statement ignored 
  19/14 PLS-00905: object SYSTEM.PRCSHIPNEXTDVD is invalid
  </code></pre>
  
  <p>Any other recommendations on what I'm doing wrong on this one?</p>
  
  <p>There is an issue with the procedure I'm calling but it does compile.</p>