How to echo text during SQL script execution in SQLPLUS

Question

I have a batch file which runs a SQL script in sqlplus and sends the output to a log file:

sqlplus user/pw < RowCount.sql > RowCount.log

My log file contains this:

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production  SQL> SQL>    COUNT(*) ----------      0  SQL>    COUNT(*) ----------      0 

etc. but it's several thousand lines of output and therefore hard to determine which results belong to which statement.

I would like to add some formatting to the output, so that I may discern what happened. Either an echo of the executed statement or manually inserting some "echo" statements into the script would be fine. Ideally it would look something like this:

SQL> select(*) from TableA;   COUNT(*) ----------      0  SQL> select(*) from TableB;   COUNT(*) ----------      0 

Answer 1

The prompt command will echo text to the output:

prompt A useful comment. select(*) from TableA; 

Will be displayed as:

SQL> A useful comment. SQL>    COUNT(*) ----------      0 

Answer 2

You can use SET ECHO ON in the beginning of your script to achieve that, however, you have to specify your script using @ instead of < (also had to add EXIT at the end):

test.sql

SET ECHO ON  SELECT COUNT(1) FROM dual;  SELECT COUNT(1) FROM (SELECT 1 FROM dual UNION SELECT 2 FROM dual);  EXIT 

terminal

sqlplus hr/oracle@orcl @/tmp/test.sql > /tmp/test.log 

test.log

SQL>  SQL> SELECT COUNT(1) FROM dual;    COUNT(1) ----------      1  SQL>  SQL> SELECT COUNT(1) FROM (SELECT 1 FROM dual UNION SELECT 2 FROM dual);    COUNT(1) ----------      2  SQL>  SQL> EXIT