How do I call an Oracle function to insert data using ODP.NET and a dataset? (specific example)

We're really lost on this one, having read the ODP.NET 2 Day+ developer guide hasn't helped. I've provided the function definition (stored in a package), I don't understand what we have to cast the dataset to or what to pass the function. Here is the function definition:

FUNCTION ins (
rec_data IN OUT schema.table%ROWTYPE,
p_rowid OUT ROWID,
p_execution_ts IN schema.table.update_ts%TYPE)
RETURN NUMBER

Here is what we have done (which does nothing):

// inserts data

public void insertData(DataSet Data) 
{ 
string connStr = "DATA SOURCE=someValidConnString"; 
OracleConnection conn = new OracleConnection(connStr); 
string rowID = String.Empty; 
Int32 rtnVal = 0; 
try 
{ 
conn.Open(); 
OracleCommand insCmd = new OracleCommand("PACKAGE.ins", conn); 
insCmd.CommandType = CommandType.StoredProcedure; 
OracleParameter outParam2 = new OracleParameter("retVal", OracleDbType.Varchar2,     rtnVal, 

ParameterDirection.ReturnValue); 
insCmd.Parameters.Add(outParam2); //return value 
OracleParameter inParam1 = new OracleParameter("rec_data", OracleDbType.NVarchar2,     dsACCTData.Tables

[0].Rows[0], ParameterDirection.InputOutput); 
OracleParameter outParam = new OracleParameter("p_rowid", OracleDbType.Varchar2, rowID, 

ParameterDirection.Output); 
OracleParameter inParam2 = new OracleParameter("p_execution_ts", OracleDbType.Date, 

Oracle.DataAccess.Types.OracleDate.GetSysDate(), ParameterDirection.Input); 
insCmd.Parameters.Add(inParam1);  //first in out parameter 
insCmd.Parameters.Add(outParam);  //second out parameter 
insCmd.Parameters.Add(inParam2);  //third in parameter 


insCmd.ExecuteNonQuery(); 
conn.Close(); 

} 
catch (OracleException ee) 
{ 
throw ee; 
} 
finally 
{ 
conn.Dispose(); 

} 
}

I know this is a vry specific question but I'm 开发者_StackOverflowreally lost. Let's assume the Oracle function ins works (it does), in this instance we simply don't know how to call it correctly using ODP.NET

Many thanks for any help.

edit: here is the error message:

ORA-06550: line 1, column 15: PLS-00306: wrong number or types of arguments in call to 'INS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Kind regards, Fugu

---

maybe this could help:

http://www.c-sharpcorner.com/UploadFile/john_charles/CallingOraclestoredproceduresfromMicrosoftdotNET06222007142805PM/CallingOraclestoredproceduresfromMicrosoftdotNET.aspx

I'm looking for the same answer and I think I found something. There is example of the output variable in second row.

objCmd.Parameters.Add("pin_deptno", OracleType.Number).Value = 20;
objCmd.Parameters.Add("pout_count", OracleType.Number).Direction = ParameterDirection.Output;

Hope that link will do any good.

Regards, M

---

Every thing looks good. But you have to check all attribute name and attribute types.

In your example have bad data type OracleDbType.Varchar2, because your function return Number (in your Oracle Function definition)

OracleParameter outParam2 = new OracleParameter("retVal", OracleDbType.Varchar2, rtnVal, ParameterDirection.ReturnValue);

insCmd.Parameters.Add(outParam2); //return value