I figured it out and it works now. I needed to add the size property
to the parameter. See code below.
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 var_length As Integer = 30
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"
//Input parameters
oraParameter = New OracleParameter("USR_ID_IN", OracleType.VarChar,
ParameterDirection.Input)
oraParameter.Size = InUserID.Length
oraParameter.Value = InUserID
oraParameter.Direction = ParameterDirection.Input
cmd.Parameters.Add(oraParameter)
//Return Value parameters
oraParameter = New OracleParameter("rtn_value", OracleType.Int32,
ParameterDirection.ReturnValue) 'Return value
oraParameter.Size = var_length
oraParameter.Value = rtn_value
oraParameter.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(oraParameter)
da = New OracleDataAdapter(cmd)
ds = New DataSet
sf_rtn_value = da.Fill(ds)
sf_rtn_value = Convert.ToInt32(cmd.Parameters("rtn_value").Value)
conn.Close()
Thanks for everyones help.
On Feb 9, 12:49 am, Raghupathi Kamuni <[email protected]> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
