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\11.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'。在行:5 char:3 +&$ mydata Invoke-SqlPlus -inputfile”@C: \ Users \ Administrator \ Desktop \ oracle \ Orac ... + ~~~~~~~ + CategoryInfo:ObjectNotFound:(sqlplus \ sql / manager @ ORCL:String)[],ParentContainsErrorRecordException + FullyQualifiedErrorId:CouldNotAutoLoadModule“
4 个解决方案
#1
10
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提出的问题,如何在PowerShell中使用带空格和引号的参数运行EXE文件。
& '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 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\11.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.
一旦成功,我会重新尝试执行上面的脚本运行命令。
#2
1
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”
#3
0
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
#4
0
You can use .NET Oracle library DLL, just make sure you have the required DLL file under the lib
folder
您可以使用.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()
}
#1
10
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提出的问题,如何在PowerShell中使用带空格和引号的参数运行EXE文件。
& '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 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\11.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.
一旦成功,我会重新尝试执行上面的脚本运行命令。
#2
1
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”
#3
0
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
#4
0
You can use .NET Oracle library DLL, just make sure you have the required DLL file under the lib
folder
您可以使用.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()
}