You created a FUNCTION instead of a stored procedure........ On Tue, Feb 9, 2010 at 12:23 AM, Will <[email protected]> wrote:
> I am new to VB.NET <http://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. >
