Error executing Oracle stored procedure using C #

Question

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

My table contains 3 columns; integer id and 2 columns of type varchar2.

This is the 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 complete this procedure with 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 generated exception:

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

Can anybody help me?

Answer 1

These changes 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 can also add exception blocks in the Oracle procedure to handle the no_data_found exception and avoid the ORA-01403 , like this:

 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 an additional parameter OUT, informing about success or failure, and process it in C # code.