Wednesday, February 17, 2016

Another HEREDOC style for Windows Batch Files

I wanted to pipe a SQL query to sqlplus in a batch file, without using a temp SQL file.

That technique involves echo'ing the SQL statements to a temp file, then call sqlplus on that file:

REM create the temp SQL file
echo SELECT USER >>temp.sql
echo FROM DUAL;  >>temp.sql

REM execute sqlplus
sqlplus -S / @temp.sql

REM cleanup!
del temp.sql

An alternative is to wrap the echo statements in parentheses "( ... )", so they execute as a block, then pipe "|" the result to sqlplus:

(
    echo SELECT USER
    echo FROM DUAL;
) | sqlplus -S /

Admittedly, this is not much prettier than the first example, but it's self-contained, and doesn't leave random temp files that may or may not get cleaned up.