title: How in ColdFusion with in variables get a Oracle stored procedure to return values?
body: <p>ORACLE 11g - COLDFUSION 9 - REF CURSOR - WITH at least one in variable.</p>
  
  <p>How in ColdFusion with in variables get a Oracle stored procedure to return values?</p>
  
  <p>We have tried several things, we can get  a stored procedure to return a result set if we are not passing in variables but we cannot get them when we are passing in variables.</p>
  
  <p>We know how to do it calling  MS SQL.</p>
  
  <p>Thanks for any help</p>
  
  <p>P.S. we have heard this may not be possible with the current Oracle Driver is there a different Oracle driver?</p>
  
  <p>From what I have been reading online I am thinking this is not just a coding issue.  I hope I am wrong about that.  This is just an example I was working on as a proof of concept so I can start creating all of them.  I can get it work if I am not using a ref cusor.</p>
  
  <p>Error Executing Database Query.<br>
  [Macromedia][Oracle JDBC Driver]The specified SQL type is not supported by this driver.  </p>
  
  <p>The error occurred in D:\apache\htdocs\test\index.cfm: line 86</p>
  
  <p>84 :    
  85 :    
  86 :    
  87 : 
  88 : </p>
  
  <p><strong>CF Call to procedure</strong></p>
  
  <pre><code>&lt;cfstoredproc procedure="BWNGDBADEV.PACK_REFCURSOR.GETALL" datasource="mydatasourcename" returncode="no"&gt;
      &lt;cfprocparam type="InOut" cfsqltype="CF_SQL_INTEGER" variable="pPERSONNELID" value="4" null="No"&gt;
      &lt;cfprocparam type="Out" cfsqltype="CF_SQL_REFCURSOR" variable="AnyVarName"&gt;
      &lt;!--- *** This name ties StoredProc results to the query below *** ---&gt;
      &lt;cfprocresult name="myvar"&gt;
  &lt;/cfstoredproc&gt;
  </code></pre>
  
  <p><strong>Oracle Package</strong></p>
  
  <pre><code>CREATE OR REPLACE
  PACKAGE PACK_REFCURSOR
  AS
  TYPE EMP_TableRows
  IS
    REF
    CURSOR
      RETURN BWNGDBADEV.PER_PERSONNEL%ROWTYPE;
  PROCEDURE GETALL(
        pPERSONNELID IN OUT BWNGDBADEV.PER_PERSONNEL.PERSONNELID%type,
        OUTTABLE OUT EMP_TableRows);
  END PACK_REFCURSOR ;
    /
  
  
  CREATE OR REPLACE
  PACKAGE BODY PACK_REFCURSOR
  AS
  PROCEDURE GETALL(
        pPERSONNELID IN OUT BWNGDBADEV.PER_PERSONNEL.PERSONNELID%type,
        OUTTABLE OUT EMP_TableRows)
  IS
  BEGIN 
  -- implicit cursor is opened - no close needed ***    
  OPEN OUTTABLE FOR 
  SELECT * 
  FROM BWNGDBADEV.PER_PERSONNEL 
  WHERE PERSONNELID = pPERSONNELID;
  
  END GETALL ;
  
  END PACK_REFCURSOR ;
  /
  </code></pre>