Oracle PL/SQL Example: Select Total Record Counts in a Cursor

  cursor c1 IS
    SELECT 
      DISTINCT EMPLOYEE_ID, 
      FIRST_NAME,
      LAST_NAME,
      HIRE_DATE,
      COUNT(DISTINCT EMPLOYEE_ID) OVER() CNT
    FROM EMPLOYEES;

CREATE OR REPLACE PROCEDURE pTest
AS
	logfile_prefix varchar2(64) := 'pTest';
	log_dir varchar2(64) := 'LOG_DIR';
	logfile_name VARCHAR2(50);
	logfile UTL_FILE.file_type;
 
	cursor c1 IS
        SELECT 
          DISTINCT EMPLOYEE_ID, 
          FIRST_NAME,
          LAST_NAME,
          HIRE_DATE,
          COUNT(DISTINCT EMPLOYEE_ID) OVER() CNT
        FROM EMPLOYEES;
 
	p_cnt INTEGER := 0;
	c1_rec c1%ROWTYPE;
 
BEGIN
    -- Timestamp log file name:
	logfile_name := logfile_prefix || '_' || TO_CHAR (SYSDATE, 'MMDDYYYY') || '.LOG';
	-- Open log file:
	logfile := UTL_FILE.fopen (log_dir, logfile_name, 'W');
 
	UTL_FILE.put_line (logfile, '{');
 
	OPEN c1;
	loop
		BEGIN
			FETCH c1 INTO c1_rec;
			exit WHEN c1%NOTFOUND;
 
			UTL_FILE.put (logfile, '"' || p_cnt || '":{');
 
			UTL_FILE.put (logfile, '"0":"' || c1_rec.EMPLOYEE_ID || '",');
			UTL_FILE.put (logfile, '"1":"' || c1_rec.FIRST_NAME || '",');
			UTL_FILE.put (logfile, '"2":"' || CONVERT(c1_rec.LAST_NAME,'us7ascii') || '",');
			UTL_FILE.put (logfile, '"3":"' || c1_rec.HIRE_DATE || '",');
 
			p_cnt := p_cnt + 1;
			IF p_cnt = c1_rec.CNT THEN
                -- Found last record
				UTL_FILE.put_line (logfile, '}');
			ELSE
				UTL_FILE.put_line (logfile, '},');
			END IF;
 
            -- Flush file every 10th record:
			IF MOD(p_cnt, 10) = 0 THEN 
				UTL_FILE.fflush(logfile);
			END IF;
 
		END;
	END loop;
	close c1;
	UTL_FILE.put_line (logfile, '}');
 
	UTL_FILE.FFLUSH(logfile);
	UTL_FILE.FCLOSE(logfile);
exception
    WHEN others THEN
        UTL_FILE.put_line (logfile, ''Exception! ' || SQLCODE || '; ' || SQLERRM');
END;