How to Run a SQL Plus Script in Powershell

Execute SQL*Plus from PowerShell

I found the solution myself

I use cmd.exe /c and alex.ps1 is now

$cmd = "cmd.exe";
$args = "/c sqlplus user/password@server/sid @C:\temp\SQL\alex.sql";
&$cmd $args;

Hope this help.

Trying to execute sql script using sqlplus from powershell commandline

I believe you need to add the @ sign before the path:

sqlplus username/password@tnsnamesalias @'path to my sql file.sql'

How to run .sql file using sqlplus from powershell

This works for me:

PS M:\> sqlplus scott/tiger@kc11g "@m:\dbscript.sql"

SQL*Plus: Release 11.2.0.1.0 Production on ╚et Kol 16 07:59:25 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Executing sql file with sqlplus in windows 10 powershell

Try using composite formatting to build the parameter string. The upside is that one can build the string and not to worry about quotation issues. Note that there is no need to escape the colon `: in the string, as it is not interpreted as scope operator.

# A variable that contains double quote
$quote = '"'

$("{0}/{1}@{2}:{3}/{4} @{5}{6}{5}" -f $username, $password, $hostIp, $port, $service, $quote, $sqlPath,$quote)
user1/pass1@123.123.1.12:1521/myDBname @"C:\My script\TEST_EXPORT.sql"

Another an alternative for building complex strings is string interpolation. Here are three versions that contain different techniques to include double-quotes. The same works in composite fomatting too.

# Double-doublequote version. I'd avoid this, as multiple double quotes are hard to read
"${username}/${password}@{$hostIp}:${port}/${service} @""${sqlPath}"""
user1/pass1@{123.123.1.12}:1521/myDBname @"C:\My script\TEST_EXPORT.sql"
# Backtick-escape version
"${username}/${password}@{$hostIp}:${port}/${service} @`"${sqlPath}`""
user1/pass1@{123.123.1.12}:1521/myDBname @"C:\My script\TEST_EXPORT.sql"
# Quote in a variable version
"${username}/${password}@{$hostIp}:${port}/${service} @${quote}${sqlPath}${quote}"
user1/pass1@{123.123.1.12}:1521/myDBname @"C:\My script\TEST_EXPORT.sql"

How to insert list as parameter from powershell to SqlPlus

Example for powershell

$username_dba = "system"
$password_dba = "manager"
$tnsalias_db = "es"
$names = "'''Name1'',''Name2'', ''X'''"
$params = '"' + $names + '"'
$sqlfile = "@sqltest.sql"

Write-Host  $names
Write-Host  $params

C:\oracle\instantclient_11_2\sqlplus  $username_dba/$password_dba@$tnsalias_db  $sqlfile  $params

Output powershell:

C:\upwork\stackoverflow\param_sql>powershell .\sql_param.ps1
'''Name1'',''Name2'', ''X'''
"'''Name1'',''Name2'', ''X'''"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 15 11:46:49 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

old   1: select sysdate from dual where DUMMY in (&&1 )
new   1: select sysdate from dual where DUMMY in ('Name1','Name2', 'X' )

SYSDATE
---------
15-NOV-19

Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Example for bat file.

@echo off
set user_name=system
set password=manageresmd
set net_service_name=esmd
set param1='''test1'',''test22'',''X'''

C:\oracle\instantclient_11_2\sqlplus.exe  %user_name%/%password%@%net_service_name%  @sqltest.sql %param1%
pause

Output bat file:

C:\upwork\stackoverflow\param_sql>sqltest1.bat

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 15 11:50:58 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

old   1: select sysdate from dual where DUMMY in (&&1 )
new   1: select sysdate from dual where DUMMY in ('test1','test22','X' )

SYSDATE
---------
15-NOV-19

Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

How to use a sql plus variable when calling a script?

You could use a substitution variable:

define a=22

host powershell.exe echo &a

If you already have the bind variable defined and populated by a process you don't want to change, you can set the substitution variable value using a dummy query and column ... new_value syntax.

Make SQL*Plus script quit

You can pipe the word exit into the SQL*Plus command line. For example, if demo.sql consists of this:

prompt This is my demo script

Then you can call it like this:

echo exit | sqlplus william/w @demo.sql

Output:

Y:\SQL>echo exit | sqlplus william/w @demo.sql

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 13 10:47:13 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sun Jan 13 2019 10:46:03 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

This is my demo script
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Y:\SQL>

Or using the -s (silent) option to suppress banners etc:

Y:\SQL>echo exit | sqlplus -s william/w @demo.sql

This is my demo script

Y:\SQL>

Tested with SQL*Plus 12.2 on Windows 10.

(From https://serverfault.com/q/87035/352734 - turns out it works in both Windows and Linux.)

You could also look at options for avoiding handling usernames and passwords e.g. here: https://dba.stackexchange.com/a/109170/103604

---

Related Topics