使用经典asp从oracle 11g存储过程返回结果集 [英] Return resultset from oracle 11g stored procedure using classic asp

问题描述

我们最近从 Oracle 10g 升级到 11g，现在微软提供程序 MSDAORA.1 将无法工作.我已改为使用 OraOLEDB.Oracle 提供程序，但现在我的一个返回结果集的存储过程也无法正常工作.

We recently upgraded from Oracle 10g to 11g and now the microsoft provider MSDAORA.1 won't work. I've changed to using the OraOLEDB.Oracle provider but now one of my stored procedures that is returning a resultset isn't going to work either.

旧电话:strSQL = "{调用 SYSADM.lss_pkg_catalog_pages.get_catalog_pages(?,{resultset 100, lss_media_cd, lss_page_num})}"

Old Call: strSQL = "{call SYSADM.lss_pkg_catalog_pages.get_catalog_pages(?,{resultset 100, lss_media_cd, lss_page_num})}"

所以我一直试图改变它以返回参数值，但我不断收到以下错误:Error: -2147217900 - ORA-06550: line 1, column 7: PLS-00306: wrong number or types调用GET_CATALOG_XXX"的参数数量 ORA-06550:第 1 行，第 7 列:PLS-00306:调用GET_CATALOG_XXX"的参数数量或类型错误 ORA-06550:第 1 行，第 7 列:PL/SQL:忽略语句

So I've been trying to change it to return parameter values but I keep getting the following error: 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

我猜是因为我的输出参数的数据类型，但似乎无法找到正确的类型来使用.

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.

这是我使用的代码:

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 

我有其他直接的 SQL 和其他存储过程调用，它们返回数据就好了，只是这个存储过程不能正常工作.

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.

我被问到 Oracle 存储过程中的类型声明:

I was asked about the type declaration inside the Oracle stored procedure:

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;

如果存储过程需要更多信息，请告诉我，我可以发布.

If there is more information necessary from the store procedure let me know and I can post it.

任何建议将不胜感激.

谢谢罗伯特

推荐答案

根据@searchAnResQ 在评论中的建议，我让我们的 oracle 团队重做存储过程以返回 Ref Cursor，这似乎成功了.我发现我们使用的 ODAC 版本不支持返回表.

From a suggestion by @searchAnResQ in the comments I had our oracle team redo the stored procedure to return a Ref Cursor and that seemed to do the trick. I found that returning a table wasn't supported by the ODAC version we were working with.

这篇关于使用经典asp从oracle 11g存储过程返回结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！