Pass Parameters from a batch file to sqlplus script
You can pass a parameter this way:
script.sql:
select '&1' from dual;
the call:
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
Bill
Updated on June 08, 2022Comments
-
Bill almost 2 years
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.
My Password Batch file looks like this. parms.bat
Rem Oracle Db set odbUsername=myUserName set odbpassword=myPassword
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.
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;
I call this script with a batch file that runs through windows task scheduler.
It looks like this.
sqlplus /nolog @C:\FileTransfers\AutomationScripts\GoodSence\SpoolGoodSenceDataSet.sql
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.