All geek questions in one place

Return results from oracle 11g stored procedure using classic asp

We recently upgraded from Oracle 10g to 11g, and now Microsoft's MSDAORA.1 provider will not work. I switched to using the OraOLEDB.Oracle provider, but now one of my stored procedures that returns a result set will also not work.

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

So, I tried 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 assume this is due to the data type of my output options, but cannot find a suitable type to use.

Here is the code I'm using:

 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

I have other direct SQL and other stored procedure calls that return data, just fine, this is only one stored procedure that does not work properly.

Edit: 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 additional information needed from the store procedure, let me know and I can publish it.

Any suggestions are welcome.

Thanks Robert

+4
source share
1 answer

From the @searchAnResQ suggestion in the comments, I had our oracle command to repeat the stored procedure to return the Ref Cursor, and this seemed to do the trick. I found that table return was not supported by the version of ODAC we were working with.

+1
source

Source: https://habr.com/ru/post/1447496/

More articles:


All Articles