将参数从批处理文件传递到sqlplus脚本 [英] Pass Parameters from a batch file to sqlplus script

问题描述

我正在尝试获取一个文件，其中包含我每天运行的某些脚本的用户名和密码.我现在有几个脚本正在使用包含我的用户名和密码的批处理文件进行工作.

I am trying to get one file with my user name and passwords for some scripts that I run every day. I have several scripts working now using a batch file that has my user names and passwords.

我的密码批处理"文件如下所示. parms.bat

My Password Batch file looks like this. parms.bat

Rem Oracle Db
set odbUsername=myUserName
set odbpassword=myPassword

我的其他脚本调用该批处理文件并获得正确的信息.我有几个SQL脚本，我也需要使用这些用户名和密码.我似乎无法让他们工作.但是我对sqlplus脚本还是相当陌生.

My other scripts call that batch file and get the information ok. I have a couple sql scripts that I also need to use these usernames and passwords. I can't seem to get them to work. But I am fairly new to sqlplus scripting.

我的Sql脚本看起来像这样.

My Sql Scripts looks like this.

CONNECT myusername@Oracleddbname/mypassword

SET FEEDBACK OFF;
SET ECHO OFF;
SET TERMOUT OFF;
SET HEADING OFF;
SET PAGESIZE 0;
SET LINESIZE 500;
SET TIMING OFF;
SET TRIMSPOOL ON;
SET COLSEP ',';

SPOOL C:\FileTransfers\MeterData.csv

PROMPT CoopCode,MeterID,DateTime,Value
SELECT DISTINCT
          a.coopcode
       || ','
       || a.meterno
       || ','
       || a.readdatetime
       || ','
       || a.usage
  FROM temp_reconfigured a, temp_goodsence b
 WHERE a.coopcode = b.coopcode AND a.meterno = b.meterno
;

Spool off;

EXIT;

我通过在Windows任务计划程序中运行的批处理文件来调用此脚本.

I call this script with a batch file that runs through windows task scheduler.

看起来像这样.

sqlplus /nolog        @C:\FileTransfers\AutomationScripts\GoodSence\SpoolGoodSenceDataSet.sql

所以我想将用户名从批处理文件传递给sql.我读过几本书，尝试了30遍，似乎都没有用.谢谢您的提前帮助.

So I would like to pass the user name to the sql from the batch file. I have read several things and tried about 30 and none seem to work. Thank you for your help in advance.

推荐答案

您可以通过以下方式传递参数:

You can pass a parameter this way:

script.sql:

select '&1' from dual;

通话:

D:\>sqlplus user/password@db @d:\script.sql 'value'

SQL*Plus: Release 11.2.0.2.0 Production on Lun Ott 3 17:02:10 2016

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


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

old   1: select '&1' from dual
new   1: select 'value' from dual

'VALU
-----
value

这篇关于将参数从批处理文件传递到sqlplus脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！