How to run a SQL Plus script in PowerShell

Question

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.

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"

Answer 1

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'

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.

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

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.

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

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

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

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

Answer 2

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