使用传统的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（？，{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})}"

所以，我一直在试图改变它返回的参数值，但我不断收到以下错误：错误：-2147217900 - ORA-06550：第1行，第7列：PLS-00306：错号码或呼叫为GET_CATALOG_XXXORA-06550类型的参数：第1行，第7列：PLS-00306：错号码或类型的呼叫'GET_CATALOG_XXXORA-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.

这里是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 

我还有其他的直接的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.

任何建议将是极大的AP preciated。

Any suggestions would be greatly appreciated.

感谢罗伯特

推荐答案

在我有我们的Oracle团队重做存储过程返回一个REF CURSOR而且似乎这样的伎俩的意见建议由@searchAnResQ。我发现，返回表不是由我们与被工作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屋！