Can only pass one parameter to Oracle Stored Procedure 1

[jomax]

I can pass one parameter to a stored procedure and get desired results.

When I add just one more parameter, I receive the Oracle Error:

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SP_GET_CARRIER_ASP' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Using classic ASP, Oracle 10G

Things I've Tried:

The Number of parameters is correct.
The order of parameters is correct.
All parameters are VarChar and I've hardcoded in values to be sure.
I've increased the size of the VarChar input

Again, the procedure/asp code works when I adjust the stored procedure to accept one parameter and I pass one parameter. Adding one after that didn't work. I tried adding one at a time.

Stored Procedure has a package for the ref cursor out.

Code:

dim cmd3, rs3
set cmd3 = Server.CreateObject("ADODB.Command")
set rs3 = Server.CreateObject("ADODB.Recordset")
cmd3.ActiveConnection = conn
cmd3.CommandType = adCmdStoredProc
cmd3.CommandText = "CUSTOMER.SP_GET_CUSTOMER_INFO"

cmd3.Parameters.Append cmd3.CreateParameter("param1", adVarChar, adParamInput, 8000, strParam1)
cmd3.Parameters.Append cmd3.CreateParameter("param2", adVarChar, adParamInput, 8000, strParam2)
cmd3.Parameters.Append cmd3.CreateParameter("param3", adVarChar, adParamInput, 8000, strParam3)
cmd3.Parameters.Append cmd3.CreateParameter("param4", adVarChar, adParamInput, 8000, strParam4)
cmd3.Parameters.Append cmd3.CreateParameter("param5", adVarChar, adParamInput, 8000, strParam5)
cmd3.Parameters.Append cmd3.CreateParameter("param6", adVarChar, adParamInput, 8000, strParam6)
cmd3.Parameters.Append cmd3.CreateParameter("param7", adVarChar, adParamInput, 8000, strParam7)

set rs3 = cmd3.Execute

Thanks in advance!

 

[jomax]

Note - the name of the stored procedure in the Oracle Error line is the real stored procedure name and does not match my code. I changed my code to be as generic as possible, but forgot to change it in the error line.

 

[Sheco]

You can get ADO to define the paramters automatically if you don't mind an extra round-trip to the DB.

Look here:

http://support.microsoft.com/kb/165156

 

[jomax]

Thanks for the post, Sheco. This link is a good-to-know.

Turns out that I needed to move the ref_cursor parameter to the end of the parameter list in the Stored Procedure.

It was at the top of the list of parameters initially. Didn't need to pass as a parameter, though, from asp.