如何将列表作为参数从powershell插入到SqlPlus [英] 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)

这是我正在尝试进行的查询示例

我用一个power shell脚本执行它，就像这样:

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

注意 $names 有一个变量名列表

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)

当然，SQL 会抛出错误.

And of course the SQL throws an error.

为什么它会像这样替换参数?

Why it substitutes the parameter like this ?

我怎样才能实现我想要做的事情，所以第一个参数是一个字符串列表，它将在 where name in(&1) 子句中使用.我的目标是 sql 将是 where name in ('Name1', 'Name2')

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

如果有帮助，将在 Oracle 11 上执行 SQL.

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

推荐答案

powershell 示例

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

输出 powershell:

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

bat 文件示例.

@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

输出bat文件:

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

这篇关于如何将列表作为参数从powershell插入到SqlPlus的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！