My application using TimesTen DB to store data. Below is my code to update data and return a updated row.
conn = new OracleConnection("My Connection");
conn.Open();
OracleTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
OracleCommand command = new OracleCommand();
command = new OracleCommand(@"DECLARE idNo NUMBER;
BEGIN
select id into idNo from " + prefix_db + @"tbl_request_in where upper(status)='PENDING' and ROWNUM <= 1 order by priority, id FOR update;
update " + prefix_db + @"tbl_request_in set status ='Processing',begin_time= SYSDATE(),response_node='10.9.70.47'
where upper(status) <> 'PROCESS' and upper(status) <> 'PROCESSING' and upper(status) <> 'OK' and upper(status)<>'ERROR'
and id=idNo;
OPEN :RETURNCURSOR for select * from APITT_tbl_request_in where id=idNo;
END;", conn);
command.Transaction = tran;
command.BindByName = true;
OracleParameter outNumPrm = command.Parameters.Add("RETURNCURSOR", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.ReturnValue);
// create a data adapter to use with the data set
OracleDataAdapter da = new OracleDataAdapter(command);
// create the data set
DataSet ds = new DataSet();
// fill the data set
da.Fill(ds);
And i give error
{Oracle.DataAccess.Client.OracleException ORA-01722: invalid number
at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src, Boolean bCheck) at Oracle.DataAccess.Client.OracleDataReader.Read() at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
How can i resolve it?
ParameterDirection.Output
type value – Ilia Maskovselect id into idNo from tbl_request_in...
. Could you please attach the tableDDL
? – Ilia Maskov