long time lurker and first time asking for help :)
We have a bug that keeps bothering us. We are unable to connect to an Oracle Database, from some Web Services with Visual Studio. We're currently using the unmanaged x64 Oracle.DataAccess.dll library (after getting some stranger issues with the Managed Driver).
It's a long and convoluted web service, but the point of failure is in this code:
protected virtual void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, IDataParameter[] commandParameters, out bool mustCloseConnection)
{
if (command == null) throw new ArgumentNullException("command");
if (string.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText");
// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open(); // <-- here
}
The connection object seems fine. Its ConnectionString property is as follows:
ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=_edited_)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=_edited_)));User Id=_edited_;Password=_edited_;"
This connection works perfectly from outside (Oracle SQL Developer access).
As soon as you call this method, an exception is generated, with the following properties:
Message = "Reference to an object not set as an instance of an object" <- Roughly translated by me, foreign IDE :(
StackTrace = " en Oracle.DataAccess.Client.OracleException.get_Number()\r\n en Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bChec...
Any help would be very appreciated. I've looked for other people's questions, but their solutions haven't worked for me. However, if this is a duplicate, please feel free to point me to the right direction. Thanks!! :)
EDIT: This is how we build the Connection object:
using Oracle.DataAccess.Client;
...
using (OracleConnection oraConn = new OracleConnection(AdoFactory.Instance.Helper.GetConnection().ConnectionString))
{
var newUser = **SessionDTO.GetSession**(token, oraConn);
AdoFactory.Instance.Usuario = newUser;
return newUser;
}
...
public static Usuario GetSession(string token, OracleConnection oraConn)
{
Usuario userActive = null;
#region Parameters
var m_paramToken = AdoFactory.Instance.Helper.GetParameter("p_TOKEN", token);
#endregion
var m_parameters = new[] { m_paramToken, AdoFactory.Instance.Helper.GetCursorForProcedure() };
using (IDataReader reader = AdoFactory.Instance.Helper.**ExecuteReader**(oraConn,
CommandType.StoredProcedure,"nameofprocedurewhichworksnicelyfromSQL",
m_parameters))
{
while (reader.Read())
{
...
public IDataReader ExecuteReader(IDbConnection p_connection, CommandType p_commandType, string p_commandText, params IDataParameter[] p_commandParameters)
{
// Pass through the call to the private overload using a null transaction value and an externally owned connection
return ExecuteReader(p_connection, null, p_commandType, p_commandText, p_commandParameters, AdoConnectionOwnership.External);
}
...
private IDataReader ExecuteReader(IDbConnection p_connection, IDbTransaction p_transaction, CommandType p_commandType, string p_commandText, IDataParameter[] p_commandParameters, AdoConnectionOwnership p_connectionOwnership)
{
if (p_connection == null) throw new ArgumentNullException("p_connection");
var mustCloseConnection = false;
// Create a command and prepare it for execution
var cmd = p_connection.CreateCommand();
try
{
**PrepareCommand**(cmd, p_connection, p_transaction, p_commandType, p_commandText, p_commandParameters, out mustCloseConnection);
// Create a reader
// Call ExecuteReader with the appropriate CommandBehavior
var dataReader = p_connectionOwnership == AdoConnectionOwnership.External ? cmd.ExecuteReader() : cmd.ExecuteReader(CommandBehavior.CloseConnection);
ClearCommand(cmd);
return dataReader;
}
catch {
if (mustCloseConnection) p_connection.Close();
throw;
}
}
ConnectionString: "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=asdf)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=asdf)));Enlist=true;User Id=asdf;Password=asdf;"
EDIT2: As suggested, I tried to open a plain OracleConnection and... it fails. The test:
// Simple test para ver si funciona crear una conexión normal de Oracle
try
{
string oradb = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=asdf)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=asdf)));Enlist=true;User Id=asdf;Password=asdf;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open(); // <-- fails here
OracleCommand cmd = new OracleCommand();
......
conn.Dispose();
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
////////////////////////////////////////
The conn object:
conn {Oracle.DataAccess.Client.OracleConnection} Oracle.DataAccess.Client.OracleConnection
CanRaiseEvents true bool
CanRaiseEventsInternal true bool
ConnectionCloseEvent null Oracle.DataAccess.Client.OracleConnection.OracleConnectionCloseEventHandler
ConnectionString "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=--EDITED--)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=--EDITED--)));Enlist=true;User Id=--EDITED--;Password=--EDITED--;" string
ConnectionTimeout 15 int
ConnectionType Undefined Oracle.DataAccess.Client.OracleConnectionType
Container null System.ComponentModel.IContainer
DRCPConnectionClass null string
DRCPPurity Pooled Oracle.DataAccess.Client.OracleConnection.OracleDRCPPurity
DataSource "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=--EDITED--)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=--EDITED--)))" string
Database "" string
DatabaseDomainName "" string
DatabaseEditionName null string
DatabaseName "" string
+ DbProviderFactory {Oracle.DataAccess.Client.OracleClientFactory} System.Data.Common.DbProviderFactory {Oracle.DataAccess.Client.OracleClientFactory}
DesignMode false bool
+ Events {System.ComponentModel.EventHandlerList} System.ComponentModel.EventHandlerList
ForceNewConnection false bool
HostName "" string
Identity null object
InstanceName "" string
IsDBVer10gR2OrHigher false bool
IsDBVer11gR1OrHigher false bool
IsDBVer11gR2OrHigher false bool
IsDBVer12cR1OrHigher false bool
IsDBVer_11_1_0_7_OrHigher false bool
LogicalTransactionId null byte[]
+ OracleLogicalTransaction {Oracle.DataAccess.Client.OracleLogicalTransaction} Oracle.DataAccess.Client.OracleLogicalTransaction
PDBName "" string
+ ProviderFactory {Oracle.DataAccess.Client.OracleClientFactory} System.Data.Common.DbProviderFactory {Oracle.DataAccess.Client.OracleClientFactory}
+ ServerVersion 'conn.ServerVersion' threw an exception of type 'System.InvalidOperationException' string {System.InvalidOperationException}
ServiceName "" string
Site null System.ComponentModel.ISite
State Closed System.Data.ConnectionState
StatementCacheSize 0 int
SwitchedConnection false bool
+ TxnHndAllocated 'conn.TxnHndAllocated' threw an exception of type 'System.NullReferenceException' int {System.NullReferenceException}
__identity null object
_stateChangeEventHandler null System.Data.StateChangeEventHandler
_supressStateChangeForReconnection false bool
+ events {System.ComponentModel.EventHandlerList} System.ComponentModel.EventHandlerList
+ listofWeakReferenceObj Count = 0 System.Collections.Generic.List<System.WeakReference>
lockOnWeakReferenceObjList {object} object
+ m_DataReaderList Count = 0 System.Collections.ArrayList
m_PatchSetVersion 0 int
m_appEdition null string
m_bCloseInProgress false bool
m_bConnforTxnStatus false bool
m_bDrcpPurityNew 0 byte
m_bErrorDoingErrorTranslation false bool
m_bGetOutcome false bool
m_bLocalTxnStartedForSysTxn false bool
m_bPrelimAuthSession false bool
m_bSTFEnabled false bool
m_bStartupShutdown false bool
m_conOpenEventHandler null Oracle.DataAccess.Client.OracleConnectionOpenEventHandler
m_conSignature 0 int
+ m_conStrVals {object[33]} object[]
m_conStrValsFromPool false bool
m_conString "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=--EDITED--)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=--EDITED--)));Enlist=true;User Id=--EDITED--;Password=--EDITED--;" string
m_conTimeout 15 int
m_contextConnection false bool
m_criteriaCtx null OracleInternal.ConnectionPool.CriteriaCtx
+ m_cs {datasrc=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=--EDITED--)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=--EDITED--)));enlist=True;lifetime=0;maxsize=100;minsize=1;incsize=5;decsize=5;timeout=15;dbapriv=None;validcon=False;pooling=True;stmtcache=0;stmtcachepurge=0;metapool=True;selftuning=True;pspe=Promotable;ha=1;rlb=1;ac=1;userid=--EDITED--} OracleInternal.ConnectionPool.ConnectionString
m_dataSource "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=--EDITED--)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=--EDITED--)))" string
m_databaseDomainName "" string
m_databaseName "" string
m_disposed false bool
m_drcpConnectionClass null string
m_drcpPurity Pooled Oracle.DataAccess.Client.OracleConnection.OracleDRCPPurity
m_enlist 0 int
m_extProcEnv null Oracle.DataAccess.Client.OracleConnection.ExtProcEnv
m_failoverEventHandler null Oracle.DataAccess.Client.OracleFailoverEventHandler
m_hostName "" string
m_id null string
m_implId 0 int
+ m_implicitRefCursorCtxList Count = 0 System.Collections.ArrayList
m_infoMessageEventHandler null Oracle.DataAccess.Client.OracleInfoMessageEventHandler
m_instanceName "" string
m_internalConStr "datasrc=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=--EDITED--)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=--EDITED--)));enlist=1;lifetime=0;maxsize=100;minsize=1;incsize=5;decsize=1;timeout=15;dbapriv=;validcon=0;stmtcache=0;stmtcachepurge=0;pooling=1;metapool=1;selftuning=1;pspe=promotable;ha=1;rlb=1;ac=1;userid=--EDITED--" string
m_internalUse false bool
m_isDb10gR2OrHigher false bool
m_isPuritySet false bool
+ m_lastEnlistedTransaction null System.Transactions.Transaction
m_logicalTransaction null Oracle.DataAccess.Client.OracleLogicalTransaction
m_majorVersion 0 int
+ m_metaDataCollectionDS null System.Data.DataSet
m_minorVersion 0 int
m_newPassword null string
m_openWithNewPwd false bool
+ m_opoConCtx {Oracle.DataAccess.Client.OpoConCtx} OracleInternal.ConnectionPool.OracleConnectionImpl {Oracle.DataAccess.Client.OpoConCtx}
+ m_oraGlobClone null Oracle.DataAccess.Client.OracleGlobalization
m_oraTransaction null Oracle.DataAccess.Client.OracleTransaction
+ m_oracleConnectionImpl null OracleInternal.ConnectionPool.OracleConnectionImpl
m_orclPermission null Oracle.DataAccess.Client.OraclePermission
m_password "--EDITED--" string
m_pdbName null string
m_persist false bool
m_promoteTxnMgr null OracleInternal.MTS.PSPETxnManagerBase
m_proxyPassword "" string
m_pwdLessString "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=--EDITED--)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=--EDITED--)));Enlist=true;User Id=--EDITED--;" string
m_pwdOSLessString "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=--EDITED--)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=--EDITED--)));Enlist=true;User Id=--EDITED--;" string
m_pwdValidated false bool
+ m_securePassword null System.Security.SecureString
+ m_secureProxyPassword null System.Security.SecureString
m_serverVersion "" string
m_serviceName "" string
m_sessionId 0 long
m_state Closed System.Data.ConnectionState
m_stateChangeEventHandler null System.Data.StateChangeEventHandler
m_stmtCacheSize 0 int
m_syncTxnComplete {object} object
m_tmpConString null string
m_tuningLock {object} object
m_userProvidedConEditionName "" string
m_userProvidedPdbName "" string
m_userProvidedServiceName "" string
m_validConString true bool
+ pmCS null OracleInternal.ConnectionPool.ConnectionString
site null System.ComponentModel.ISite
+ Static members
And the exception object:
- ex {"Referencia a objeto no establecida como instancia de un objeto."} System.Exception {System.NullReferenceException}
+ Data {System.Collections.ListDictionaryInternal} System.Collections.IDictionary {System.Collections.ListDictionaryInternal}
HResult -2147467261 int
HelpLink null string
+ IPForWatsonBuckets 0x00007ffd87f6667a System.UIntPtr
+ InnerException null System.Exception
IsTransient false bool
Message "Referencia a objeto no establecida como instancia de un objeto." string
RemoteStackTrace null string
Source "Oracle.DataAccess" string
StackTrace " en Oracle.DataAccess.Client.OracleException.get_Number()\r\n en Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable, OracleLogicalTransaction m_OracleLogicalTransaction)\r\n en Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src, OracleLogicalTransaction m_oracleLogicalTransaction)\r\n en Oracle.DataAccess.Client.OracleConnectionOCP.Open(OracleConnection con)\r\n en Oracle.DataAccess.Client.OracleConnection.Open()\r\n en UNED.FP.BL.OAuth.OAuth.ValidateToken(String token) en --EDITED--" string
+ TargetSite {Int32 get_Number()} System.Reflection.MethodBase {System.Reflection.RuntimeMethodInfo}
WatsonBuckets null object
_HResult -2147467261 int
_className null string
+ _data {System.Collections.ListDictionaryInternal} System.Collections.IDictionary {System.Collections.ListDictionaryInternal}
_dynamicMethods null object
+ _exceptionMethod {Int32 get_Number()} System.Reflection.MethodBase {System.Reflection.RuntimeMethodInfo}
_exceptionMethodString null string
_helpURL null string
+ _innerException null System.Exception
+ _ipForWatsonBuckets 0x00007ffd87f6667a System.UIntPtr
_message "Referencia a objeto no establecida como instancia de un objeto." string
_remoteStackIndex 0 int
_remoteStackTraceString null string
+ _safeSerializationManager {System.Runtime.Serialization.SafeSerializationManager} System.Runtime.Serialization.SafeSerializationManager
_source "Oracle.DataAccess" string
+ _stackTrace {sbyte[384]} object {sbyte[]}
_stackTraceString null string
_watsonBuckets null object
_xcode -532462766 int
+ _xptrs 0x0000000000000000 System.IntPtr
+ Static members
