For embedding in Unix shell scripts I want to execute a set of SQL commands and exit sqlcl straight afterwards.
For this I use successfully the pipe-from-stdin method:
echo "SELECT COLA FROM MYTABLE;" | sql -L <connectionstr>
which return the query result to stdout. Great!
Now I want to "decorate" the query with some directives for sqlcl. E.g. I want to change the output format:
echo "SET SQLFORMAT CSV;\n SELECT COLA FROM MYTABLE;" | sql -L <connectionstr>
Works like a beauty! Sometimes I want to add echoing of the SQL command:
echo "SET ECHO ON;\n SELECT COLA FROM MYTABLE;" | sql -L <connectionstr>
Surprisingly this does not work! The "SET ECHO ON;" directive is ignored. However if I do
echo "SET ECHO ON;\n SELECT COLA FROM MYTABLE;" >combined_query.sql
echo @combined_query.sql | sql -L <connectionstr>
then it works. Is there a way to make "SET ECHO ON;" work in a direct pipe without the detour via a file?
The short answer is 'no'. From the documentation:
ECHO does not affect the display of commands you enter interactively or redirect to SQL*Plus from the operating system.
That's the SQL*Plus documentation; the SQLcl documentation doesn't go into much detail but in general it behaves the same as SQL*Plus.