Suppressing the "rows selected" message in Oracle SQL.

What with middleware such as ODBC, JDBC, ADO, etc. it's not often that we find ourselves querying SQL databases directly at the SQL interpreter.  I recently had cause to do this in order extract some existing data to feed into a load testing tool.

Normally whenever you execute a SQL SELECT query in Oracle, the returned data has an "n rows selected" footer.  Most of the time this is helpful and informative, but if you are SPOOLing the query results for use in a data file, this additional information pollutes the captured data.

-- Desciption: Extract and save name data.
-- Author: Philip Thatcher
-- Date: 11/07/2006
SET ECHO OFF;
SET TIMING OFF;
SET SERVEROUTPUT ON SIZE 1000000 format wrapped;
SET PAGES 999;
SET LINESIZE 1000;
SET PAGESIZE 9999;
SET TRIMSPOOL ON;
SET HEADING OFF;
SPOOL NameData.csv
SELECT TRIM(IDENTIFIER) || ',' || TRIM(FIRST_NAMES) || ',' || TRIM(FAMILY_NAME) FROM PERSONS;
SPOOL OFF

Results in NameData.csv:

1,JAMES ROBERT,CARPENTER
2,HARRIET,CARRINGTON
3,TERESA,CARROLL
4,BENJAMIN,CARTER
4 rows selected. 

The inclusion of the "rows selected" line in the CSV file caused the load test tool to abort when encountered – it was intended that it would loop back to the top.

Considering this a common requirement, I googled for "ORACLE SQL SELECT suppress "rows selected" message" and was surprised not to find the answer, or, in fact, many hits at all (Oracle's own documentation is web published and would have been included in the Google search).

 

One suggested work around that did come up was to post-process the SPOOLed data file to remove the unwanted line.

 

e.g.
% grep -v "rows selected" tables.txt > temp; mv temp tables.txt

Thankfully, a very experienced Oracle DBA in my office was able to provide the correct solution (thx Matthew).  The Oracle command to do exactly this is "SET FEEDBACK OFF;".  Simply add this into the SQL script alongside the other "SET" lines.

Given its usefulness, and distinct lack of publicity, I thought I'd contribute it here.