Why do I get an Oracle error when calling a procedure with C#?

Question

I wrote this procedure in Oracle 11g:

create or replace
PROCEDURE P1 
(
   ID_1 IN NUMBER   
  , P_NAME OUT VARCHAR2  
) AS 

  BEGIN
SELECT NAME_ into  p_name  FROM  A1 WHERE ID=ID_1; 
END P1;

and I wrote this code in C# to call the procedure:

OracleConnection conn = new OracleConnection("User Id=webservice_access;Password=DAMAVAND;Server=ORA11;");
//OracleConnection conn = new OracleConnection("User Id=SYSTEM;Password=123456;Server=ORA11;");
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "P1";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ID_1", 1);
cmd.Parameters.Add("p_name", OracleType.VarChar, 16).Direction = ParameterDirection.Output;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
Console.WriteLine(cmd.Parameters["p_name"].Value.ToString());
cmd.Connection.Close();

but when I run the C# application I get this error:

ORA-06512:at "WEBSERVICE_ACCESS.P1",Line 10
ORA-06512:at line 1

What happened? Why do I get that error?

Have you tried running the procedure in a SQL worksheet (SQL*Plus, TOAD, etc)? What happens? — APC
@APC yes my friend i run that procedure on the sql developer and every thing fine!and work,i test that code on two server,one server work and another not work! — behzad razzaqi

APC APC · Accepted Answer · 2015-07-01T05:57:12

ORA-06512 indicates an unhandled exception in your procedure. You have no error handling in your code, so that's reasonable.

Of course, because you have no error handling it's pretty hard for anybody to know what the error is. Most likely is data: either you have no record in A1 where ID=1 (i.e. NO_DATA_FOUND exception) or you have more than one such record (i.e. TOO_MANY_ROWS exception).

ODP has a class for handling exceptions. Find out more.

Why do I get an Oracle error when calling a procedure with C#?

1 Answers