title: Powershell Connection to Oracle Database
body: <p>I'm having trouble connecting to an Oracle database from Powershell using the <code>Oracle.ManagedDataAccess.dll</code>.</p>
  
  <p>I followed <a href="https://blogs.technet.microsoft.com/heyscriptingguy/2012/12/04/use-oracle-odp-net-and-powershell-to-simplify-data-access/" rel="noreferrer">this</a> tutorial on Technet and ended up with this code:</p>
  
  <pre><code>add-type -path "C:\oracle\product\12.1.0\client_1\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll"
  $username = "XXXX"
  $password = "XXXX"
  $data_source = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXX)(PORT=XXXX))(CONNECT_DATA = (SERVER=dedicated)(SERVICE_NAME=XXXX)))"
  $connection_string = "User Id=$username;Password=$password;Data Source=$data_source"
  
  try{
      $con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connection_string)
  
      $con.Open()
  } catch {
      Write-Error (“Can’t open connection: {0}`n{1}” -f `
          $con.ConnectionString, $_.Exception.ToString())
  } finally{
      if ($con.State -eq ‘Open’) { $con.close() }
  }
  </code></pre>
  
  <p>Unfortunately I'm getting this error:</p>
  
  <pre><code>C:\Users\XXXX\Desktop\oracle_test.ps1 : Can’t open connection: User Id=XXXX;Password=XXXX;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXX)(PORT=XXXX))(CONNECT_DATA = 
  (SERVER=dedicated)(SERVICE_NAME=XXXX)))
  System.Management.Automation.MethodInvocationException: Exception calling "Open" with "0" argument(s): "The type initializer for 'Oracle.ManagedDataAccess.Types.TimeStamp' threw an exception." ---&gt; 
  System.TypeInitializationException: The type initializer for 'Oracle.ManagedDataAccess.Types.TimeStamp' threw an exception. ---&gt; System.Runtime.Serialization.SerializationException: Unable to find assembly 
  'Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=XXXX'.
     at OracleInternal.Common.OracleTimeZone.GetInstance()
     at Oracle.ManagedDataAccess.Types.TimeStamp..cctor()
     --- End of inner exception stack trace ---
     at OracleInternal.ConnectionPool.PoolManager`3.CreateNewPR(Int32 reqCount, Boolean bForPoolPopulation, ConnectionString csWithDiffOrNewPwd, String instanceName)
     at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
     at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
     at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword)
     at Oracle.ManagedDataAccess.Client.OracleConnection.Open()
     at CallSite.Target(Closure , CallSite , Object )
     --- End of inner exception stack trace ---
     at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
     at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
     at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
     at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
      + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
      + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,oracle_test.ps1
  </code></pre>
  
  <ul>
  <li>I have checked, that the connection string is correct (it works for <code>tnsping</code>)</li>
  <li>The Username and password are correct as well</li>
  <li>I replaced anything that shouldn't be disclosed with <strong>XXXX</strong></li>
  <li>The database version is: <strong>Oracle Database 12c Release 12.1.0.1.0</strong></li>
  <li>The error is the same for the 32-Bit and 64-Bit Powershell instances</li>
  <li>Before the first run the <code>Oracle.ManagedDataAccess.dll</code> isn't loaded, after the run it stays loaded until I close that shell</li>
  </ul>
  
  <p>Unfortunately I'm neither an Oracle nor a Powershell Expert (I prefer MySQL and Python), therefore I would really appreciate any ideas and/or insights you might have.</p>