Facing issue with a PowerShell script for scheduling SQL scripts on Oracle database

user_12218680 0
Viewed 144

I am new to PowerShell and am a beginner level programmer. I have been trying to tweak a PowerShell script to schedule SQL queries on Oracle Database. However, while I run the below on the PowerShell ISE, I get an error:

Add-Type -Path C:\Oracle\Oracle_Home\product\11.2.0\client_1\odp.net\bin\2.x\Oracle.DataAccess.dll
$username = "username"
$password = "password"
$datasource = "HOST:PORT/Instance"
$connectionString = "User Id=$username;Password=$password;Data Source=$datasource"
$query = "SELECT FULL_NAME FROM PER_PEOPLE_X WHERE EMPLOYEE_NUMBER = 'AB123'"
$connection = New-Object Oracle.DataAccess.Client.OracleConnection("$connectionString")
$connection.open()
$command = New-Object Oracle.DataAccess.Client.OracleCommand
$command.Connection = $connection
$command.CommandText = $query
$ds = New-Object system.Data.DataSet
$da = New-Object Oracle.DataAccess.Client.OracleDataAdapter($command)
[void]$da.fill($ds)
return $ds.Tables[0] | SELECT FULL_NAME  Export-CSV "C:\test.csv" -NoTypeInformation
$connection.Close()

Below is the error message that states there is an issue loading Oracle.DataAccess.dll (even though I do see the file on that directory):

Add-Type: Could not load file or assembly 
'file:///C:\Oracle\Oracle_Home\product\11.2.0\client_1\odp.net\bin\2.x\Oracle.DataAccess.dll' or one of its dependencies. An 
attempt was made to load a program with an incorrect format.
At line:1 char:1
+ Add-Type -Path C:\Oracle\Oracle_Home\product\11.2.0\client_1\odp.net\ ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Add-Type], BadImageFormatException
    + FullyQualifiedErrorId : System.BadImageFormatException,Microsoft.PowerShell.Commands.AddTypeCommand
powershell oracle windows
1 Answers

Here is a simpler alternative if you would like it. This requires that SQL*Plus be installed on the computer. In this case I am retrieving a list of server names from an Oracle database and putting the list into the powershell variable $dbServers. This might be useful if it fits within your development requirements:

$username = "username"
$password = "password"
$instance = "dbname.world"

$sqlcmd="set serveroutput off
   set linesize 160
   set pagesize 0
   set heading off
   set feedback off
   select server_name from tnsnames.tns_servers
      where dbtype in ('ORA', 'SS') and version is not null
       order by server_name;
   exit
   "

   $dbServers = $sqlcmd | sqlplus -s $username/$password@$instance
user_28004020
Related