SQLPLUS - Multiple sql statements from command line

Question

I have an Oracle 11 instance on Windows 2003 R2.

I run this to get the output as shown below:

C:\>echo select count(*) from v$session; | sqlplus -s zabbix/pwd@localhost:

1521/orcl

  COUNT(*)
  ----------
       31

But, I want the output to just be 31, and not with the column header and the lines below it - something to the effect of SET HEADING OFF from within SQL*Plus.

I came across this question which suggested using the -e option for echo, but that either doesn't seem to work on Windows or I am missing something.

C:\>echo -e "set heading off; \n select count(*) from v$session;" | sqlplus -s zabbix/pwd@localhost:1521/orcl

SP2-0734: unknown command beginning "-e "set he..." - rest of line ignored.

This is the exact command mentioned in the post I referenced above:

C:\>echo -e "select 1 from dual; \n select 2 from dual;" | sqlplus -s zabbix/pwd@localhost:1521/orcl

SP2-0734: unknown command beginning "-e "select..." - rest of line ignored.

I do not see a SQL*Plus flag I can use (like -s I used above for silence) to turn heading off. Hence trying this method!

What can I do to make it work on Windows?

Community · Accepted Answer · 2017-05-23 12:25:51Z

1

Bit messy, but you can do:

C:>(echo set heading off & echo select count(*^^^) from v$session; & echo exit;) | sqlplus -s zabbix/pwd@localhost:1521/orcl

        53

The ^^^) part is to escape the parenthesis in the count(*), within the parentheses wrapping the two echo commands together - which provides a single input for SQL*Plus.

This has a blank line at the top; you might prefer to use set pagesize 0 instead of set heading off:

C:>(echo set pages 0 & echo select count(*^^^) from v$session; & echo exit;) | sqlplus -s zabbix/pwd@localhost:1521/orcl
        53

You can put multiple settings in one set command if you need to, as well.

Alternatively, just put all your commands in a script file, e.g. test.sql:

set pages 0
select count(*) from v$session;
exit

and then run that with:

sqlplus -s zabbix/pwd@localhost:1521/orcl @test

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Apr 4, 2017 at 22:31

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

rvasu Over a year ago

The syntax with 'pages 0' works great. Thank you Alex!

rvasu Over a year ago

Actually, the leading spaces in the output value doesn't work with zabbix for me.

rvasu Over a year ago

I mean, instead of select count() from v$session; COUNT() -------------- 39 I need: select trim(count()) from v$session; COUNT() ------------- 39 When I use echo: (echo set pages 0 & echo select trim(LIMIT_VALUE) from v$resource_limit where RESOURCE_NAME='sessions'; & echo exit;) | "C:\Program Files\Zabbix\sqlplus.exe" -s zabbix/pwd@localhost:1521/orcl I get - 'from was unexpected at this time.' Any idea how to resolve this?

Alex Poole Over a year ago

Did you escape the inner closing parenthesis again, with ^^^)

rvasu Over a year ago

Just came here to say that it worked after escaping the ).. Thank you again Alex, really appreciate it.

Collectives™ on Stack Overflow

SQLPLUS - Multiple sql statements from command line

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related