Populate / display dynamic columns in Forms [message #619084] |
Thu, 17 July 2014 23:26 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
oralover2006
Messages: 144 Registered: January 2010 Location: India
|
Senior Member |
|
|
hi all,
I found following example to have Dynamic Columns ( Name and Number of Columns ) and data ( in Matrix / Pivot ) and want to fill a Form's grid and then use that grid to make an Excel file ( .csv ), but due to dynamic Column Names & Numbers how I can achieve? please help I do not have much Forms experience but this is requirement of Job.
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.
regards.
|
|
|
Re: Populate / display dynamic columns in Forms [message #619117 is a reply to message #619084] |
Fri, 18 July 2014 02:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Forms isn't designed to handle dynamic columns. You can write a form that will get the data and write the csv easily enough, but I can't think of any way to get the data to appear in a datablock, other than having a large number of generic varchar columns.
|
|
|
|