How to insert list as parameter from powershell to SqlPlus

Question

I have a query which is like this in a file :

script.sql

select * 
from test_table
where name in (&1)

This is an example of query i'm trying to make

and i execute it with a power shell script which is like this :

$names = '''Name1'', ''Name2'''
$params = '"' + $names + '"'
sqlplus -silent $username/$password@$tnsalias "@script.sql" $params

Note that $names has a variable list of names

But then when the script is executed, the parameter get substitute like this :

former : where name in (&1)
new    : where name in (Name1)

And of course the SQL throws an error.

Why it substitutes the parameter like this ?

How can i achieve what i want to do, so the first parameter is a list of strings which will be used in the where name in(&1) clause. My goal is that the sql will be where name in ('Name1', 'Name2')

The SQL is executed on Oracle 11 if that can help.

Sorry, in my script the quoting is correct. Here i just put an exemple to simplify what i want to do. I will edit my question. However, you mean that i don't need the double quotes to wrap the names list ? — LuftWaffle
– LuftWaffle, Commented Nov 14, 2019 at 14:12
If you are calling sqlplus in PowerShell, double quotes that are not literal tell PowerShell to expand everything inside and the resulting expansion will be a string. Single quotes surrounding text tells PowerShell to treat everything inside literally (except escaped characters) as a string. In your case, PowerShell interprets $names to be a string. Does sqlplus require literal double quotes to be passed to it? If you did something like $names = '"''Name1'',''Name2''"', then the string would include the surrounding double quotes. You also have a space between the names. Does sqlplus care? — AdminOfThings
– AdminOfThings, Commented Nov 14, 2019 at 14:29
As far as i know, sqlplus parameters require double quotes to include white spaces and others (espcially if you have multiple parameters) so i guess they are required here as i want the list to be one single parameter. I will update my question to include the goal that i want. in SQL, the space should not be a problem but for sqlplus i don't know (i tested and it didn't change the result) — LuftWaffle
– LuftWaffle, Commented Nov 14, 2019 at 14:40
Sorry it's another copy, error. In my actual script it is &1. I will again fix my question — LuftWaffle
– LuftWaffle, Commented Nov 14, 2019 at 15:35

Dmitry Demin · Accepted Answer · 2019-11-15 08:55:57Z

1

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

edited Nov 15, 2019 at 8:55

answered Nov 15, 2019 at 8:49

Dmitry Demin

2,1232 gold badges19 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LuftWaffle Over a year ago

So the solution is by using double '&' ?

Dmitry Demin Over a year ago

This magic is by extra double quotes. $names = "'''Name1'',''Name2'', ''X'''"

Collectives™ on Stack Overflow

How to insert list as parameter from powershell to SqlPlus

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related