SQLPlus Command Line In-line Set Commands

Question

I have the need to run linux command line direct query, including SET options.

The query by itself runs fine.

However, when I attempt to add set commands, it fails.

(my alias 'db' connects to SQLPlus successfully)

OK/works fine (when sending single commands, issue seems to be with strining them together):

$db<<<"SELECT * from mydb.mytable;"
$db <<<"show heading;"
$db <<<"SET heading OFF;"

Not OK - Any variation of quotes/not quotes I try results in error:

$db<<<SET HEADING OFF "SELECT * from mydb.mytable;"
$db<<<"SET HEADING OFF, SELECT * from mydb.mytable;"
$db<<<"SET HEADING OFF "SELECT * from mydb.mytable;"

Results in complete error or:

SQL> SP2-0158: unknown SET option ","

Please assist.

Answer 1

You should not put SET and SELECT in the same line, they are seperate commands. For example this works:

$ alias db='sqlplus -s user/password'
$ db <<< 'set heading off
set timing on
set echo off
select * from dual;'
X
Elapsed: 00:00:00.00

If you insist on using a one-liner, you could try this:

$ echo -ne 'set heading off\nset timing on\nset echo off\nselect * from dual;' | db
X
Elapsed: 00:00:00.00

• user4950017: Thank you Balazs Papp; both solutions work and I will use a mixture of the two!

Answer 2

@Balaz gave me a great idea. Based on his answer, first set environment variable for your standard set commands, connection credentials, and create a function in your shell (can be done in your .bashrc file):

sets="set pagesize 0 heading off underline off feedback off"
ConnStr=user/passwd@servicename
function db { sqlplus -s $ConnStr <<< "$(echo -ne "${sets}\n${@}")" ; }

Then run sqlplus command like such:

db "select * from dual;"

Voila!