"I have a bad feeling about this..." - Lots of people, over 6 Star Wars movies
Not so long ago, I had a requirement to be able to have a scheduled job that ran a SQL script, took the output and did something with it in Windows CMD shell. Not only that, I had to feed a parameter in.
I'd done this many times in my previous life, taking a value from a SQL script in Oracle, passing it to the Unix shell and running with it.
Having to do it in Windows was a bit more of a challenge.
Took quite a bit of research to find out how to do it, so it's worthy of preserving here.
First, we need the SQL we're going to execute:
set NOCOUNT ON select column1 from table1 where column2 = '$(var1)'
Next, we need to call it and pass the variable in: (let's assume I've setup a user called 'ro' with a password of 'read' that only has select access on the table I'm selecting from. The script (above) is sitting in 'c:\sql' and I'm writing the output to 'c:\sql\out'. Finally, the process that calls this script is passing the parameter. (In the real world this is all on one line. A blog is only so wide ...). Also, in my example this script is called by another process passing in a job number as the parameter - so the %1 is the job number parameter that is being passed in.
sqlcmd -S SRV01 -d myprd1 -U ro -P read -h-1 -i c:\sql\query1.sql
-o c:\sql\out\query1.txt -v var1="%1"
End result is that I now have the value of column1 from table1 sitting in a text file.
Next up, we need to be able to interrogate that value in order to be able to do something else with it.
for /f "usebackq delims=" %%i in (`cat c:\sql\out\query1.txt`) do set H001=%%i
At the end of that, we now have the value from the text file sitting in variable H001, that we can use for whatever.
Of course a clean system is a good system, and you should always clean up after yourself:
if exist c:\sql\out\query1.txt (erase c:\sql\out\query1.txt)
So far, so good. But, what is done on SQL Server in Windows should also be done in Unix and Oracle, right ? Second verse, same as the first (except for the whole change of OS / RDBMS thing).
Going to change this one up just a little. Let's say we need two values, the first being 8 characters and the second being 4.
set verify off set echo off set feedback off set heading off spool /tmp/query1.out select substr(column1,1,8)||substr(column3,1,4) from table1 where column2 = '&1'; spool off exit
The same requirements for calling the script. And again, this is called by another processing passing in a parameter in the first position.
export ORACLE_SID=myprd1 $ORACLE_HOME/bin/sqlplus ro/read @/scripts/query1.sql $1
Alternatively, and actually my preferred way of doing it is to put the username and password as the first line of the script and to remove the user/pass from the invocation line. This way, in the event of someone trying to execute 'ps -ef | grep sqlplus' at just the magic moment, won't see your database credentials in the process list.
Once we have our output, we need to get it into the environment variable.
H001=`cat /tmp/query1.txt | grep -v ^$ | cut -c1-8` H002=`cat /tmp/query1.txt | grep -v ^$ | cut -c9-12`
There's other ways to get the same information too, I'll list just one more here.
H000=`$ORACLE_HOME/bin/sqlplus -s @/scripts/query1.sql` H001=`echo $H000 | cut -c1-8` H002=`echo $H000 | cut -c9-12`
That would assign the whole 12 character string to H000 and you could extract the other two variables from it.
