PL/SQL numeric or value error in C#

Question

 Hi

i am trying to retrieve a value from my database through a stored procedure. i get a PL/SQL numeric or value error when i run the code.

PL/SQL

   create or replace
   PROCEDURE PROD_PASSWORD 
   (
     PR_USERNAME IN VARCHAR2  
   , PWORD OUT VARCHAR2  
   ) AS 
   BEGIN
     select p_password into pword from tbl_personnel where p_username = pr_username;
   END PROD_PASSWORD;

C#

   string oradb = "Data Source=localhost:1521/XE;User Id=system;Password=Blue1960;";

   OracleConnection conn = new OracleConnection(oradb); // C#

   conn.Open();

   OracleCommand cmd = new OracleCommand("PROD_PASSWORD", conn);
   cmd.BindByName = true;
   cmd.CommandType = CommandType.StoredProcedure;

   cmd.Parameters.Add("PR_USERNAME", OracleDbType.Varchar2, txtUsername.Text, ParameterDirection.Input);
   cmd.Parameters.Add("PWORD", OracleDbType.Varchar2, ParameterDirection.ReturnValue);

   cmd.ExecuteNonQuery();

   txtPassword.Text = cmd.Parameters["PWORD"].Value.ToString();

   conn.Dispose();

Thanks in advance

Answer 1

Thanks Shankar

I don't understand what you mean by "Where is your Input Value that you are Passing to the Procedure ?".

From what i understand my input parameter in the procedure is given a value by adding a parameter in C# and assigning it its value from 'txtUsername.Text'. Is this a correct assumption?

the table is as follows:

 

PERSONNEL_ID    NUMBER            
F_NAME             VARCHAR2(255 BYTE)    
L_NAME             VARCHAR2(255 BYTE)    
P_USERNAME      VARCHAR2(255 BYTE)    
P_PASSWORD     VARCHAR2(255 BYTE)    
TYPE                VARCHAR2(255 BYTE)    

Hope this is what you asked for. Really appreciate the help

Thanks Lance

Answer 2

Hi Lance,

Where is your Input Value that you are Passing to the Procedure ?

Can You Please Post your Table Definition here. The PL/SQL numeric or value error in C# usually occurs occurs when we try to convert varchar datatype to number datatype and the varchar value cannot be converted to number datatype.

Thanks, Shankar M