windows - 面临用于在 Oracle 数据库上调度 SQL 脚本的 PowerShell 脚本问题

Question

我是 PowerShell 的新手，是一名初级程序员。我一直在尝试调整 PowerShell 脚本以在 Oracle 数据库上安排 SQL 查询。但是，当我在 PowerShell ISE 上运行以下命令时，出现错误:

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()

下面是错误消息，指出加载 Oracle.DataAccess.dll 时出现问题(即使我确实在该目录中看到该文件):

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

Answer 1

如果您愿意，这里有一个更简单的替代方案。这需要在计算机上安装 SQL*Plus。在本例中，我从 Oracle 数据库中检索服务器名称列表，并将该列表放入 powershell 变量 $dbServers 中。如果符合您的开发要求，这可能会有用:

$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