Executing sql file with sqlplus in windows 10 powershell

Question

I have created a .bat file to export csv file regulary through windows task scheduling which works fine. But not working when I switch to Powershell. It returns (both in ISE and right click .ps1 "Run with Powershell") with:

SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 2 14:05:52 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR: ORA-12154: TNS:could not resolve the connect identifier specified

So.I'm not sure what I'm doing wrong. The variable input are dummies.

In my .bat contains:

SET NLS_LANG=.AL32UTF8
SET hostIp="123.123.1.12"
SET username="user1"
SET password="pass1"
SET port="1521"
SET service="myDBname"
SET sqlPath="C:\My script\TEST_EXPORT.sql"
sqlplus %username%/%password%@%hostIp%:%port%/%service% @"%sqlPath%"

In my .ps1 contains:

cls
$hostIp="123.123.1.12"
$username="user1"
$password="pass1"
$port="1521"
$service="myDBname"
$sqlPath="C:\My script\TEST_EXPORT.sql"
echo "$username/$password@$hostIp`:$port/$service @$sqlPath"
sqlplus "$username/$password@$hostIp`:$port/$service @$sqlPath"

The Powershell version is missing double quotes around $sqlPath, which the Cmd version has. — vonPryz
In addition to vonPryz's good find, $database in the sqlplus line is not defined anywhere, at least in the code you've given. Line above it shows $service instead of $database — Daniel
@Daniel Thx. for pointing out. I missed typed that one. Editted with $service. Still getting the same error. — Golf
@vonPryz how should I double quotes areounf the $sqlpath? double quotes within double quotes? — Golf
You're welcome. Regarding the double quotes, you can double them up or use backtick to escape them. — Daniel

vonPryz vonPryz · Accepted Answer · 2021-05-02T10:07:38

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/[email protected]: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"

Executing sql file with sqlplus in windows 10 powershell

1 Answers