Error "infer from sequence" returning table from Oracle function in C # where function uses dblink for SQL Server
In C # code, I am trying to load a data table from an Oracle function. The function has a return type SYS_REFCURSOR
. This is my code trying to populate DataTable dt
with a function:
using (var connection = new OracleConnection(connstring))
{
connection.Open();
using (var command = new OracleCommand())
{
command.Connection = connection;
command.CommandText = "FNC_AXA_APPTS";
command.CommandType = CommandType.StoredProcedure;
OracleParameter retVal = new OracleParameter("PRS", OracleDbType.RefCursor);
retVal.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(retVal);
command.Parameters.Add(new OracleParameter("EG_PARAM", OracleDbType.Varchar2, 50)).Value = paramValue;
command.ExecuteNonQuery();
using (OracleDataReader reader = ((OracleRefCursor)command.Parameters["PRS"].Value).GetDataReader())
{
dt.Load(reader);
}
}
}
In some cases, the Oracle function uses dblink for SQL databases. In these cases, I get the following exceptions ...
ORA-01002: fetch out of sequence
ORA-02063: preceding line from GATE_LINK
... where GATE_LINK
is dblink. So far, my research confirms that the problem must be dblink related.
We are using Oracle Data Provider for .NET - is it possible this does not support dblink for SQL Server? Or, if so, what can I tweak at the end of the SQL to solve this problem?
I should mention that we are connecting to Oracle DB via VPN and the connection string is using the following format:
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4};"
Thanks in advance...
source to share
After spending more than one day researching this, I was directed to an answer 10 minutes after my question was posted. Typical!
The answer was found here - https://community.oracle.com/thread/659625 - and all it takes is to associate the calling code with the transaction. The working code looks like this:
using (var connection = new OracleConnection(connstring))
{
connection.Open();
using (var command = connection.CreateCommand())
{
// Start a local transaction
using (var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
// Assign transaction object for a pending local transaction
command.Transaction = transaction;
command.CommandText = "FNC_AXA_APPTS";
command.CommandType = CommandType.StoredProcedure;
OracleParameter retVal = new OracleParameter("PRS", OracleDbType.RefCursor);
retVal.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(retVal);
command.Parameters.Add(new OracleParameter("EG_PARAM", OracleDbType.Varchar2, 50)).Value = paramValue;
command.ExecuteNonQuery();
using (OracleDataReader reader = ((OracleRefCursor)command.Parameters["PRS"].Value).GetDataReader())
{
dt.Load(reader);
}
}
}
}
My limited understanding of the solution is that without this, the transaction is performed on the SQL Server side, which causes the returned cursor to fail in its iteration after the .NET code is passed. If anyone has a better explanation please add to this question.
source to share