CODEWITHSUNDEEP
c#oracle-databasestored-procedures
behzad razzaqi
behzad razzaqi

Reputation: 1511

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

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?

Upvotes: 0

Views: 50

Answers (1)

APC
APC

Reputation: 146199

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.

Upvotes: 1

Related Questions