VARIABLE g_refcur REFCURSOR
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAP

DECLARE
  v_sql	 VARCHAR2(32767);
  v_refcur  SYS_REFCURSOR;
  v_sql2	 VARCHAR2(32767);
BEGIN
  v_sql := '    SELECT deptno';
  FOR r IN
	 (SELECT DISTINCT TO_CHAR (hiredate,'YYYY') years
 	  FROM	 emp
 	  -- add date formats to to_date's:
 	  WHERE  hiredate BETWEEN TO_DATE ('01-JAN-1970', 'DD-MON-YYYY')
 			      AND TO_DATE ('31-DEC-2003', 'DD-MON-YYYY')
 	  ORDER  BY 1)
  LOOP
 	 v_sql := v_sql || ',
 		SUM (DECODE (TO_CHAR (hiredate, ' || chr(39) || 'YYYY' || chr(39) || '), ' ||
 		CHR (39) || r.years || CHR(39) || ', sal)) ' ||
 		'"' || r.years || '"';
  END LOOP;
  --
  -- add to_date's with date formats:
  v_sql := v_sql || '
	 FROM	emp
	 WHERE	hiredate BETWEEN TO_DATE (' || chr(39) || '01-JAN-1970' || chr(39) || ', '
					    || chr(39) || 'DD-MON-YYYY' || chr(39) || ')
			     AND TO_DATE (' || chr(39) || '31-DEC-2003' || chr(39) || ', '
					    || chr(39) || 'DD-MON-YYYY' || chr(39) || ')
	 GROUP	BY deptno
	 ORDER	BY deptno';
  -- format dbms_sql output so that it is easeir to read:
  v_sql2 := v_sql || CHR(10);
  WHILE INSTR (v_sql2, CHR(10)) > 0 LOOP
	 DBMS_OUTPUT.PUT_LINE (SUBSTR (v_sql2, 1, INSTR (v_sql2, CHR(10)) - 1));
	 v_sql2 := SUBSTR (v_sql2, INSTR (v_sql2, CHR(10)) + 1);
  END LOOP;
  OPEN v_refcur FOR v_sql;
  -- assign value of v_refcur to variable that you can display:
  :g_refcur := v_refcur;
END;

    SELECT deptno,
           SUM (DECODE (TO_CHAR (hiredate, 'YYYY'), '1980', sal)) "1980",
           SUM (DECODE (TO_CHAR (hiredate, 'YYYY'), '1981', sal)) "1981",
           SUM (DECODE (TO_CHAR (hiredate, 'YYYY'), '1982', sal)) "1982",
           SUM (DECODE (TO_CHAR (hiredate, 'YYYY'), '1983', sal)) "1983"
    FROM   emp
    WHERE  hiredate BETWEEN TO_DATE ('01-JAN-1970', 'DD-MON-YYYY')
                        AND TO_DATE ('31-DEC-2003', 'DD-MON-YYYY')
    GROUP  BY deptno
    ORDER  BY deptno

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> PRINT g_refcur

    DEPTNO       1980       1981       1982       1983
---------- ---------- ---------- ---------- ----------
        10                  7450       1300
        20        800       5975       3000       1100
        30                  9400

3 rows selected.