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

Reputation: 1511

Error when calling oracle procedure with c#?

I wrote this procedure in Oracle:

create or replace
PROCEDURE P1 
(
   ID_1 IN NUMBER   
  , P_NAME OUT VARCHAR2  
) AS 
BEGIN
 -- INSERT INTO A1 (ID, NAME_) VALUES (ID_1,'6666');
  SELECT NAME_ into  p_name  FROM  A1 WHERE ID=ID_1; 
END P1;


And wrote this code in c# for run that procedure:

cmd.Connection = conn;
            cmd.CommandText = "P1";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("ID_1", 88);
            cmd.Parameters.Add("p_name", OracleType.VarChar, 16).Direction = ParameterDirection.ReturnValue;
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
            Console.WriteLine(cmd.Parameters["p_name"].Value.ToString());
            cmd.Connection.Close();


But when I run the c# app I get this error:

Unhandled Exception: System.Data.OracleClient.OracleException: ORA-06550: line 1
, column 18:
PLS-00306: wrong number or types of arguments in call to 'P1'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

What happened?

Upvotes: 1

Views: 41

Answers (2)

Alex K.
Alex K.

Reputation: 175748

You need ParameterDirection.Output not ParameterDirection.ReturnValue for paramaters marked as OUT.

You are also using ExecuteNonQuery with a Query (SELECT)

Upvotes: 1

mGuv
mGuv

Reputation: 631

Taking a stab in the dark here:

// this line looks wrong as it's not the return value, that's the return value of the procedure itself.
cmd.Parameters.Add("p_name", OracleType.VarChar, 16).Direction = ParameterDirection.ReturnValue;

// I think it should be ouput
cmd.Parameters.Add("p_name", OracleType.VarChar, 16).Direction = ParameterDirection.Output;

Upvotes: 0

Related Questions