Error in executing Oracle Stored Procedure using C#

Question

I am trying to execute an Oracle stored procedure (1 input and 2 out parameters) using C#.

My table contain 3 columns; an integer id, and 2 varchar2 type columns.

This is table definition:

CREATE TABLE TESTTABLE 
(
  ID INT Not Null,
  FNAME VARCHAR2(200),
  LNAME VARCHAR2(200),
  Constraint PK Primary Key (ID)
);

This is my stored procedure:

create or replace PROCEDURE TESTP 
(
  tempID IN TESTTABLE.ID%Type,
  tempName Out TESTTABLE.NAME%TYPE,
  tempLName out TESTTABLE.LNAME%TYPE
) 
AS 
BEGIN
    select Name, LNAME 
    into tempName, tempLName 
    from TestTable 
    where ID = tempID;
END;

Here is the code to execute this procedure from C#:

try
{
            Int32 id = 1;
            string FName = "", LName = "";

            using (_ora.GetOracleConnection())
            {
                Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("TESTP", _ora.GetOracleConnection());
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("tempId", Oracle.DataAccess.Client.OracleDbType.Int32,ParameterDirection.Input).Value = id;
                cmd.Parameters.Add("tempName", Oracle.DataAccess.Client.OracleDbType.Varchar2,200,ParameterDirection.Output).Value = FName;
                cmd.Parameters.Add("tempLName", Oracle.DataAccess.Client.OracleDbType.Varchar2,200,ParameterDirection.Output).Value = LName;

                cmd.ExecuteNonQuery();
            }
}
catch (Exception ex) 
{ 
     MessageBox.Show(ex.ToString());   
}

This is the exception generated:

Oracle.DataAccess.Client.OracleException ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "USMANDBA.TESTP", line 9

Can anyone help me ?

Answer 1

These modifications in your code worked for me:

using (connection)
{
    Int32 id = 1;
    OracleCommand cmd = new OracleCommand("TESTP", connection);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("tempID", OracleDbType.Int32, ParameterDirection.Input).Value = id;
    cmd.Parameters.Add("tempName", OracleDbType.Varchar2, 200).Direction = ParameterDirection.Output;
    cmd.Parameters.Add("tempLName", OracleDbType.Varchar2, 200).Direction = ParameterDirection.Output;
    cmd.ExecuteNonQuery();
    string FName = cmd.Parameters["tempName"].Value.ToString();
    string LName = cmd.Parameters["tempLName"].Value.ToString();
}

You could also add exception blocks in Oracle procedure to handle no_data_found exception and avoid ORA-01403 error, like here:

CREATE OR REPLACE PROCEDURE TESTP (tempID  IN  TESTTABLE.ID%Type,
    tempName out TESTTABLE.NAME%TYPE, tempLName out TESTTABLE.LNAME%TYPE) AS
BEGIN
  select Name, LNAME Into tempName,tempLName from TestTable Where ID = tempID;
EXCEPTION WHEN NO_DATA_FOUND THEN
  tempName := null;
  tempLName := null;
END;

and add additional OUT parameter informing about success or failure and handle it in C# code.