Home » Developer & Programmer » Forms » Populate / display dynamic columns in Forms (Developer Forms 10g, DB 11g)
Populate / display dynamic columns in Forms [message #619084] Thu, 17 July 2014 23:26 Go to next message
oralover2006
Messages: 119
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 messageGo to next message
cookiemonster
Messages: 11285
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.
Re: Populate / display dynamic columns in Forms [message #619129 is a reply to message #619117] Fri, 18 July 2014 05:05 Go to previous message
oralover2006
Messages: 119
Registered: January 2010
Location: India
Senior Member
thanks cookiemonster for reply.

so, I should create a Matrix Type Report only ?
no any other way to achieve that? because I can't produce Excel file from Report runtime.

regards.
Previous Topic: Based on LOV Values in the same Canvas
Next Topic: Populate a base table block based on the value of a field
Goto Forum:
  


Current Time: Sat Dec 20 18:13:11 CST 2014

Total time taken to generate the page: 0.20667 seconds