Oracle Stored Proc access - OleDb vs OracleClient

[jimfollett1]

Heya,

Can anyone put me on the right track with respect to the error I'm getting whilst calling an Oracle Stored proc from vb.net 2005. I have been trawling through code examples high and low, but to no avail. Here's my calling code to a simple, one output, stored proc. It gives the error whilst calling "cmd2.ExecuteScalar":

PLS-00306: wrong number or types of arguments in call to 'TEST'

However, when I changed the provider to System.Data.OleDb it worked fine - any theories?

cheers,
James


VB.NET code

Dim conn2 As New System.Data.OracleClient.OracleConnection("...")
Dim cmd2 As New System.Data.OracleClient.OracleCommand
conn2.Open()
cmd2.Connection = conn2
cmd2.CommandType = CommandType.StoredProcedure
cmd2.CommandText = "test"
 
cmd2.Parameters.Add("Id", OracleClient.OracleType.Int32, 9)
cmd2.Parameters("Id").Direction = ParameterDirection.Output
 
cmd2.ExecuteScalar()
Dim j As Integer = CInt(cmd2.Parameters("").Value)
conn2.Close()

Stored Proc:

PROCEDURE test 
(outId out number)
IS
BEGIN
SELECT SEQ.nextval into outId FROM dual;
END test;

 

[jmcilhinney]

Your sproc has a parameter named "outId" while you're creating an OracleParameter named "Id". OLEDB is probably simply making the assumption that if there's only one parameter in the sproc and one in the command then they are the same parameter but OracleClient is enforcing matching names, which it should in my opinion. If they represent the same thing then they should have the same name. What if you had 10 parameters? What kind of brothel would it be if none of the names matched then?

 

[jimfollett1]

Heya jmcilhinney,

Thank you kindly. You hit the nail on the head (not perhaps with avoiding a nameless 10 member brothel) but with the name matching.

I changed my parameter name to "outID" and bingo OracleClient was playing ball. I agree the stricter naming system is the way forward, but wouldn't life be so much easier if Oracle gave less generic error messages...

cheers,
Jim