如何在 PowerShell 中运行 SQL Plus 脚本

How to run a SQL Plus script in PowerShell

提问by user3826435

I am trying to log in to the the Oracle DB using PowerShell and run a script called "C:\Users\Administrator\Desktop\oracle\OracleCleanTest.sql", When I execute the PS nothing happens.

我正在尝试使用 PowerShell 登录 Oracle DB 并运行一个名为“C:\Users\Administrator\Desktop\oracle\OracleCleanTest.sql”的脚本，当我执行 PS 时没有任何反应。

Here is what I have.

这是我所拥有的。

$adminLogon = "sys as sysdba/manager@ORCL"
$logon = "sqlplus\sql/manager@ORCL"


$mydata = Invoke-SqlPlus -inputfile       "@C:\Users\Administrator\Desktop\oracle\OracleCleanTest.sql" $logon

I've also tried this.

我也试过这个。

$database = "ORCL";
$user = "sys as sysdba";
$pw = "manager";

sqlplus.exe -d $database -U $user -P $pw -I "@C:\Users\Administrator\Desktop\oracle\OracleCleanTest.sql"

I tried this.

我试过这个。

& 'C:\app\Administrator\product.2.0\client_1\BIN\sqlplus.exe' 'QE-JDBC-1/manager@ORCL sys as sysdba' '@C:\Users\Administrator\Desktop\oracle\OracleCleanTest.sql'

I get the error, "& : The module 'sqlplus' could not be loaded. For more information, run 'Import-Module sqlplus'. At line:5 char:3 + & $mydata Invoke-SqlPlus -inputfile "@C:\Users\Administrator\Desktop\oracle\Orac ... + ~~~~~~~ + CategoryInfo : ObjectNotFound: (sqlplus\sql/manager@ORCL:String) [], ParentContainsErrorRecordException + FullyQualifiedErrorId : CouldNotAutoLoadModule"

我收到错误消息，“&：无法加载模块 'sqlplus'。有关更多信息，请运行 'Import-Module sqlplus'。在 line:5 char:3 + & $mydata Invoke-SqlPlus -inputfile "@C: \Users\Administrator\Desktop\oracle\Orac ... + ~~~~~~~ + CategoryInfo : ObjectNotFound: (sqlplus\sql/manager@ORCL:String) [], ParentContainsErrorRecordException +fullyQualifiedErrorId : couldNotAutoLoadModule"

回答by Patrick Bacon

I use the call operator, &, as Keith Hill has suggested with the question, How to run an EXE file in PowerShell with parameters with spaces and quotes.

我使用调用运算符 , &，正如 Keith Hill 在问题中所建议的，How to run an EXE file in PowerShell with parameters with空格和引号。

& 'path\sqlplus.exe' 'system/password@dbase as sysdba'

I placed the username, password in quotes due to the spaces.

由于空格，我将用户名和密码放在引号中。

To start a script, I add another parameter as follows:

要启动脚本，我添加了另一个参数，如下所示：

 & 'path\sqlplus.exe' 'system/password@dbase as sysdba' '@my_script.sql'

If you are receiving the ORA-12154 error, and you know that other users have established connections (which implies that the database listener is running properly); I would then examine if SQL*Plus can find my tnsname file.

如果您收到 ORA-12154 错误，并且您知道其他用户已建立连接（这意味着数据库侦听器运行正常）；然后我会检查 SQL*Plus 是否可以找到我的 tnsname 文件。

My first task would be to see if I can tnsping as follows in Windows cmd.exe:

我的第一个任务是看看我是否可以在 Windows cmd.exe 中按如下方式进行 tnsping：

tnsping orcl

It will confirm that a connection can (or can not be established).

它将确认可以（或不能建立）连接。

If it cannot, I would check to see if the environment variable, ORACLE_HOME, is set. SQL*Plus uses this to find tnsname.ora file.

如果不能，我将检查是否设置了环境变量 ORACLE_HOME。SQL*Plus 使用它来查找 tnsname.ora 文件。

If it is not set, I would execute this statement in PowerShell (to establish this environment variable):

如果未设置，我将在 PowerShell 中执行此语句（以建立此环境变量）：

[Environment]::SetEnvironmentVariable("ORACLE_HOME", "C:\app\Administrator\product.2.0\client_1" , "User")

Next, I would retry to tnsping (identified above).

接下来，我将重试 tnsping（如上所示）。

Once successful, I would re-try to execute the script running command above.

一旦成功，我会重新尝试执行上面的脚本运行命令。

回答by Vik

I use this:

我用这个：

$cmd = "cmd.exe"
$args = ("/c sqlplus {0}/{1}@{2}:{3}/{4} @{5} {6}" -f $userName, $password, $tnsAlias, $port, $dbInstance, $sqlScript, $outputFileName)
&$cmd $args 

回答by PaSe

In your Windows PowerShell command prompt the code does not require variable setting or anything fancy. Just do this:

在您的 Windows PowerShell 命令提示符中，代码不需要变量设置或任何花哨的东西。只需这样做：

sqlplus ElBankoUser\SupaSecretyPass "@C:\Users\ElBankoUser\Documents\MaFancySckrp.sql"

sqlplus ElBankoUser\SupaSecretyPass "@C:\Users\ElBankoUser\Documents\MaFancySckrp.sql"

回答by Ostati

You can use .NET Oracle libraryDLL, just make sure you have the required DLL file under the libfolder

您可以使用.NET Oracle 库DLL，只需确保文件lib夹下有所需的 DLL 文件

Add-Type -Path "lib\Oracle.ManagedDataAccess.dll"

$query = "select  1 as Col1, 2 as Col2, 3 as Col3 from dual
          union
          select  4 as Col1, 5 as Col2, 6 as Col3 from dual
          union
          select  7 as Col1, 8 as Col2, 9 as Col3 from dual"

$cn   = New-Object Oracle.ManagedDataAccess.Client.OracleConnection -ArgumentList "TNS-ConnectionString-Here"
$cmd  = New-Object Oracle.ManagedDataAccess.Client.OracleCommand    -ArgumentList $query

$cmd.Connection = $cn

try {
    $cn.Open()

    $reader = $cmd.ExecuteReader()

    while ($reader.Read()) {
        $col1 = $reader["Col1"]
        $col2 = $reader["Col2"]
        $col3 = $reader["Col3"]

        Write-Host $col1, $col2, $col3
    }

} catch {
    Write-Error $_.Exception.Message
} finally {
    $cmd.Dispose()
    $cn.Dispose()
}

回答by Chuck

Why not use this?

为什么不使用这个？

sqlplus -s $adminLogin "@C:\Users\Administrator\Desktop\oracle\OracleCleanTest.sql"

sqlplus -s $adminLogin "@C:\Users\Administrator\Desktop\oracle\OracleCleanTest.sql"

-s just suppresses the sqlplus banner.

-s 只是抑制 sqlplus 横幅。