9

I have spooled a file before running the below command and got the output like this,

I have set heading off, feedback off

SET HEADING OFF
SET FEEDBACK OFF
SPOOL D:\TEST.TXT
SELECT SYSDATE FROM DUAL;
SPOOL OFF

OUTPUT in TEST.TXT:

SQL> SELECT SYSDATE FROM DUAL;

20-JAN-09

SQL> SPOOL OFF

How can i remove the two SQL> lines. I want only the output.

Thanks in advance.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Srinivasan Thirunavukkarasu
  • 103
  • 2
  • 2
  • 4

2 Answers2

15

The command you need is:

SET ECHO OFF

However, it only works for code run from scripts, not command entered interactively. You would create a script file like this (e.g. called test.sql):

SET HEADING OFF FEEDBACK OFF ECHO OFF PAGESIZE 0
SPOOL D:\TEST.TXT 
SELECT SYSDATE FROM DUAL; 
SPOOL OFF

Then in SQL Plus run it like this:

SQL> @test

I added PAGESIZE 0 to the SET command to remove the blank line you otherwise get before the date in the output file.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
4

use this:

#!/bin/ksh
CONNECT_STRING=dbapp/dbapp@inst
SQLPLUS_SETTINGS="SET PAGESIZE 1000 LINESIZE 500 ECHO OFF TRIMS ON TAB OFF FEEDBACK OFF HEADING OFF"
SQL_RESULT=`sqlplus -s ${CONNECT_STRING} << EOF
${SQLPLUS_SETTINGS}
select sysdate from dual;
exit;
EOF`

echo $SQL_RESULT >output_file
Vijay
  • 65,327
  • 90
  • 227
  • 319