I am new to VB.NET development and would like to know how to execute a
Stored Function.
We are using Visual Studio 2005, MS.Net framework version 2.0.
The SF takes in 6 parameters and returns a number, noted below is the
beginnig of the SF. I need to check the return value from the SF.
The application bombs when it gets to cmd.ExecuteNonQuery(), I receive
message:
Additional information: ORA-06550: line 1, column 7:
PLS-00221: 'SF_VERIFY_USERID_PASSWORD' is not a procedure or is
undefined.
All DB objects are visible to the schema login account.
I've done my research on the web and come up with the code below.
In addition I have noticed when adding New OracleParameter() that it
already has the parameter direction (ParameterDirection.Input), do I
need to add it again as noted below in my code(oraParameter.Direction
= ParameterDirection.Input) ?
//Oracle SF
CREATE OR REPLACE FUNCTION SF_VERIFY_USERID_PASSWORD (
USR_ID_IN IN VARCHAR2,
CURR_PW_IN IN VARCHAR2,
NEW_PW_IN IN VARCHAR2,
CONFIRM_PW_IN IN VARCHAR2,
PW_CHANGE_IN IN VARCHAR2,
SECURITY_LEVEL_IN IN VARCHAR2)
RETURN NUMBER
AS
ret_val NUMBER := 1;
...code
RETURN ret_val;
Noted below is the VB.net code that I have:
Function execute_SF_VERIFY_USERID_PASSWORD
(ByVal InUserID As String, ByVal InPassWord As String, ByVal new_pw_in
As String, ByVal confirm_pw_in As String, ByVal pw_change_in As
String, ByVal security_lvl_in As String) As Integer
Dim sf_rtn_value As Integer = 0
Dim conn As OracleConnection = New
OracleConnection(SQLHelper.ConnectionString())
Dim da As System.Data.OracleClient.OracleDataAdapter
Dim ds As System.Data.DataSet
Dim oraParameter As OracleParameter
conn.Open()
Dim cmd As New OracleCommand
Dim rtn_value As Integer = 0
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "SF_VERIFY_USERID_PASSWORD"
oraParameter = New OracleParameter("USR_ID_IN",
OracleType.VarChar, ParameterDirection.Input)
oraParameter.Value = InUserID
oraParameter.Direction = ParameterDirection.Input
cmd.Parameters.Add(oraParameter)
oraParameter = New OracleParameter("CURR_PW_IN",
OracleType.VarChar, ParameterDirection.Input)
oraParameter.Value = InPassWord
oraParameter.Direction = ParameterDirection.Input
cmd.Parameters.Add(oraParameter)
oraParameter = New OracleParameter("NEW_PW_IN",
OracleType.VarChar, ParameterDirection.Input)
oraParameter.Value = new_pw_in
oraParameter.Direction = ParameterDirection.Input
cmd.Parameters.Add(oraParameter)
oraParameter = New OracleParameter("CONFIRM_PW_IN",
OracleType.VarChar, ParameterDirection.Input)
oraParameter.Value = confirm_pw_in
oraParameter.Direction = ParameterDirection.Input
cmd.Parameters.Add(oraParameter)
oraParameter = New OracleParameter("PW_CHANGE_IN",
OracleType.VarChar, ParameterDirection.Input)
oraParameter.Value = pw_change_in
oraParameter.Direction = ParameterDirection.Input
cmd.Parameters.Add(oraParameter)
oraParameter = New OracleParameter("SECURITY_LEVEL_IN",
OracleType.VarChar, ParameterDirection.Input)
oraParameter.Value = security_lvl_in
oraParameter.Direction = ParameterDirection.Input
cmd.Parameters.Add(oraParameter)
oraParameter = New OracleParameter() 'Return value
oraParameter.Direction = ParameterDirection.ReturnValue
cmd.ExecuteNonQuery()
rtn_value = oraParameter.Value
conn.Close()
Return rtn_value
End Function
Thank you,
William.
