title: Calling Oracle PL/SQL stored procedure from java middle tier using JDBC on Linux?
body: <p>Newbie question... Can anyone provide a high level description of what things need to be done in a PL/SQL stored procedure (residing in a database Linux server) to send data to a Java program (in an application server)? </p>
  
  <p>UPDATE 1</p>
  
  <p>elrado's answer below moved me forward (thanks!). I can see that the PL/SQL stored procedure simply needs to set an OUT parameter as REF CURSOR (e.g. SYS_REFCURSOR). Then, the calling Java routine can use something like this:</p>
  
  <pre class="lang-java prettyprint-override"><code>import oracle.jdbc.*;
  ...
  // call stored procedure using SQL92 syntax
  CallableStatement cs = conn.prepareCall( "{call myStoredProc (?,?,?,?,?)}" );
  
  // set IN parameters
  cs.setString(1, in1var);
  cs.setString(2, in2var);
  cs.setString(3, in3var);
  
  // register OUT parameters
  cs.registerOutParameter(4, Types.VARCHAR);
  cs.registerOutParameter(5, OracleTypes.CURSOR);
  
  // execute and retrieve Oracle "ref cursor" as a Java "ResultSet"
  cs.execute();
  rs = (ResultSet) cs.getObject(5);
  
  // process result
  while (rs.next()) {
    ...
  }
  
  // always retrieve ResultSet before OUT parameters
  out1var = cs.getInt(4);
  </code></pre>
  
  <p>QUESTION 1: Does the above look OK?</p>
  
  <p>I see in Oracle's documentation (see pages 4-14 to 4-15 <a href="http://isu.ifmo.ru/docs/doc112/java.112/e10589.pdf" rel="nofollow">http://isu.ifmo.ru/docs/doc112/java.112/e10589.pdf</a>) that I should use an <code>OracleCallableStatement</code> instead of a <code>CallableStatement</code> so that it should look like this:</p>
  
  <pre class="lang-java prettyprint-override"><code>// execute and retrieve Oracle "ref cursor" as a Java "ResultSet"
  cs.execute();
  rs = {(OracleCallableStatement)cs}.getCursor(5);
  </code></pre>
  
  <p>QUESTION 2: Are both methods fine? If so, what are the pro's and con's for using one over the other? </p>
  
  <p>QUESTION 3: I didn't understand the <code>Important</code> note on page 4-15 regarding Oracle 11G database:</p>
  
  <p><code>Unlike in past releases, the cursor associated with a REF CURSOR is not closed when the result set object in which the REF CURSOR was materialized is closed.</code></p>
  
  <p>Does this mean that the <code>ref cursor</code> is closed when <code>cs</code> is closed (as opposed to previous database releases, which closed <code>ref cursor</code> when <code>rs</code> is closed)?</p>