如何在PowerShell中运行SQL Plus脚本

问题描述

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

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.

这就是我所拥有的.

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


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

我也尝试过这个.

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

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

我尝试过这个.

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

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

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"

推荐答案

我使用呼叫运算符&，正如Keith Hill提出的问题.

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.

& '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'

如果您收到ORA-12154错误，并且您知道其他用户有建立的连接(这意味着数据库侦听器正在运行适当地);然后，我将检查SQL * Plus是否可以找到我的tnsname文件.

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

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

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

tnsping orcl

它将确认连接可以(或不能建立).

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

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

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.

如果未设置，我将在PowerShell中执行此语句(以建立此环境变量):

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

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

接下来，我将重试tnsping(如上所述).

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

成功后，我将重试执行上面的脚本运行命令.

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

这篇关于如何在PowerShell中运行SQL Plus脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！