ODP.NET: Error when load data from Timesten refCursor to DataTable

Question

I want to load data from Timesten DB to datatable, but i get error Here is my code

Script:

create or replace FUNCTION APITT_tbl_request(prefix_db IN VARCHAR2) RETURN SYS_REFCURSOR AS
    res SYS_REFCURSOR;
BEGIN

    OPEN res FOR SELECT * FROM APITT_tbl_request_in;
    RETURN res;
END;

C# code

conn = new OracleConnection(conf.GetAPIDatabaseConnectionString());
conn.Open();
OracleTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
OracleCommand command = new OracleCommand();
DataTable dt = new DataTable();
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);

Error in da.Fill(dt);

{System.InvalidOperationException: Operation is not valid due to the current state of the object. at Oracle.DataAccess.Client.OracleCommand.ExecuteReader

score 0 · Answer 1 · answered Mar 25 '16 at 11:05

You haven't set the connection that the command uses.

With ODP.NET it's possible to have more than one connection open at the same time. If you don't specify which connection a command should use, how is ODP.NET supposed to know?

Either add the line

command.Connection = conn;

or replace the line

OracleCommand command = new OracleCommand();

with

OracleCommand command = conn.CreateCommand();

ODP.NET: Error when load data from Timesten refCursor to DataTable

1 Answers1