3 Things You Want To Know About SET FEEDBACK Command

If you ever wrote an SQL script, then you are familiar with the SET FEEDBACK SQLPlus command.

What most of us use this command for is, to set it to the value of ON or OFF, depending on the outcome we are looking for.

If we want to display the number of records returned by the select statement, then we set feedback ON.
If we do not want to display the number of records (rows) returned by the select statement, then we set feedback OFF.

What you might not be familiar with is, that the default value of SET FEEDBACK is not ON, instead it is FEEDBACK ON FOR 6 or more rows.

Which means, by default, feedback will only return the number of records, if the statement returns at least 6 rows.

SQL>show feedback
FEEDBACK ON for 6 or more rows SQL_ID OFF

SQL>select username from dba_users where username='SYS';

USERNAME
----------
SYS

-- You see, there is no number of rows message returned, as the query only returned 1 row.

SQL>select username from dba_users;

USERNAME
----------
SYS
SYSTEM
...

36 rows selected

-- Feedback is returned here as the query returns more than 6 rows.

For different versions of Oracle, the default value differs, as shown below:

--11g,12c
SQL>show feedback
FEEDBACK ON for 6 or more rows

--18c
SQL>show feedback
FEEDBACK ON for 6 or more rows SQL_ID OFF

If you SET FEEDBACK 1, this is equivalent with SET FEEDBACK ON, which means that no matter how many records the statement returns (at least 1), you get the feedback.
If you SET FEEDBACK 0, this is equivalent with SET FEEDBACK OFF, which means that no feedback is returned, no matter how many rows the statement returns.

As Oracle versions evolve, so does the SET FEEDBACK command.

12c introduced us to SET FEEDBACK ONLY, which suppresses display of data returned by the query. The only thing that is displayed is the number of rows the query returned.
Watch it in action:

SQL>SET FEEDBACK ONLY

SQL>show feedback
feedback ONLY

SQL>select username from dba_users where username='SYS';
   
    1 row returned

18c introduced us to SET FEEDBACK ON SQL_ID , which means the SQL_ID for the currently executed SQL or PL/SQL statement is displayed after the results.
This is very useful, and a huge time saver, when all you need is to know the SQL_ID. If you are not running 18c yet, there are other ways to get the SQL_ID of the statement you just ran.

When feedback for SQL_ID is ON, then the value of the SQL_ID is assigned to a predefined variable _SQL_ID

Let’s see the new behaviour in action:

SQL>SET FEEDBACK ON SQL_ID

SQL>show feedback
FEEDBACK ON for 1 or more rows SQL_ID ON

SQL>select username from dba_users where username='SYS';

USERNAME
----------
SYS

1 row selected.

SQL_ID: ay8r70pg7smra


SQL> select '&_sql_id' as sql_id from dual;

SQL_ID
-------------
ay8r70pg7smra

SQL> SET FEEDBACK OFF

SQL> show feedback
feedback OFF SQL_ID OFF

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana