Oracle Stored Procedure Problem in .NET
-
My problem is this, i have a stored procedure in oracle, and i which authenticates a user on the basis of loginName and password. the problem is that when i accesssed it through C# giving the two parameter as input and one parameter as output. there is no value return in output parameter. can some on tell me how to get the output parameter value in my code Thanks In Advance //------------- stored procedure name is ===========SecurityPkg.Authenticate============= PROCEDURE Authenticate(loginName IN VARCHAR2,loginPassword IN VARCHAR2, loginResult OUT INT ) IS objectId NUMBER := -1; objectPwd VARCHAR2(100); passwordExpiredDate DATE; BEGIN Select OBJECT_ID,PASSWORD,PASSWORD_EXPIRES INTO objectId,objectPwd,passwordExpiredDate From Application_User Where Application_User.Login_Name=loginName; IF(objectId = -1) THEN loginResult := ACCOUNT_DOESNOT_EXITS; END IF; IF(objectPwd != loginPassword) THEN loginResult := WRONG_PASSWORD; End if; IF(passwordExpiredDate < SYSDATE) THEN loginResult := PASSWORD_EXPIRED; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN loginResult := ACCOUNT_DOESNOT_EXITS; END Authenticate; //------------------- csharp code that access the above stored procedure of oracle OracleCommand cmd=new OracleCommand(); cmd.CommandText="Security_Pkg.Authenticate"; cmd.Connection=myConnection; cmd.CommandType=CommandType.StoredProcedure; // first param OracleParameter loginNameParam=new OracleParameter("loginName",OracleDbType.Varchar2); loginNameParam.Direction=ParameterDirection.Input; loginNameParam.Value="Ali"; // 2nd param OracleParameter loginPasswordParam=new OracleParameter("loginPassword",OracleDbType.Varchar2); loginPasswordParam.Direction=ParameterDirection.Input; loginPasswordParam.Value="pass"; // 3rd param OracleParameter loginResultParam=new OracleParameter("loginResult",OracleDbType.Int16); loginResultParam.Direction=ParameterDirection.Output; cmd.Parameters.Add(loginNameParam); cmd.Parameters.Add(loginPasswordParam); cmd.Parameters.Add(loginResultParam); cmd.ExecuteNonQuery(); Now when i check the value of loginResultParam, its null. can any one tell me how to get its value
-
My problem is this, i have a stored procedure in oracle, and i which authenticates a user on the basis of loginName and password. the problem is that when i accesssed it through C# giving the two parameter as input and one parameter as output. there is no value return in output parameter. can some on tell me how to get the output parameter value in my code Thanks In Advance //------------- stored procedure name is ===========SecurityPkg.Authenticate============= PROCEDURE Authenticate(loginName IN VARCHAR2,loginPassword IN VARCHAR2, loginResult OUT INT ) IS objectId NUMBER := -1; objectPwd VARCHAR2(100); passwordExpiredDate DATE; BEGIN Select OBJECT_ID,PASSWORD,PASSWORD_EXPIRES INTO objectId,objectPwd,passwordExpiredDate From Application_User Where Application_User.Login_Name=loginName; IF(objectId = -1) THEN loginResult := ACCOUNT_DOESNOT_EXITS; END IF; IF(objectPwd != loginPassword) THEN loginResult := WRONG_PASSWORD; End if; IF(passwordExpiredDate < SYSDATE) THEN loginResult := PASSWORD_EXPIRED; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN loginResult := ACCOUNT_DOESNOT_EXITS; END Authenticate; //------------------- csharp code that access the above stored procedure of oracle OracleCommand cmd=new OracleCommand(); cmd.CommandText="Security_Pkg.Authenticate"; cmd.Connection=myConnection; cmd.CommandType=CommandType.StoredProcedure; // first param OracleParameter loginNameParam=new OracleParameter("loginName",OracleDbType.Varchar2); loginNameParam.Direction=ParameterDirection.Input; loginNameParam.Value="Ali"; // 2nd param OracleParameter loginPasswordParam=new OracleParameter("loginPassword",OracleDbType.Varchar2); loginPasswordParam.Direction=ParameterDirection.Input; loginPasswordParam.Value="pass"; // 3rd param OracleParameter loginResultParam=new OracleParameter("loginResult",OracleDbType.Int16); loginResultParam.Direction=ParameterDirection.Output; cmd.Parameters.Add(loginNameParam); cmd.Parameters.Add(loginPasswordParam); cmd.Parameters.Add(loginResultParam); cmd.ExecuteNonQuery(); Now when i check the value of loginResultParam, its null. can any one tell me how to get its value
It should work, according to the documentation. I've also done this many times using the SqlClient, which works similarily (from callers' perspectives). Have you tested your stored procedure using some Oracle command execution environment? I must admit that I don't know much about Oracle (for my own personal reasons) but I know that in SQL Server you have to declare the size of your char arrays, even for parameters declared in the stored procedure, as well as the size of them when declaring your parameters to use with ADO.NET.
Microsoft MVP, Visual C# My Articles
-
It should work, according to the documentation. I've also done this many times using the SqlClient, which works similarily (from callers' perspectives). Have you tested your stored procedure using some Oracle command execution environment? I must admit that I don't know much about Oracle (for my own personal reasons) but I know that in SQL Server you have to declare the size of your char arrays, even for parameters declared in the stored procedure, as well as the size of them when declaring your parameters to use with ADO.NET.
Microsoft MVP, Visual C# My Articles
I believe that the problem lies in your PL/SQL code. The one thing that I notice is that you never assign a default value to the output parameter in your sproc. If none of the
Ifconditions test true, thenloginResultis returned with the database default value. In my testing (VS.NET 2002, Oracle.DataAccess, Oracle 9i), the output parameter defaults to null regardless of data type declaration. By settingloginResultto your default success value before running theSelectstatement, you should get the desired result. Hope that helps. :) --Jesse -
My problem is this, i have a stored procedure in oracle, and i which authenticates a user on the basis of loginName and password. the problem is that when i accesssed it through C# giving the two parameter as input and one parameter as output. there is no value return in output parameter. can some on tell me how to get the output parameter value in my code Thanks In Advance //------------- stored procedure name is ===========SecurityPkg.Authenticate============= PROCEDURE Authenticate(loginName IN VARCHAR2,loginPassword IN VARCHAR2, loginResult OUT INT ) IS objectId NUMBER := -1; objectPwd VARCHAR2(100); passwordExpiredDate DATE; BEGIN Select OBJECT_ID,PASSWORD,PASSWORD_EXPIRES INTO objectId,objectPwd,passwordExpiredDate From Application_User Where Application_User.Login_Name=loginName; IF(objectId = -1) THEN loginResult := ACCOUNT_DOESNOT_EXITS; END IF; IF(objectPwd != loginPassword) THEN loginResult := WRONG_PASSWORD; End if; IF(passwordExpiredDate < SYSDATE) THEN loginResult := PASSWORD_EXPIRED; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN loginResult := ACCOUNT_DOESNOT_EXITS; END Authenticate; //------------------- csharp code that access the above stored procedure of oracle OracleCommand cmd=new OracleCommand(); cmd.CommandText="Security_Pkg.Authenticate"; cmd.Connection=myConnection; cmd.CommandType=CommandType.StoredProcedure; // first param OracleParameter loginNameParam=new OracleParameter("loginName",OracleDbType.Varchar2); loginNameParam.Direction=ParameterDirection.Input; loginNameParam.Value="Ali"; // 2nd param OracleParameter loginPasswordParam=new OracleParameter("loginPassword",OracleDbType.Varchar2); loginPasswordParam.Direction=ParameterDirection.Input; loginPasswordParam.Value="pass"; // 3rd param OracleParameter loginResultParam=new OracleParameter("loginResult",OracleDbType.Int16); loginResultParam.Direction=ParameterDirection.Output; cmd.Parameters.Add(loginNameParam); cmd.Parameters.Add(loginPasswordParam); cmd.Parameters.Add(loginResultParam); cmd.ExecuteNonQuery(); Now when i check the value of loginResultParam, its null. can any one tell me how to get its value
Try closing the connection object before you check the output parameter value. I haven't the faintest idea why, but with sql7.0 I get null for output parameters until after I call the close method on the connections... Someone smarter than I will have to take a shot at why that might be... Grasping... Bill