create or replace function pivot_func
return SYS_REFCURSOR as
  v_sql	 long;
  v_refcur  sys_refcursor;
  v_firstdate date;
  v_days  number;
begin

select to_date(to_char(trunc (sysdate, 'month'),'dd-mon-yyyy'))
    into v_firstdate
    from dual;
	
  V_DAYS := TRUNC(TO_CHAR(SYSDATE,'DD')); 
      v_sql :=
    	 ' select *
		   from (select module_name,
		                app_name, 
						audit_date
          from audit_trail
         where audit_date between  to_char(trunc (sysdate, '||''''||'month'||''''||'),'||''''||'dd-mon-yyyy'||''''||') 
		       and  TO_CHAR(LAST_DAY(sysdate),'||''''||'dd-mon-yyyy'||''''||'))
   	  pivot  (sum(to_char(audit_date,'||''''||'dd-mm-yy'||''''||')) for audit_date in  (';
   	for i in 1..v_days
    LOOP
     v_sql := v_sql ||''''||v_firstdate ||''''||',';
     V_FIRSTDATE := V_FIRSTDATE + 1; 
    End Loop ;
    V_SQL := RTRIM (V_SQL, ',') || ')) '; 
    dbms_output.put_line(V_SQL);
    open v_refcur for v_sql;
     Return V_Refcur;
     close v_refcur;
   end pivot_func;


i will get the sql as 

  select *
		   from (select module_name,
		                app_name, 
						audit_date
                  from METRICS_AUDIT_TRAIL1
             where audit_date between  to_char(trunc (sysdate, 'month'),'dd-mon-yyyy') 
		       and  TO_CHAR(LAST_DAY(sysdate),'dd-mon-yyyy'))
   	  pivot  (sum(to_char(audit_date,'dd')) for audit_date in  ('01-JAN-14','02-JAN-14','03-JAN-14','04-JAN-14','05-JAN-14','06-JAN-14','07-JAN-14'))