ODP.NET: How can i get data from dynamic sql to dataset

Question

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?

I think the parameter should have `ParameterDirection.Output` type value — Ilia Maskov, Mar 21 '16 at 07:44
I change to ParameterDirection.Output, but get same error :( — Nguyen Anh Duc, Mar 21 '16 at 07:49
It looks like the problem is in your first query `select id into idNo from tbl_request_in...`. Could you please attach the table `DDL` ? — Ilia Maskov, Mar 21 '16 at 10:14
You can use any select, ex: `select * from APITT_tbl_request_in` And i return Ref Cursor but get the same error :( — Nguyen Anh Duc, Mar 25 '16 at 01:58

Wernfried Domscheit · Answer 1 · 2016-03-23T09:24:02.977

0

An anonymous PL/SQL block cannot return any value. Write a function like this:

CREATE OR REPLACE FUNCTION APITT_tbl_request(prefix_db IN VARCHAR2) RETURN SYS_REFCURSOR AS
    idNo NUMBER;
    res SYS_REFCURSOR;
BEGIN
    EXECUTE IMMEDIATE 'SELECT ID ' 
        ||'FROM '|| prefix_db || 'tbl_request_in '
        ||'WHERE UPPER(status)=''PENDING'' AND ROWNUM <= 1 ORDER BY priority, ID FOR UPDATE' 
    INTO idNo;

    EXECUTE IMMEDIATE 'UPDATE '|| prefix_db || 'tbl_request_in SET status =''Processing'', begin_time= :bt, response_node=''10.9.70.47'' ' 
        ||'WHERE UPPER(status) <> ''PROCESS'' AND UPPER(status) <> ''PROCESSING'' AND UPPER(status) <> ''OK'' AND UPPER(status)<>''ERROR'' AND ID= :id'
    USING SYSDATE,  idNo

    OPEN res FOR SELECT * FROM APITT_tbl_request_in WHERE ID=idNo;
    RETURN res;
END;

and call this function like

DataTable dt = new DataTable();
OracleCommand command = new OracleCommand(@"BEGIN :RETURNCURSOR := APITT_tbl_request(:prefix_db); END;");
command.CommandType = CommandType.Text;
command.Parameters.Add("RETURNCURSOR", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
command.Parameters.Add("prefix_db", OracleDbType.Varchar2, ParameterDirection.Input).Value = prefix_db;
OracleDataAdapter da = new OracleDataAdapter(command);
da.Fill(dt);

If you cannot create the function in the database, try to replace string APITT_tbl_request(:prefix_db); with the function body but I don't know if this works.

edited Mar 23 '16 at 09:24

answered Mar 21 '16 at 09:42

Wernfried Domscheit

54,457
9
76
110

I run by your code, it show error {System.InvalidOperationException: Operation is not valid due to the current state of the object. at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) 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) at AsyncQueue.ProcessQueue.Process() in ProcessQueue.cs:line 164} – Nguyen Anh Duc Mar 22 '16 at 08:33
Did you open the Connection? – Wernfried Domscheit Mar 22 '16 at 11:38
Yes, the connection was opened – Nguyen Anh Duc Mar 23 '16 at 01:07
Sorry, i lack excuteNonQuery, but when add this code. it's so different error :( {System.InvalidOperationException: Operation is not valid due to the current state of the object. at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() } ================================================ Below is mycode https://s27.postimg.org/5v2om6oer/Untitled.png – Nguyen Anh Duc Mar 23 '16 at 08:59
See my updated answer. I don't know if a `RefCursor` can fill a `DataSet`, usually a `DataSet` contains several `DataTable`, I assume you have to add them manually to your `DataSet` – Wernfried Domscheit Mar 23 '16 at 09:26
I change to DataTable but i get the same error :( `{System.InvalidOperationException: Operation is not valid due to the current state of the object. at Oracle.DataAccess.Client.OracleCommand.ExecuteReader` – Nguyen Anh Duc Mar 25 '16 at 01:56
There is no "ExecuteReader()" neither in the question nor in my answer! Check your code and open a new question. – Wernfried Domscheit Mar 25 '16 at 05:24

ODP.NET: How can i get data from dynamic sql to dataset

1 Answers1