ORA-06550 error in .net with C#

[bgundas]

Hello, I need immediate help with this problem.

From C# code I am trying to call a stored procedure, which is in a

oracle 8i db.


First of all here is the definition of the function(oracle function).

//==================================================================

FUNCTION INSERT_COMMENTS( iv_acc_id IN VARCHAR2,

iv_user_id IN VARCHAR2,

iv_comment IN VARCHAR2 )

RETURN BOOLEAN;

//==================================================================









My Catch catches this error code coming for Oracle side.

//==================================================================

:In insertComments2 method => ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to
'INSERT_COMMENTS'

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

//==================================================================











And here is my C# code that tries to make the call to the oracle.

//==================================================================

public static void insertComments2(string accID)

{

try

{

string useridv = "DAYBREAK";

string STR_COMMMENT = "Wellcome letter has been
generated";


//==============================================================================

//create an instance of the command object
giving the procedure name

OleDbCommand sqlCmd2 = new
OleDbCommand("ACCP50.INSERT_COMMENTS",myConn) ;



// Define the command type u r executing as a
Stored Procedure.

sqlCmd2.CommandType =
CommandType.StoredProcedure ;




sqlCmd2.Parameters.Add("iv_acc_id",OleDbType.VarChar,20);

sqlCmd2.Parameters["iv_acc_id"].Direction =
ParameterDirection.Input ;




sqlCmd2.Parameters.Add("iv_user_id",OleDbType.VarChar, 20);

sqlCmd2.Parameters["iv_user_id"].Direction =
ParameterDirection.Input ;




sqlCmd2.Parameters.Add("iv_comment",OleDbType.VarChar,40);

sqlCmd2.Parameters["iv_comment"].Direction =
ParameterDirection.Input ;



sqlCmd2.Parameters.Add("RETURN
BOOLEAN",OleDbType.Boolean);

sqlCmd2.Parameters["RETURN BOOLEAN"].Direction
= ParameterDirection.ReturnValue;



detailLog(accID,w);



//sqlCmd2.Parameters["RETURN
BOOLEAN"].Direction = ParameterDirection.ReturnValue;

//sqlCmd2.Parameters["RETURN BOOLEAN"];



sqlCmd2.Parameters["iv_acc_id"].Value = accID;
;



sqlCmd2.Parameters["iv_user_id"].Value =
useridv;



sqlCmd2.Parameters["iv_comment"].Value =
STR_COMMMENT;



// execute the stored procedure

sqlCmd2.ExecuteNonQuery();



// if ((string) (sqlCmd2.Parameters["RETURN
BOOLEAN"].Value.ToString()) == "true")

// detailLog(" Success. Comments has been
inserted successfully.", w);

// else

// detailLog(" FAILED. Comment insertion
failed.", w);



}

catch (Exception error)

{

detailLog("In insertComments2 method => "
+error.Message, w);

System.Console.Write(error.Message);

}

}



//===============================================================

 

[David Browne]

Hello, I need immediate help with this problem.

oracle 8i db.

First, why are you using OleDb instead of one of the .NET providers for
Oracle? No one knows or cares how OleDb does parameter binding for Oracle
functions.

Second, just write out the PL/SQL block for invoking this function, and the
problem should fall out.

begin
:rv := INSERT_COMMENTS( :iv_acc_id, :iv_user_id, :iv_comment);
end;

This block should work in SQLPlus, Toad, ODBC, OleDB, OracleClient, ODP.NET,
JDBC, Pro*C, etc. Note that :rv should be bound to an output parameter
here, and the commandType is text.

David