CODEWITHSUNDEEP
windowsoracle-databasesqlplus
rvasu
rvasu

Reputation: 73

SQLPLUS - Multiple sql statements from command line

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?

Upvotes: 1

Views: 4874

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

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

Upvotes: 1

Related Questions