Question:
I need to be able to connect to an Windows 7 based Oracle server (32 bit, Oracle XE) which is on my network. The machine I need to connect from is running Windows 7 64 bit, with Powershell installed on both machines.
I have installed the Oracle 32 bit client on my 64 bit machine and have SQL Developer installed on both machines. I want to create a script that connects the the Oracle database and runs a simple SELECT query. I can’t get it to connect though.
I have tried using ODAC (I think I have to install Visual Studio to use this as the install fails). I hear that OleBD might be a lot easier. I would like to do it with TNS is possible. Can anyone offer me any guidance here? I have a book on Powershell and Oracle and I am still confused, I can’t get past the first stage.
Any help would be greatly appreciated.
Answer:
Here is a small example of what I was using in 2015.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
# Ora002.ps1 # Need installation of ODAC1120320Xcopy_x64.zip # The 32 bit version also exists # Load the good assembly Add-Type -Path "C:\oracle\odp.net\bin\4\Oracle.DataAccess.dll" # Connexion string $compConStr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.213.5.123)(PORT=1609)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=COMPEIERE)));User Id=TheLogin;Password=ThePassword;" # Connexion $oraConn= New-Object Oracle.DataAccess.Client.OracleConnection($compConStr) $oraConn.Open() # Requête SQL $sql1 = @" SELECT XX_MYSESSION_ID FROM XX_SILOGIXWSLOG WHERE xx_name='customer_log' AND xx_param_4 IS NOT NULL "@ $command1 = New-Object Oracle.DataAccess.Client.OracleCommand($sql1,$oraConn) # Execution $reader1=$command1.ExecuteReader() $n = 0 while ($reader1.read()) { $reader1["XX_MYSESSION_ID"] } # Fermeture de la conexion $reader1.Close() $oraConn.Close() Write-Output $retObj |
—– Edited in fall 2017 —–
For a while now Oracle edited a full managed DLL for .NET which is available through Nugets :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
# Download the package if it's not on the disk $version = '12.2.1100' try { if (! $(Test-Path ".\NugetPackages\Oracle.ManagedDataAccess.$version\lib\net40\Oracle.ManagedDataAccess.dll")) { $ManagedDataAccess = Install-Package Oracle.ManagedDataAccess -Destination ".\NugetPackages" -Force -Source 'https://www.nuget.org/api/v2' -ProviderName NuGet -RequiredVersion $version -ErrorAction SilentlyContinue } Add-Type -Path ".\NugetPackages\Oracle.ManagedDataAccess.$version\lib\net40\Oracle.ManagedDataAccess.dll" } catch [System.Management.Automation.ParameterBindingException] { $global:OracleError = New-Object PSCustomObject -Property @{"StackTrace"=$_.ScriptStackTrace;"Detail" = "Ligne $($_.InvocationInfo.ScriptLineNumber) : $($_.exception.message)";"TimeStamp"=([datetime]::Now)} $log = $null } # Connexion $oraConn= New-Object Oracle.ManagedDataAccess.Client.OracleConnection (($compConStr) $oraConn.Open() # Requête SQL $sql1 = @" SELECT XX_MYSESSION_ID FROM XX_SILOGIXWSLOG WHERE xx_name='customer_log' AND xx_param_4 IS NOT NULL "@ $command1 = New-Object Oracle.ManagedDataAccess.Client.OracleCommand($sql1,$oraConn) # Execution $reader1=$command1.ExecuteReader() $n = 0 while ($reader1.read()) { $reader1["XX_MYSESSION_ID"] } # Fermeture de la conexion $reader1.Close() $oraConn.Close() Write-Output $retObj |