CODEWITHSUNDEEP
sqloracle-databasepowershellsqlplus
LuftWaffle
LuftWaffle

Reputation: 306

How to insert list as parameter from powershell to SqlPlus

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.

Upvotes: 1

Views: 1712

Answers (1)

Dmitry Demin
Dmitry Demin

Reputation: 2113

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

Upvotes: 1

Related Questions