title: Stored Procedure two exception select statements
body: <p>I am having some trouble with my Stored Procedure in Oracle 12.1</p>
  
  <p>The blow code is getting an error and I think it has to do with using two EXCEPTION statements in a row? </p>
  
  <p>I am created a stored Procedure for a fake small online DVD company that updates the queue position in the rental queue table. I use an IF statement that says if the member already has this DVD in the queue, then updates the queue position. In order to check if the member already has a DVD in the queue I write an SQL Select statement that checks for the DVD in the members queue. If the statement finds a DVD then it inserts it into "dvd_check". If no DVD is found then i place DVD_check to equal 0.</p>
  
  <p>I also have a 2nd SQL statement to determine the original queue order of the row that is about to be updated. Both Statements run great on their own within the stored procedure but when I put them together in the StoredProcedure before my IF statement, then I get an error:</p>
  
  <p>Error(17,5): PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:     ( begin case declare end exit for goto if loop mod null...
  AND </p>
  
  <p>Error(29,4): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     end not pragma final instantiable order overriding static...</p>
  
  <p>Here is my Code:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE sp_queueorder (
  dvdid_sp number,
  memberid_sp number,
  queueposition_sp number)
  IS
  dvd_check number;
  old_queueposition number;
  BEGIN
  
  SELECT dvdid INTO dvd_check FROM rentalqueue  
  WHERE DVDid = dvdid_sp and memberid = memberid_sp;
  EXCEPTION
  WHEN no_data_found THEN dvd_check := 0;
  
  SELECT queueposition INTO old_queueposition FROM rentalqueue  
  WHERE DVDid = dvdid_sp and memberid = memberid_sp;
  EXCEPTION
  WHEN no_data_found THEN old_queueposition := 0;
  
  IF dvd_check &gt; 0 THEN
  
  UPDATE rentalqueue SET queueposition = queueposition + 1
  WHERE memberid = memberid_sp and queueposition &gt;= queueposition_sp AND queueposition &lt;=       old_queueposition;
  
  UPDATE rentalqueue SET queueposition = queueposition_sp
  WHERE dvdid = dvdid_sp AND memberid = memberid_sp;
  
  END IF;
  END;
  </code></pre>
  
  <p>Thanks so much for helping me solve this issue.</p>