title: Calling an Oracle SP from ADO
body: <p>I'm trying to call a Stored Procedure with both in and out parameters from ADO</p>
  
  <p>First a Little packagde and a SP :</p>
  
  <pre><code>CREATE OR REPLACE PACKAGE CD_package IS
     TYPE CD_cursor IS REF CURSOR;
     PROCEDURE CDList ( pCost IN Number, pList OUT CD_Cursor ) ;
  END CD_package;
  /
  CREATE OR REPLACE PACKAGE BODY CD_package AS
  PROCEDURE CDList ( pCost IN Number, pList OUT CD_Cursor ) IS
  -- returns a list of titles available at cost pCost
  BEGIN
     OPEN pList FOR
      SELECT CURRENT_TIMESTAMP FROM DUAL;
  
  END CDList;
  
  END CD_package;
  </code></pre>
  
  <p>And my Delphi Code:</p>
  
  <pre><code>      With SP do
        begin
          Close;
          Connection := ADOConnection1;
    ProcedureName := 'CD_package.CDList';  
          Parameters.Clear;
          Parameters.CreateParameter('pCost', ftFloat, pdInput, 0, 2.2);
          Open;
          Last;
        end; // with
  </code></pre>
  
  <p>SP I a TADOStoredProc and ADOConnection1 is a valid connection to my OracleDatabase.</p>
  
  <p>According to seval sites you should NOT create the OUT procedure. But here I end up in a catch 22.</p>
  
  <p>If I omit the parameter I gent an error about missing paramater: <a href="https://i.stack.imgur.com/7n5hz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7n5hz.png" alt="enter image description here"></a></p>
  
  <p>If I add a Cursor parameter </p>
  
  <pre><code>...
      Parameters.CreateParameter('pList', ftCursor, pdOutput, -1, NULL);
  ...
  </code></pre>
  
  <p>I get an error that the parameters isn't declared correctly 
  <a href="https://i.stack.imgur.com/hMjRc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hMjRc.png" alt="enter image description here"></a></p>
  
  <p>The obvious vould be swich away from ADO, but that is not possible since the program id filled with references to <code>_RecordSet</code> </p>
  
  <p>So in short how do I call a SP in Oracle using ADO? </p>
  
  <p>The only fixed things are Delphi and ADO. You are free to change the TADOStoredProc into an TADOCommand or query. As long as it is working. </p>