ORACLE SQL: GETTING THE COUNT OF ROWS RETRIEVED IN SQLPLUS (WITHOUT USING FEEDBACK OPTION)

#--Below is a way we can display the total row count in the end of the sqlplus script output using Aggregate function over the entire partition

SET TERMOUT OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON
SET VERIFY OFF
SET PAGESIZE 0
COLUMN empno FORMAT 99999
COLUMN ename FORMAT a10
COLUMN ROW_COUNT NEW_VALUE TOTAL_ROWS NOPRINT
spool C:\Users\Bhaskar\Blog\Blog\example_output.txt REPLACE
SELECT 0 "ROW_COUNT" FROM DUAL:
PROMPT EMPNO|ENAME|JOB|DEPTNO
PROMPT
SELECT empno, ename, job, deptno,count(*) over () "ROW_COUNT"
FROM emp;
PROMPT
SELECT 'Row Count:('||TO_CHAR(SYSDATE,'DD-MM-YYYY')||'): '||&TOTAL_ROWS
FROM DUAL;
SPOOL OFF
CLEAR COLUMNS

#--Below is the way we can display the total rowcount in the end of the script using simply rownum

SET TERMOUT OFF 

SET FEEDBACK OFF

SET SERVEROUTPUT ON

SET VERIFY OFF

SET PAGESIZE 0

COLUMN empno FORMAT 99999

COLUMN ename FORMAT a10

COLUMN ROW_COUNT NEW_VALUE TOTAL_ROWS NOPRINT 

spool C:\Users\Bhaskar\Blog\Blog\example_output.txt REPLACE

SELECT 0 "ROW_COUNT" FROM DUAL:

PROMPT EMPNO|ENAME|JOB|DEPTNO

PROMPT

SELECT empno, ename, job, deptno,ROWNUM "ROW_COUNT"

FROM emp;

PROMPT

SELECT 'Row Count:('||TO_CHAR(SYSDATE,'DD-MM-YYYY')||'): '||&TOTAL_ROWS 

FROM DUAL;

SPOOL OFF

CLEAR COLUMNS

NOTE:

#--1). In the script above, the TOTAL_ROWS variable is used to hold the value of the column ROW_COUNT

#--2). The initial count is set to 0 by using the first SELECT query

#--3). Observe that the value in the variable TOTAL_ROWS is referenced using "&" in the finale SELECT clause