Databases
StackOverflow2013
Postsdbo
POHow do I call an Oracle function to insert data using ODP.NET and a dataset? (specific example)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I call an Oracle function to insert data using ODP.NET and a dataset? (specific example)
    text
    copied!<p>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:</p> <pre><code>FUNCTION ins ( rec_data IN OUT schema.table%ROWTYPE, p_rowid OUT ROWID, p_execution_ts IN schema.table.update_ts%TYPE) RETURN NUMBER </code></pre> <p>Here is what we have done (which does nothing):</p> <pre><code>// 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(); } } </code></pre> <p>I know this is a vry specific question but I'm really 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</p> <p>Many thanks for any help.</p> <p>edit: here is the error message:</p> <p>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</p> <p>Kind regards, Fugu</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload