SQL*Plus how to execute environment set-up and query in one statement

I execute the following query which returns one value and it works fine.

echo "select myquery ;" | sqlplus user/pass@dbase | | head -4 | tail -1

The issue is that when the one value returned is too large it gets split in multiple rows thus the ETL tool that I further use gets problems. Therefore I have tried to use the linesize option but I am not able to combine it with the query.

If I connect to sqlplus and run: SET LINESIZE 3200 followed by select myquery ; the output is fine but I am not able to combine them in the echo statement. I have tried in different ways, for example:

echo "SET LINESIZE 3200 / select myquery ;" | sqlplus user/pass@dbase | | head -4 | tail -1

Any ideas?

Thanks!

Answer

A heredoc (as shown in @Philippe’s answer) is going to be easier to read and maintain, but if for some reason you really wanted to keep it in one line you could add the -e flag to echo and embed new lines:

echo -e "SET LINESIZE 3200 n select myquery ;" | sqlplus user/pass@dbase | head -4 | tail -1

or use printf:

printf "set linesize 3200 n select myquery ;n" | sqlplus user/pass@dbase | head -4 | tail -1

(In both I’ve taken out the extra | from your original version)

You can extend this to avoid the need to head and tail the output:

printf "set linesize 3200 n set pagesize 0 n set feedback off n select myquery ;n" | sqlplus -s user/pass@dbase

or with the various set options combined:

printf "set linesize 3200 pagesize 0 feedback off n select myquery ;n" | sqlplus -s user/pass@dbase

The -s suppresses the banner; from the head/tail values you’re using I imagine you already have that and just didn’t show it. (I’d usually throw in -l as well, so it doesn’t get stuck if the credentials are wrong.) The other set commands remove the column headings and “1 row selected.” message.

While that’s a longer single command line overall, it has the advantage of not manipulating/mangling any error output you might get.

Or as a heredoc:

sqlplus -l -s user/pass@dbase <<EOF
set linesize 3200
set pagesize 0
set feedback off
select myquery ;
EOF