1

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
  • 1
  • 1
rvasu
  • 73
  • 2
  • 6

1 Answers1

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
Community
  • 1
  • 1
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • The syntax with 'pages 0' works great. Thank you Alex! – rvasu Apr 04 '17 at 22:50
  • Actually, the leading spaces in the output value doesn't work with zabbix for me. – rvasu Apr 05 '17 at 22:06
  • 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? – rvasu Apr 05 '17 at 22:14
  • Did you escape the inner closing parenthesis again, with `^^^)` – Alex Poole Apr 05 '17 at 22:21
  • Just came here to say that it worked after escaping the ).. Thank you again Alex, really appreciate it. – rvasu Apr 05 '17 at 22:49