2 Replies Latest reply: Mar 19, 2012 11:55 PM by 892258 RSS

    ORA-06502 error while running procedure, from visual studio using odp.net

    892258
      Hi,

      I am trying to execute a procedure in oracle from my asp.net application. Please see the below code

      PROCEDURE
      ==========
      PROCEDURE sp_AdminLogin(usr credentials.username%TYPE,pwd out credentials.password%TYPE)

      IS
      BEGIN
      SELECT password into pwd FROM Credentials where username=usr ;

      END sp_AdminLogin;

      =============
      C# page
      ========

      OracleConnection objConn = con.getConnection();
      OracleCommand objCmd = new OracleCommand();

      objCmd.Connection = objConn;
      objCmd.CommandText = " sp_AdminLogin";
      objCmd.CommandType = CommandType.StoredProcedure;
      objCmd.Parameters.Add("usr", OracleDbType.Varchar2).Value = txtUserName.Text.ToString();

      objCmd.Parameters.Add("pwd", OracleDbType.Varchar2).Direction = ParameterDirection.Output;


      try

      {

      objConn.Open();

      objCmd.ExecuteNonQuery();
      if(txtPassword.Text.ToString()== objCmd.Parameters["pwd"].Value)

      System.Console.WriteLine("You Are Successfully Logged in");
      else
      System.Console.WriteLine("Invalid Username or password");
      }

      catch (Exception ex)

      {

      System.Console.WriteLine("Exception: {0}",ex.ToString());

      }



      objConn.Close();
      ==================
      But, i am getting the below error
      ORA-06502: PL/SQL: numeric or value error
      ORA-06512: at "SCMTICKET.SP_ADMINLOGIN", line 5
      ORA-06512: at line 1

      When I had run the procedure directly,it is giving the correct output without any error..datatype is correct..Then,why I am getting this error..Please help to solve this issue..

      Edited by: hari on Mar 19, 2012 7:05 AM