title: Oracle Ref Cursor Vs Select into with Exception handling
body: <p>I have a couple of scenarios:</p>
  
  <ol>
  <li><p>Need to read the value of a column from three different tables in a predefined order and only 1 table will have the data</p></li>
  <li><p>Read data from table1 if records are present for criteria given else read data from Table2 for given criteria</p></li>
  </ol>
  
  <p>In Oracle Stored Procedures</p>
  
  <p>The way these are being handled right now is to first get the count for a given query into a variable, and if the count > 0, then we execute the same query to read the actual data as in:</p>
  
  <pre><code>select count(*) from table1 into v_count
  if v_count &gt; 0
  then
      select data into v_data from table1
  end if;
  
  Return v_data
  </code></pre>
  
  <p>This is being done to avoid the no_data_found exception, otherwise I would need three exception handler blocks to catch the no_data_found exception for each table access.</p>
  
  <p>Currently I am reimplementing this with Cursors so that I have something like this:</p>
  
  <pre><code>cursor C1 is
      select data from table1;
  Open C1
  Fetch C1 into v_data
  if C1%FOUND
  then
      Close C1
      Return v_data
  End If
  </code></pre>
  
  <p>I wanted to find out which one is better from a performance point of view--the one with Cursors, or the one which does a Select into a variable and has three no_data_found Exception blocks. I don't want to use the two stage query process which we have currently.</p>