title: Return resultset from oracle 11g stored procedure using classic asp
body: <p>We recently upgraded from Oracle 10g to 11g and now the microsoft provider <code>MSDAORA.1</code> won't work. I've changed to using the <code>OraOLEDB.Oracle</code> provider but now one of my stored procedures that is returning a resultset isn't going to work either.</p>
  
  <p><strong>Old Call:</strong>
      strSQL = "{call SYSADM.lss_pkg_catalog_pages.get_catalog_pages(?,{resultset 100, lss_media_cd, lss_page_num})}"</p>
  
  <p>So I've been trying to change it to return parameter values but I keep getting the following error: <code>Error: -2147217900 - ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GET_CATALOG_XXX' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GET_CATALOG_XXX' ORA-06550: line 1, column 7: PL/SQL: Statement ignored</code></p>
  
  <p>I'm guessing that it's because of the data type of my output parameters but can't seem to find the right type to use.  </p>
  
  <p><strong>Here is the code I'm using:</strong></p>
  
  <pre><code>dim con, rst1, prm1, prm2, prm3, prm4
  
  set cmCmd = Server.CreateObject("ADODB.Command")
  set con = Server.CreateObject("ADODB.Connection")
  Set Rst1 = Server.CreateObject("ADODB.Recordset")
  
  Con.Provider = "OraOLEDB.Oracle"
  Con.ConnectionString = "Data Source=XXXXXX;Password=XXXXXX;User ID=XXXXX;Persist Security Info=True"
  Con.Open
  
  cmCmd.ActiveConnection = Con
  cmCmd.CommandType = adCmdText
  
  Set Prm1 = cmCmd.CreateParameter("PRODUCT_ID", adVarChar, adParamInput, 20, strTempProductID)
  cmCmd.Parameters.Append Prm1                                
  
  Set Prm2 = cmCmd.CreateParameter("LSS_MEDIA_XX", adVarWChar, adParamOutput, 100) 
  cmCmd.Parameters.Append Prm2                
  
  Set Prm3 = cmCmd.CreateParameter("LSS_PAGE_XXX", adVarWChar, adParamOutput, 100) 
  cmCmd.Parameters.Append Prm3 
  
  strSql = "{ CALL SYSADM.lss_pkg_catalog_pages.get_catalog_pages(?, ?, ?) }" 
  
  cmCmd.CommandText = strSql
  cmCmd.Execute 
  </code></pre>
  
  <p>I have other straight SQL and other stored procedure calls that return data just fine it's just this one stored procedure that isn't working properly.</p>
  
  <p><strong>Edit:</strong>
  I was asked about the type declaration inside the Oracle stored procedure: </p>
  
  <pre><code>TYPE t_lss_media_XXX is TABLE of ps_lss_cat_XXXX.lss_XXX%TYPE
            INDEX BY BINARY_INTEGER;
  
  TYPE t_lss_page_XXX is TABLE of ps_lss_cat_XXXX.lss_XXXX%TYPE
             INDEX BY BINARY_INTEGER;
  </code></pre>
  
  <p>If there is more information necessary from the store procedure let me know and I can post it.</p>
  
  <p>Any suggestions would be greatly appreciated.</p>
  
  <p>Thanks Robert</p>