Search code examples
linuxoracle-databaseoracle12csqlplus

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!

Solution

  • 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