title: Call Oracle stored proc with output parameter
body: <p>I'm working with SSIS 2008 and am having a problem calling an Oracle stored procedure that has an output parameter.</p>
  
  <p>I call the stored procedure in SqlPlus like this:</p>
  
  <pre><code>var vresult number;
  exec my_stored_procedure(:vresult);
  print vresult;
  </code></pre>
  
  <p>The statements work and I get the output I need. I am trying to do something similar in SSIS, yet I need to do this repeatedly, maybe in a ForEach or a script to update a temporary result set with the result of calling the stored procedure (the stored procedure generates a number, and I need to add that number to each row in a result set which just holds some state information).</p>
  
  <p>I have tried a lot of different approaches and always end up with 'invalid statement' or similar errors. </p>
  
  <p>I have also tried the following approaches: </p>
  
  <ol>
  <li><p><a href="https://stackoverflow.com/questions/58540/ssis-oracle-parameter-mapping">How to resolve SQL query parameters mapping issues while using Oracle OLE DB provider?</a></p></li>
  <li><p><a href="https://stackoverflow.com/questions/5168772/update-a-row-in-oracle-using-oledb-commandssis">Update a row in oracle using OLEDB command(SSIS)</a></p></li>
  <li><p><a href="https://stackoverflow.com/questions/5006365/oracle-variables">Oracle variables</a></p></li>
  </ol>
  
  <p>The crux of the problem seems to be the stored procedure's output parameter.</p>
  
  <p>I have tried using the the Oracle Provider for OLE DB. Any ideas?</p>