Stored Procedures OUT parameters

[Chris Wallace]

Can anyone help,

From VB6 I can call an Oracle stored procedure no problem. However, I need to get a return value from the stored procedure.

To do this I have written an Oracle function that sums 2 values, here it is:
********
function ExFunct (Num1 in number, Num2 in Number ) return number
is
temp number;

begin
temp := Num1 + Num2;
return temp;
end ExFunct;

'***************************
'***HERE IS THE VB6 CODE****

Public Sub GetSPVal()
'Executes an action query, returns RecordsAffected

On Error GoTo ehExecuteSQL

Dim cn As ADODB.Connection
Dim objComm As ADODB.Command

Set cn = New ADODB.Connection
Set objComm = New ADODB.Command

cn.Open strConnect

Set objComm.activeconnection = cn
objComm.commandtype = adCmdText

objComm.CommandText = "{? = call ExFunct (2,2)}"

objComm.execute

MsgBox objComm.Parameters(0).Value
'cn.execute strSQL, ExecuteSQL 'RecordsAffected is returned

Exit Sub

ehExecuteSQL:
'If Transaction is not commited, it will be rolled back
ExecuteSQL = -2 '-2 indicates error condition
End Sub

*****
MY ERROR THAT I GET IS:-
[Oracle][ODBC Oracle Driver]SQLBindParameter has not been called for parameter #1.


Thanks for any help,

Chris

[Clunietp]

Hi Chris

I'm no Oracle expert, but it appears as though your stored procedure is returning a value, NOT using output parameters. In any case, try adding this line of code (you'll have to customize it, of course)

cmd.Parameters.Append cmd.CreateParameter("myname", adInteger, adParamReturnValue, , intMyIntegerVariable)

Make sure this one is the first parameter added (via parameters.append). This works with SQL server return values, it should be the same for Oracle...


HTH

Tom