HELLO  10   ACCOUNTING
7839  KING
7698  BLAKE
7782  CLARK
7566  JONES
7788  SCOTT
7902  FORD
7369  SMITH
7499  ALLEN
7521  WARD
7654  MARTIN
7844  TURNER
7876  ADAMS
7900  JAMES
7934  MILLER
HELLO  20   RESEARCH
7839  KING
7698  BLAKE
7782  CLARK
7566  JONES
7788  SCOTT
7902  FORD
7369  SMITH
7499  ALLEN
7521  WARD
7654  MARTIN
7844  TURNER
7876  ADAMS
7900  JAMES
7934  MILLER
HELLO  30   SALES
7839  KING
7698  BLAKE
7782  CLARK
7566  JONES
7788  SCOTT
7902  FORD
7369  SMITH
7499  ALLEN
7521  WARD
7654  MARTIN
7844  TURNER
7876  ADAMS
7900  JAMES
7934  MILLER
HELLO  40   OPERATIONS
7839  KING
7698  BLAKE
7782  CLARK
7566  JONES
7788  SCOTT
7902  FORD
7369  SMITH
7499  ALLEN
7521  WARD
7654  MARTIN
7844  TURNER
7876  ADAMS
7900  JAMES
7934  MILLER
56

DECLARE
        emp_dept_id emp.deptno%TYPE;
        emp_emp_id emp.empno%TYPE;
        emp_last_name emp.ename%TYPE;
        v_count number DEFAULT 0;
BEGIN
        FOR i IN (SELECT DISTINCT deptno, dname
            FROM dept)
        LOOP
            --v_COUNT := v_COUNT + 1;
            DBMS_OUTPUT.PUT_LINE('HELLO'||'  '||i.deptno||'   '||i.dname);
            FOR j IN (SELECT empno, ename
                INTO emp_emp_id,emp_last_name
                FROM emp)
            LOOP
                DBMS_OUTPUT.PUT_LINE(j.empno||'  '||j.ename);
                v_COUNT := v_COUNT + 1;
            END LOOP;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(v_COUNT);
END;

            FOR j IN (SELECT empno, ename
                INTO emp_emp_id,emp_last_name
                FROM emp
                where deptno = i.deptno)             --> this!
            LOOP

select  case grouping(empno)
          when 0 then empno || '  ' || ename
          else 'HELLO' || '  ' || d.deptno || '   '|| d.dname
        end emp_list
  from  emp e,
        dept d
  where d.deptno = e.deptno(+)
  having grouping(empno) = 1 or empno is not null
  group by grouping sets((d.deptno,dname),(d.deptno,empno,ename,dname))
  order by d.deptno,
           grouping(empno) desc
/

EMP_LIST
-----------------------
HELLO  10   ACCOUNTING
7934  MILLER
7839  KING
7782  CLARK
HELLO  20   RESEARCH
7876  ADAMS
7788  SCOTT
7566  JONES
7369  SMITH
7902  FORD
HELLO  30   SALES

EMP_LIST
-----------------------
7900  JAMES
7499  ALLEN
7521  WARD
7654  MARTIN
7698  BLAKE
7844  TURNER
HELLO  40   OPERATIONS

18 rows selected.

SCOTT@orcl >