How do I return a parameter value from an Oracle stored procedure?
I've got a stored procedure that takes 4 parameters (3 IN and 1 OUT). When I call it in SQL+ I have to declare the OUT parameter before I call the procedure using:
var v_message varchar2(50);
Then when the procedure is executed it sets the value of v_message to an error/success message, which I can then view by typing:
print v_message;
When I call the procedure from VB I can set the OUT parameter in the code and append it onto the command (with all the input parameters). This is fine and the procedure executes without a problem. However I am unable to get the value of v_message.
The VB code I'm using is:
Dim objCommand As ADODB.Command
Set objCommand = New ADODB.Command
objCommand.CommandText = "update_pass.validate_input"
objCommand.CommandType = adCmdStoredProc
Set objCommand.ActiveConnection = DBConnection
objCommand.Parameters.Append objCommand.CreateParameter("v_userid", adVarChar, adParamInput, 255)
objCommand.Parameters.Append objCommand.CreateParameter("v_new_password_1", adVarChar, adParamInput, 255)
objCommand.Parameters.Append objCommand.CreateParameter("v_new_password_2", adVarChar, adParamInput, 255)
objCommand.Parameters.Append objCommand.CreateParameter("v_message", adVarChar, adParamOutput, 255)
objCommand("v_userid") = UserName
objCommand("v_new_password_1") = NewPass
objCommand("v_new_password_2") = ConfNewPass
objCommand.Execute
No probs so far, but when I try to do this:
MsgBox objCommand1("v_userid")
MsgBox objCommand1("v_new_password_1")
MsgBox objCommand1("v_message")
v_message is always blank.
How do I get the value of this parameter?????
