Take the 2-minute tour ×

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.

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

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.

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"

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

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.

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

TYPE t_lss_page_XXX is TABLE of ps_lss_cat_XXXX.lss_XXXX%TYPE

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

Any suggestions would be greatly appreciated.

Thanks Robert

share|improve this question
Pls check this link from OTN :Stored Procedures and Functions Returning Rowsets –  SearchAndResQ Nov 22 '12 at 6:57
I looked at that site and specifically the section on PLSQLRSet and even copied the code and modified it for my situation and still get the same error. –  Robert Nov 25 '12 at 4:52
can you show the modified code ? you could edit your question . –  SearchAndResQ Nov 26 '12 at 5:34
.. And, as that link shows, can you change the types of those two to ref_cursors ? –  SearchAndResQ Nov 26 '12 at 10:55
The code sample is the code I'm using which was copied from that site and modified to meet my needs. –  Robert Nov 26 '12 at 13:45
show 3 more comments

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer


