oracle – Using SQLPlus Command Line In-line Set Commands

command lineoraclesqlplus

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.

Best Answer

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
Related Question