Oracle query - UTL_FILE.FOPEN ( excel format) dates as headers instead of hardcoding the days

http://www.experts-exchange.com/acceptAnswer.jsp?aid=40763906 ( Closed )

I am sorry the user keeps changing their minds. They wanted output in excel and I have written using UTL_FILE.FOPEN. The output is in excel. Only problem is that I have days hard coded as before. But your solution earlier posted on the closed question really helped to for csv. I tried to add your dates concept. But got error.

CREATE OR REPLACE PROCEDURE load_hosp_usage (date_from IN VARCHAR,
                                                   ym IN VARCHAR)  AS

  FILENAME  UTL_FILE.FILE_TYPE;
  FILENAME1 VARCHAR2(10000);
  v_procedure_c constant varchar2(30) := 'load_hosp_usage';
  v_location_i integer;
	
	
  CURSOR C1 IS
    SELECT inv_product_type,
         customer_id,
         patient_name,
         SUM(
             CASE
                 WHEN iwd = TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd = invoice_date THEN 1 ELSE 0 END
             END
         )
             sun_1,
         SUM(
             CASE
                 WHEN iwd + 1 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 1 = invoice_date THEN 1 ELSE 0 END
             END
         )
             mon_1,
         SUM(
             CASE
                 WHEN iwd + 2 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 2 = invoice_date THEN 1 ELSE 0 END
             END
         )
             tue_1,
         SUM(
             CASE
                 WHEN iwd + 3 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 3 = invoice_date THEN 1 ELSE 0 END
             END
         )
             wed_1,
         SUM(
             CASE
                 WHEN iwd + 4 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 4 = invoice_date THEN 1 ELSE 0 END
             END
         )
             thu_1,
         SUM(
             CASE
                 WHEN iwd + 5 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 5 = invoice_date THEN 1 ELSE 0 END
             END
         )
             fri_1,
         NVL(SUM(CASE WHEN iwd + 6 = invoice_date THEN 1 ELSE 0 END),0) sat_1,
         NVL(SUM(CASE WHEN iwd + 7 = invoice_date THEN 1 ELSE 0 END),0) sun_2,
         NVL(SUM(CASE WHEN iwd + 8 = invoice_date THEN 1 ELSE 0 END),0) mon_2,
         NVL(SUM(CASE WHEN iwd + 9 = invoice_date THEN 1 ELSE 0 END),0) tue_2,
         NVL(SUM(CASE WHEN iwd + 10 = invoice_date THEN 1 ELSE 0 END),0) wed_2,
         NVL(SUM(CASE WHEN iwd + 11 = invoice_date THEN 1 ELSE 0 END),0) thu_2,
         NVL(SUM(CASE WHEN iwd + 12 = invoice_date THEN 1 ELSE 0 END),0) fri_2,
         NVL(SUM(CASE WHEN iwd + 13 = invoice_date THEN 1 ELSE 0 END),0) sat_2,
         NVL(SUM(CASE WHEN iwd + 14 = invoice_date THEN 1 ELSE 0 END),0) sun_3,
         NVL(SUM(CASE WHEN iwd + 15 = invoice_date THEN 1 ELSE 0 END),0) mon_3,
         NVL(SUM(CASE WHEN iwd + 16 = invoice_date THEN 1 ELSE 0 END),0) tue_3,
         NVL(SUM(CASE WHEN iwd + 17 = invoice_date THEN 1 ELSE 0 END),0) wed_3,
         NVL(SUM(CASE WHEN iwd + 18 = invoice_date THEN 1 ELSE 0 END),0) thu_3,
         NVL(SUM(CASE WHEN iwd + 19 = invoice_date THEN 1 ELSE 0 END),0) fri_3,
         NVL(SUM(CASE WHEN iwd + 20 = invoice_date THEN 1 ELSE 0 END),0) sat_3,
         NVL(SUM(CASE WHEN iwd + 21 = invoice_date THEN 1 ELSE 0 END),0) sun_4,
         NVL(SUM(CASE WHEN iwd + 22 = invoice_date THEN 1 ELSE 0 END),0) mon_4,
         NVL(SUM(CASE WHEN iwd + 23 = invoice_date THEN 1 ELSE 0 END),0) tue_4,
         NVL(SUM(CASE WHEN iwd + 24 = invoice_date THEN 1 ELSE 0 END),0)wed_4,
         NVL(SUM(CASE WHEN iwd + 25 = invoice_date THEN 1 ELSE 0 END),0) thu_4,
         NVL(SUM(CASE WHEN iwd + 26 = invoice_date THEN 1 ELSE 0 END),0) fri_4,
         NVL(SUM(CASE WHEN iwd + 27 = invoice_date THEN 1 ELSE 0 END),0) sat_4,
         NVL(SUM(CASE WHEN iwd + 28 = invoice_date THEN 1 ELSE 0 END),0) sun_5,
         NVL(SUM(CASE WHEN iwd + 29 = invoice_date THEN 1 ELSE 0 END),0) mon_5,
         NVL(SUM(CASE WHEN iwd + 30 = invoice_date THEN 1 ELSE 0 END),0) tue_5,
         NVL(SUM(CASE WHEN iwd + 31 = invoice_date THEN 1 ELSE 0 END),0) wed_5,
         NVL(SUM(CASE WHEN iwd + 32 = invoice_date THEN 1 ELSE 0 END),0) thu_5,
         NVL(SUM(CASE WHEN iwd + 33 = invoice_date THEN 1 ELSE 0 END),0) fri_5,
         NVL(SUM(CASE WHEN iwd + 34 = invoice_date THEN 1 ELSE 0 END),0) sat_5,
         NVL(SUM(CASE WHEN iwd + 35 = invoice_date THEN 1 ELSE 0 END),0) sun_6,
         NVL(SUM(CASE WHEN iwd + 36 = invoice_date THEN 1 ELSE 0 END),0) mon_6,
         NVL(SUM(CASE WHEN iwd + 37 = invoice_date THEN 1 ELSE 0 END),0) tue_6,
         NVL(SUM(CASE WHEN iwd + 38 = invoice_date THEN 1 ELSE 0 END),0) wed_6,
         NVL(SUM(CASE WHEN iwd + 39 = invoice_date THEN 1 ELSE 0 END),0) thu_6,
         NVL(SUM(CASE WHEN iwd + 40 = invoice_date THEN 1 ELSE 0 END),0) fri_6,
         NVL(SUM(CASE WHEN iwd + 41 = invoice_date THEN 1 ELSE 0 END),0) sat_6,
         NVL(SUM(CASE WHEN iwd + 42 = invoice_date THEN 1 ELSE 0 END),0) sun_7,
         NVL(SUM(CASE WHEN iwd + 43 = invoice_date THEN 1 ELSE 0 END),0) mon_7,
         NVL(SUM(CASE WHEN iwd + 44 = invoice_date THEN 1 ELSE 0 END),0) tue_7,
         NVL(SUM(CASE WHEN iwd + 45 = invoice_date THEN 1 ELSE 0 END),0) wed_7,
         NVL(SUM(CASE WHEN iwd + 46 = invoice_date THEN 1 ELSE 0 END),0) thu_7,
         NVL(SUM(CASE WHEN iwd + 47 = invoice_date THEN 1 ELSE 0 END),0) fri_7,
         NVL(SUM(CASE WHEN iwd + 48 = invoice_date THEN 1 ELSE 0 END),0) sat_7,
         NVL(SUM(CASE WHEN iwd + 49 = invoice_date THEN 1 ELSE 0 END),0)sun_8,
         NVL(SUM(CASE WHEN iwd + 50 = invoice_date THEN 1 ELSE 0 END),0) mon_8,
         NVL(SUM(CASE WHEN iwd + 51 = invoice_date THEN 1 ELSE 0 END),0) tue_8,
         NVL(SUM(CASE WHEN iwd + 52 = invoice_date THEN 1 ELSE 0 END),0)wed_8,
         NVL(SUM(CASE WHEN iwd + 53 = invoice_date THEN 1 ELSE 0 END),0) thu_8,
         NVL(SUM(CASE WHEN iwd + 54 = invoice_date THEN 1 ELSE 0 END),0) fri_8,
         NVL(SUM(CASE WHEN iwd + 55 = invoice_date THEN 1 ELSE 0 END),0) sat_8,
         NVL(SUM(CASE WHEN iwd + 56 = invoice_date THEN 1 ELSE 0 END),0) sun_9,
         NVL(SUM(CASE WHEN iwd + 57 = invoice_date THEN 1 ELSE 0 END),0) mon_9,
         NVL(SUM(CASE WHEN iwd + 58 = invoice_date THEN 1 ELSE 0 END),0) tue_9,
         NVL(SUM(CASE WHEN iwd + 59 = invoice_date THEN 1 ELSE 0 END),0) wed_9,
         NVL(SUM(CASE WHEN iwd + 60 = invoice_date THEN 1 ELSE 0 END),0) thu_9,
         NVL(SUM(CASE WHEN iwd + 61 = invoice_date THEN 1 ELSE 0 END),0) fri_9,
         NVL(SUM(CASE WHEN iwd + 62 = invoice_date THEN 1 ELSE 0 END),0) sat_9,
         NVL(SUM(CASE WHEN iwd + 63 = invoice_date THEN 1 ELSE 0 END),0) sun_10,
         NVL(SUM(CASE WHEN iwd + 64 = invoice_date THEN 1 ELSE 0 END),0) mon_10,
         NVL(SUM(CASE WHEN iwd + 65 = invoice_date THEN 1 ELSE 0 END),0) tue_10,
         NVL(SUM(CASE WHEN iwd + 66 = invoice_date THEN 1 ELSE 0 END),0) wed_10,
         NVL(SUM(CASE WHEN iwd + 67 = invoice_date THEN 1 ELSE 0 END),0) thu_10,
         NVL(SUM(CASE WHEN iwd + 68 = invoice_date THEN 1 ELSE 0 END),0) fri_10,
         NVL(SUM(CASE WHEN iwd + 69 = invoice_date THEN 1 ELSE 0 END),0) sat_10,
         NVL(SUM(CASE WHEN iwd + 70 = invoice_date THEN 1 ELSE 0 END),0) sun_11,
         NVL(SUM(CASE WHEN iwd + 71 = invoice_date THEN 1 ELSE 0 END),0) mon_11,
         NVL(SUM(CASE WHEN iwd + 72 = invoice_date THEN 1 ELSE 0 END),0) tue_11,
         NVL(SUM(CASE WHEN iwd + 73 = invoice_date THEN 1 ELSE 0 END),0) wed_11,
         NVL(SUM(CASE WHEN iwd + 74 = invoice_date THEN 1 ELSE 0 END),0) thu_11,
         NVL(SUM(CASE WHEN iwd + 75 = invoice_date THEN 1 ELSE 0 END),0) fri_11,
         NVL(SUM(CASE WHEN iwd + 76 = invoice_date THEN 1 ELSE 0 END),0) sat_11,
         NVL(SUM(CASE WHEN iwd + 77 = invoice_date THEN 1 ELSE 0 END),0) sun_12,
         NVL(SUM(CASE WHEN iwd + 78 = invoice_date THEN 1 ELSE 0 END),0) mon_12,
         NVL(SUM(CASE WHEN iwd + 79 = invoice_date THEN 1 ELSE 0 END),0) tue_12,
         NVL(SUM(CASE WHEN iwd + 80 = invoice_date THEN 1 ELSE 0 END),0) wed_12,
         NVL(SUM(CASE WHEN iwd + 81 = invoice_date THEN 1 ELSE 0 END),0) thu_12,
         NVL(SUM(CASE WHEN iwd + 82 = invoice_date THEN 1 ELSE 0 END),0) fri_12,
         NVL(SUM(CASE WHEN iwd + 83 = invoice_date THEN 1 ELSE 0 END),0) sat_12,
         NVL(SUM(CASE WHEN iwd + 84 = invoice_date THEN 1 ELSE 0 END),0) sun_13,
         NVL(SUM(CASE WHEN iwd + 85 = invoice_date THEN 1 ELSE 0 END),0) mon_13,
         NVL(SUM(CASE WHEN iwd + 86 = invoice_date THEN 1 ELSE 0 END),0) tue_13,
         NVL(SUM(CASE WHEN iwd + 87 = invoice_date THEN 1 ELSE 0 END),0) wed_13,
         NVL(SUM(CASE WHEN iwd + 88 = invoice_date THEN 1 ELSE 0 END),0) thu_13,
         NVL(SUM(CASE WHEN iwd + 89 = invoice_date THEN 1 ELSE 0 END),0) fri_13,
         NVL(SUM(CASE WHEN iwd + 90 = invoice_date THEN 1 ELSE 0 END),0) sat_13,
         NVL(SUM(CASE WHEN iwd + 91 = invoice_date THEN 1 ELSE 0 END),0) sun_14,
         NVL(SUM(CASE WHEN iwd + 92 = invoice_date THEN 1 ELSE 0 END),0) mon_14,
         NVL(SUM(CASE WHEN iwd + 93 = invoice_date THEN 1 ELSE 0 END),0) tue_14,
         NVL(SUM(CASE WHEN iwd + 94 = invoice_date THEN 1 ELSE 0 END),0) wed_14,
         NVL(SUM(CASE WHEN iwd + 95 = invoice_date THEN 1 ELSE 0 END),0) thu_14,
         NVL(SUM(CASE WHEN iwd + 96 = invoice_date THEN 1 ELSE 0 END),0) fri_14,
         NVL(SUM(CASE WHEN iwd + 97 = invoice_date THEN 1 ELSE 0 END),0) sat_14,
         NVL(SUM(CASE WHEN iwd + 98 = invoice_date THEN 1 ELSE 0 END),0) sun_15,
         NVL(SUM(CASE WHEN iwd + 99 = invoice_date THEN 1 ELSE 0 END),0) mon_15
    FROM (SELECT TO_CHAR(TRUNC(ih.invoice_date, 'mm'), 'yyyymm') ym,
                 NEXT_DAY(TRUNC(TO_DATE( date_from, 'DD-MON-YYYY'), 'mm'), 'Sun') iwd,
                 CASE
                     WHEN vp.inv_product_type = 'RBC' THEN 'Red Blood Cells'
                     WHEN vp.inv_product_type = 'LRBC' THEN 'Leukoreduced Red Blood Cells'
                     WHEN vp.inv_product_type = 'LPHER' THEN 'SDP'
                     WHEN vp.inv_product_type = 'PHER' THEN 'SDP'
                     WHEN vp.inv_product_type = 'FFP' THEN 'Fresh Frozen Plasma'
                     WHEN vp.inv_product_type = 'FP24' THEN 'Plasma Frozen withing 24hrs'
                     WHEN vp.inv_product_type = 'CRYO' THEN 'Cryoprecipitate'
                 END
                     inv_product_type,
                 ih.customer_id,
                 id.patient_name,
                 ih.invoice_date
            FROM tab1 ih,
                 tab2 id,
                 tab4 ip,
                 tab3 vp
           WHERE id.invoice_number = ih.invoice_number
             AND id.item_id = vp.product_code
             AND id.item_id = ip.item_id
             AND ip.item_type IN ('P')
             --AND ih.customer_id = 'WAD-EX0128'
             AND ih.invoice_date >= TO_DATE( ym, 'YYYYMM')
             AND ih.invoice_date < ADD_MONTHS(TO_DATE( ym, 'YYYYMM'), 3)
             AND vp.inv_product_type IN ('RBC',
                                         'LRBC',
                                         'LPHER',
                                         'PHER',
                                         'FFP',
                                         'FP24',
                                         'CRYO'))
GROUP BY inv_product_type, customer_id, patient_name
ORDER BY inv_product_type, customer_id, patient_name;
  VARC1 C1%ROWTYPE;
BEGIN

v_location_i := 1000;

  --FILENAME1   := 'hosp_usage' || '_' || SYSDATE || '.CSV';
  FILENAME1   := 'hosp_usage' || '_' || SYSDATE || '.xls';
v_location_i := 2000;
  FILENAME    := UTL_FILE.FOPEN('HOSP_USAGE_DIR', FILENAME1, 'W');

  /* THIS WILL CREATE THE HEADING IN EXCEL SHEET */
  v_location_i := 3000;
    UTL_FILE.PUT_LINE(FILENAME, 'INV PRODUCT TYPE'||chr(9)||'CUSTOMER ID'||chr(9)||'NAME'||chr(9)||
             'SUN1'||chr(9)||'MON1'||chr(9)||'TUE1'||chr(9)||'WED1'||chr(9)||'THU1'||chr(9)||'FRI1'||chr(9)||'SAT1'||chr(9)||
			 'SUN2'||chr(9)||'MON2'||chr(9)||'TUE2'||chr(9)||'WED2'||chr(9)||'THU2'||chr(9)||'FRI2'||chr(9)||'SAT2'||chr(9)||
			 'SUN3'||chr(9)||'MON3'||chr(9)||'TUE3'||chr(9)||'WED3'||chr(9)||'THU3'||chr(9)||'FRI3'||chr(9)||'SAT3'||chr(9)||
			 'SUN4'||chr(9)||'MON4'||chr(9)||'TUE4'||chr(9)||'WED4'||chr(9)||'THU4'||chr(9)||'FRI4'||chr(9)||'SAT4'||chr(9)||
			 'SUN5'||chr(9)||'MON5'||chr(9)||'TUE5'||chr(9)||'WED5'||chr(9)||'THU5'||chr(9)||'FRI5'||chr(9)||'SAT5'||chr(9)||
			 'SUN6'||chr(9)||'MON6'||chr(9)||'TUE6'||chr(9)||'WED6'||chr(9)||'THU6'||chr(9)||'FRI6'||chr(9)||'SAT6'||chr(9)||
			 'SUN7'||chr(9)||'MON7'||chr(9)||'TUE7'||chr(9)||'WED7'||chr(9)||'THU7'||chr(9)||'FRI7'||chr(9)||'SAT7'||chr(9)||
			 'SUN8'||chr(9)||'MON8'||chr(9)||'TUE8'||chr(9)||'WED8'||chr(9)||'THU8'||chr(9)||'FRI8'||chr(9)||'SAT8'||chr(9)||
			 'SUN9'||chr(9)||'MON9'||chr(9)||'TUE9'||chr(9)||'WED9'||chr(9)||'THU9'||chr(9)||'FRI9'||chr(9)||'SAT9'||chr(9)||
			 'SUN10'||chr(9)||'MON10'||chr(9)||'TUE10'||chr(9)||'WED10'||chr(9)||'THU10'||chr(9)||'FRI10'||chr(9)||'SAT10'||chr(9)||
			 'SUN11'||chr(9)||'MON11'||chr(9)||'TUE11'||chr(9)||'WED11'||chr(9)||'THU11'||chr(9)||'FRI11'||chr(9)||'SAT11'||chr(9)||
			 'SUN12'||chr(9)||'MON12'||chr(9)||'TUE12'||chr(9)||'WED12'||chr(9)||'THU12'||chr(9)||'FRI12'||chr(9)||'SAT12'||chr(9)||
			 'SUN13'||chr(9)||'MON13'||chr(9)||'TUE13'||chr(9)||'WED13'||chr(9)||'THU13'||chr(9)||'FRI13'||chr(9)||'SAT13'||chr(9)||
			 'SUN14'||chr(9)||'MON14'||chr(9)||'TUE14'||chr(9)||'WED14'||chr(9)||'THU14'||chr(9)||'FRI14'||chr(9)||'SAT14'||chr(9)||
			 'SUN15'||chr(9)||'MON15');
	

  OPEN C1;
  v_location_i := 4000;
  LOOP
    FETCH C1
       INTO VARC1;
	   v_location_i := 5000;
    EXIT WHEN C1%NOTFOUND;
     /*  THIS WILL PRINT THE RECORDS IN EXCEL SHEET AS PER THE QUERY IN CURSOR */
   v_location_i := 6000;
    UTL_FILE.PUT_LINE(FILENAME,VARC1.inv_product_type||chr(9)||VARC1.customer_id||chr(9)||VARC1.patient_name||chr(9)||
	               VARC1.sun_1||chr(9)||VARC1.mon_1||chr(9)||VARC1.tue_1||chr(9)||VARC1.wed_1||chr(9)||VARC1.thu_1||chr(9)||VARC1.fri_1||chr(9)||VARC1.sat_1||chr(9)||
        		   VARC1.sun_2||chr(9)||VARC1.mon_2||chr(9)||VARC1.tue_2||chr(9)||VARC1.wed_2||chr(9)||VARC1.thu_2||chr(9)||VARC1.fri_2||chr(9)||VARC1.sat_2||chr(9)||
				   VARC1.sun_3||chr(9)||VARC1.mon_3||chr(9)||VARC1.tue_3||chr(9)||VARC1.wed_3||chr(9)||VARC1.thu_3||chr(9)||VARC1.fri_3||chr(9)||VARC1.sat_3||chr(9)||
				   VARC1.sun_4||chr(9)||VARC1.mon_4||chr(9)||VARC1.tue_4||chr(9)||VARC1.wed_4||chr(9)||VARC1.thu_4||chr(9)||VARC1.fri_4||chr(9)||VARC1.sat_4||chr(9)||
				   VARC1.sun_5||chr(9)||VARC1.mon_5||chr(9)||VARC1.tue_5||chr(9)||VARC1.wed_5||chr(9)||VARC1.thu_5||chr(9)||VARC1.fri_5||chr(9)||VARC1.sat_5||chr(9)||
				   VARC1.sun_6||chr(9)||VARC1.mon_6||chr(9)||VARC1.tue_6||chr(9)||VARC1.wed_6||chr(9)||VARC1.thu_6||chr(9)||VARC1.fri_6||chr(9)||VARC1.sat_6||chr(9)||
				   VARC1.sun_7||chr(9)||VARC1.mon_7||chr(9)||VARC1.tue_7||chr(9)||VARC1.wed_7||chr(9)||VARC1.thu_7||chr(9)||VARC1.fri_7||chr(9)||VARC1.sat_7||chr(9)||
				   VARC1.sun_8||chr(9)||VARC1.mon_8||chr(9)||VARC1.tue_8||chr(9)||VARC1.wed_8||chr(9)||VARC1.thu_8||chr(9)||VARC1.fri_8||chr(9)||VARC1.sat_8||chr(9)||
				   VARC1.sun_9||chr(9)||VARC1.mon_9||chr(9)||VARC1.tue_9||chr(9)||VARC1.wed_9||chr(9)||VARC1.thu_9||chr(9)||VARC1.fri_9||chr(9)||VARC1.sat_9||chr(9)||
				   VARC1.sun_10||chr(9)||VARC1.mon_10||chr(9)||VARC1.tue_10||chr(9)||VARC1.wed_10||chr(9)||VARC1.thu_10||chr(9)||VARC1.fri_10||chr(9)||VARC1.sat_10||chr(9)||
				   VARC1.sun_11||chr(9)||VARC1.mon_11||chr(9)||VARC1.tue_11||chr(9)||VARC1.wed_11||chr(9)||VARC1.thu_11||chr(9)||VARC1.fri_11||chr(9)||VARC1.sat_11||chr(9)||
				   VARC1.sun_12||chr(9)||VARC1.mon_12||chr(9)||VARC1.tue_12||chr(9)||VARC1.wed_12||chr(9)||VARC1.thu_12||chr(9)||VARC1.fri_12||chr(9)||VARC1.sat_12||chr(9)||
				   VARC1.sun_13||chr(9)||VARC1.mon_13||chr(9)||VARC1.tue_13||chr(9)||VARC1.wed_13||chr(9)||VARC1.thu_13||chr(9)||VARC1.fri_13||chr(9)||VARC1.sat_13||chr(9)||
				   VARC1.sun_14||chr(9)||VARC1.mon_14||chr(9)||VARC1.tue_14||chr(9)||VARC1.wed_14||chr(9)||VARC1.thu_14||chr(9)||VARC1.fri_14||chr(9)||VARC1.sat_14||chr(9)||
				   VARC1.sun_15||chr(9)||VARC1.mon_15);
	

  END LOOP;
v_location_i := 7000;
  UTL_FILE.FCLOSE(FILENAME);
 v_location_i := 8000; 
EXCEPTION
     WHEN OTHERS THEN
      dbms_output.put_line('Error in ' || v_procedure_c ||
                           ' at line: ' || v_location_i); 

END load_hosp_usage;
/

Open in new window

LVL 6
anumosesAsked:
Who is Participating?
 
sdstuberCommented:
declare a variable to hold the header and then use the header query from the previous question

here's a self-contained example of what I mean,  just copy the variable declaration and the select into your code

and print the variable with utl_file


DECLARE
    v_header VARCHAR2(32767);
BEGIN
    SELECT REPLACE(
               'INV_PRODUCT_TYPE' || ',' || 'CUSTOMER_ID' || ',' || 'PATIENT_NAME' || ',' || days,
               ',',
               CHR(9)
           )
               data
      INTO v_header
      FROM (    SELECT SUBSTR(
                           SYS_CONNECT_BY_PATH(
                               TO_CHAR(
                                   NEXT_DAY(TO_DATE( :ym, 'YYYYMM') - 1, 'Sun') + LEVEL - 1,
                                   'dd-Mon-yyyy'
                               ),
                               ','
                           ),
                           2
                       )
                           days
                  FROM DUAL
            CONNECT BY LEVEL <= 12 * 7
              ORDER BY LEVEL DESC)
     WHERE ROWNUM = 1;

    DBMS_OUTPUT.put_line(v_header);
END

Open in new window

;
0
 
slightwv (䄆 Netminder) Commented:
Can you post the link to the previous question?  What you posted wasn't a question link.

What is the error?

Also:  You really shouldn't create a tab separated file and name it with a .XLS extension.  Excel may be able to automatically convert it on the fly but it can case problems with the users when they go to save it since it is still basically a TXT file not a binary XLS file.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
anumosesAuthor Commented:
Modified the routine. No errors compiled fine. But the dates are not displayed. What was my mistake?

CREATE OR REPLACE PROCEDURE load_hosp_usage_05072015 (date_from IN VARCHAR,
                                                   ym IN VARCHAR)  AS

   v_header VARCHAR2(32767);
  FILENAME  UTL_FILE.FILE_TYPE;
  FILENAME1 VARCHAR2(10000);
  v_procedure_c constant varchar2(30) := 'load_hosp_usage';
  v_location_i integer;
	
	
  CURSOR C1 IS
    SELECT inv_product_type,
         customer_id,
         patient_name,
         SUM(
             CASE
                 WHEN iwd = TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd = invoice_date THEN 1 ELSE 0 END
             END
         )
             sun_1,
         SUM(
             CASE
                 WHEN iwd + 1 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 1 = invoice_date THEN 1 ELSE 0 END
             END
         )
             mon_1,
         SUM(
             CASE
                 WHEN iwd + 2 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 2 = invoice_date THEN 1 ELSE 0 END
             END
         )
             tue_1,
         SUM(
             CASE
                 WHEN iwd + 3 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 3 = invoice_date THEN 1 ELSE 0 END
             END
         )
             wed_1,
         SUM(
             CASE
                 WHEN iwd + 4 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 4 = invoice_date THEN 1 ELSE 0 END
             END
         )
             thu_1,
         SUM(
             CASE
                 WHEN iwd + 5 >= TRUNC(invoice_date, 'mm')
                 THEN
                     CASE WHEN iwd + 5 = invoice_date THEN 1 ELSE 0 END
             END
         )
             fri_1,
         NVL(SUM(CASE WHEN iwd + 6 = invoice_date THEN 1 ELSE 0 END),0) sat_1,
         NVL(SUM(CASE WHEN iwd + 7 = invoice_date THEN 1 ELSE 0 END),0) sun_2,
         NVL(SUM(CASE WHEN iwd + 8 = invoice_date THEN 1 ELSE 0 END),0) mon_2,
         NVL(SUM(CASE WHEN iwd + 9 = invoice_date THEN 1 ELSE 0 END),0) tue_2,
         NVL(SUM(CASE WHEN iwd + 10 = invoice_date THEN 1 ELSE 0 END),0) wed_2,
         NVL(SUM(CASE WHEN iwd + 11 = invoice_date THEN 1 ELSE 0 END),0) thu_2,
         NVL(SUM(CASE WHEN iwd + 12 = invoice_date THEN 1 ELSE 0 END),0) fri_2,
         NVL(SUM(CASE WHEN iwd + 13 = invoice_date THEN 1 ELSE 0 END),0) sat_2,
         NVL(SUM(CASE WHEN iwd + 14 = invoice_date THEN 1 ELSE 0 END),0) sun_3,
         NVL(SUM(CASE WHEN iwd + 15 = invoice_date THEN 1 ELSE 0 END),0) mon_3,
         NVL(SUM(CASE WHEN iwd + 16 = invoice_date THEN 1 ELSE 0 END),0) tue_3,
         NVL(SUM(CASE WHEN iwd + 17 = invoice_date THEN 1 ELSE 0 END),0) wed_3,
         NVL(SUM(CASE WHEN iwd + 18 = invoice_date THEN 1 ELSE 0 END),0) thu_3,
         NVL(SUM(CASE WHEN iwd + 19 = invoice_date THEN 1 ELSE 0 END),0) fri_3,
         NVL(SUM(CASE WHEN iwd + 20 = invoice_date THEN 1 ELSE 0 END),0) sat_3,
         NVL(SUM(CASE WHEN iwd + 21 = invoice_date THEN 1 ELSE 0 END),0) sun_4,
         NVL(SUM(CASE WHEN iwd + 22 = invoice_date THEN 1 ELSE 0 END),0) mon_4,
         NVL(SUM(CASE WHEN iwd + 23 = invoice_date THEN 1 ELSE 0 END),0) tue_4,
         NVL(SUM(CASE WHEN iwd + 24 = invoice_date THEN 1 ELSE 0 END),0)wed_4,
         NVL(SUM(CASE WHEN iwd + 25 = invoice_date THEN 1 ELSE 0 END),0) thu_4,
         NVL(SUM(CASE WHEN iwd + 26 = invoice_date THEN 1 ELSE 0 END),0) fri_4,
         NVL(SUM(CASE WHEN iwd + 27 = invoice_date THEN 1 ELSE 0 END),0) sat_4,
         NVL(SUM(CASE WHEN iwd + 28 = invoice_date THEN 1 ELSE 0 END),0) sun_5,
         NVL(SUM(CASE WHEN iwd + 29 = invoice_date THEN 1 ELSE 0 END),0) mon_5,
         NVL(SUM(CASE WHEN iwd + 30 = invoice_date THEN 1 ELSE 0 END),0) tue_5,
         NVL(SUM(CASE WHEN iwd + 31 = invoice_date THEN 1 ELSE 0 END),0) wed_5,
         NVL(SUM(CASE WHEN iwd + 32 = invoice_date THEN 1 ELSE 0 END),0) thu_5,
         NVL(SUM(CASE WHEN iwd + 33 = invoice_date THEN 1 ELSE 0 END),0) fri_5,
         NVL(SUM(CASE WHEN iwd + 34 = invoice_date THEN 1 ELSE 0 END),0) sat_5,
         NVL(SUM(CASE WHEN iwd + 35 = invoice_date THEN 1 ELSE 0 END),0) sun_6,
         NVL(SUM(CASE WHEN iwd + 36 = invoice_date THEN 1 ELSE 0 END),0) mon_6,
         NVL(SUM(CASE WHEN iwd + 37 = invoice_date THEN 1 ELSE 0 END),0) tue_6,
         NVL(SUM(CASE WHEN iwd + 38 = invoice_date THEN 1 ELSE 0 END),0) wed_6,
         NVL(SUM(CASE WHEN iwd + 39 = invoice_date THEN 1 ELSE 0 END),0) thu_6,
         NVL(SUM(CASE WHEN iwd + 40 = invoice_date THEN 1 ELSE 0 END),0) fri_6,
         NVL(SUM(CASE WHEN iwd + 41 = invoice_date THEN 1 ELSE 0 END),0) sat_6,
         NVL(SUM(CASE WHEN iwd + 42 = invoice_date THEN 1 ELSE 0 END),0) sun_7,
         NVL(SUM(CASE WHEN iwd + 43 = invoice_date THEN 1 ELSE 0 END),0) mon_7,
         NVL(SUM(CASE WHEN iwd + 44 = invoice_date THEN 1 ELSE 0 END),0) tue_7,
         NVL(SUM(CASE WHEN iwd + 45 = invoice_date THEN 1 ELSE 0 END),0) wed_7,
         NVL(SUM(CASE WHEN iwd + 46 = invoice_date THEN 1 ELSE 0 END),0) thu_7,
         NVL(SUM(CASE WHEN iwd + 47 = invoice_date THEN 1 ELSE 0 END),0) fri_7,
         NVL(SUM(CASE WHEN iwd + 48 = invoice_date THEN 1 ELSE 0 END),0) sat_7,
         NVL(SUM(CASE WHEN iwd + 49 = invoice_date THEN 1 ELSE 0 END),0)sun_8,
         NVL(SUM(CASE WHEN iwd + 50 = invoice_date THEN 1 ELSE 0 END),0) mon_8,
         NVL(SUM(CASE WHEN iwd + 51 = invoice_date THEN 1 ELSE 0 END),0) tue_8,
         NVL(SUM(CASE WHEN iwd + 52 = invoice_date THEN 1 ELSE 0 END),0)wed_8,
         NVL(SUM(CASE WHEN iwd + 53 = invoice_date THEN 1 ELSE 0 END),0) thu_8,
         NVL(SUM(CASE WHEN iwd + 54 = invoice_date THEN 1 ELSE 0 END),0) fri_8,
         NVL(SUM(CASE WHEN iwd + 55 = invoice_date THEN 1 ELSE 0 END),0) sat_8,
         NVL(SUM(CASE WHEN iwd + 56 = invoice_date THEN 1 ELSE 0 END),0) sun_9,
         NVL(SUM(CASE WHEN iwd + 57 = invoice_date THEN 1 ELSE 0 END),0) mon_9,
         NVL(SUM(CASE WHEN iwd + 58 = invoice_date THEN 1 ELSE 0 END),0) tue_9,
         NVL(SUM(CASE WHEN iwd + 59 = invoice_date THEN 1 ELSE 0 END),0) wed_9,
         NVL(SUM(CASE WHEN iwd + 60 = invoice_date THEN 1 ELSE 0 END),0) thu_9,
         NVL(SUM(CASE WHEN iwd + 61 = invoice_date THEN 1 ELSE 0 END),0) fri_9,
         NVL(SUM(CASE WHEN iwd + 62 = invoice_date THEN 1 ELSE 0 END),0) sat_9,
         NVL(SUM(CASE WHEN iwd + 63 = invoice_date THEN 1 ELSE 0 END),0) sun_10,
         NVL(SUM(CASE WHEN iwd + 64 = invoice_date THEN 1 ELSE 0 END),0) mon_10,
         NVL(SUM(CASE WHEN iwd + 65 = invoice_date THEN 1 ELSE 0 END),0) tue_10,
         NVL(SUM(CASE WHEN iwd + 66 = invoice_date THEN 1 ELSE 0 END),0) wed_10,
         NVL(SUM(CASE WHEN iwd + 67 = invoice_date THEN 1 ELSE 0 END),0) thu_10,
         NVL(SUM(CASE WHEN iwd + 68 = invoice_date THEN 1 ELSE 0 END),0) fri_10,
         NVL(SUM(CASE WHEN iwd + 69 = invoice_date THEN 1 ELSE 0 END),0) sat_10,
         NVL(SUM(CASE WHEN iwd + 70 = invoice_date THEN 1 ELSE 0 END),0) sun_11,
         NVL(SUM(CASE WHEN iwd + 71 = invoice_date THEN 1 ELSE 0 END),0) mon_11,
         NVL(SUM(CASE WHEN iwd + 72 = invoice_date THEN 1 ELSE 0 END),0) tue_11,
         NVL(SUM(CASE WHEN iwd + 73 = invoice_date THEN 1 ELSE 0 END),0) wed_11,
         NVL(SUM(CASE WHEN iwd + 74 = invoice_date THEN 1 ELSE 0 END),0) thu_11,
         NVL(SUM(CASE WHEN iwd + 75 = invoice_date THEN 1 ELSE 0 END),0) fri_11,
         NVL(SUM(CASE WHEN iwd + 76 = invoice_date THEN 1 ELSE 0 END),0) sat_11,
         NVL(SUM(CASE WHEN iwd + 77 = invoice_date THEN 1 ELSE 0 END),0) sun_12,
         NVL(SUM(CASE WHEN iwd + 78 = invoice_date THEN 1 ELSE 0 END),0) mon_12,
         NVL(SUM(CASE WHEN iwd + 79 = invoice_date THEN 1 ELSE 0 END),0) tue_12,
         NVL(SUM(CASE WHEN iwd + 80 = invoice_date THEN 1 ELSE 0 END),0) wed_12,
         NVL(SUM(CASE WHEN iwd + 81 = invoice_date THEN 1 ELSE 0 END),0) thu_12,
         NVL(SUM(CASE WHEN iwd + 82 = invoice_date THEN 1 ELSE 0 END),0) fri_12,
         NVL(SUM(CASE WHEN iwd + 83 = invoice_date THEN 1 ELSE 0 END),0) sat_12,
         NVL(SUM(CASE WHEN iwd + 84 = invoice_date THEN 1 ELSE 0 END),0) sun_13,
         NVL(SUM(CASE WHEN iwd + 85 = invoice_date THEN 1 ELSE 0 END),0) mon_13,
         NVL(SUM(CASE WHEN iwd + 86 = invoice_date THEN 1 ELSE 0 END),0) tue_13,
         NVL(SUM(CASE WHEN iwd + 87 = invoice_date THEN 1 ELSE 0 END),0) wed_13,
         NVL(SUM(CASE WHEN iwd + 88 = invoice_date THEN 1 ELSE 0 END),0) thu_13,
         NVL(SUM(CASE WHEN iwd + 89 = invoice_date THEN 1 ELSE 0 END),0) fri_13,
         NVL(SUM(CASE WHEN iwd + 90 = invoice_date THEN 1 ELSE 0 END),0) sat_13,
         NVL(SUM(CASE WHEN iwd + 91 = invoice_date THEN 1 ELSE 0 END),0) sun_14,
         NVL(SUM(CASE WHEN iwd + 92 = invoice_date THEN 1 ELSE 0 END),0) mon_14,
         NVL(SUM(CASE WHEN iwd + 93 = invoice_date THEN 1 ELSE 0 END),0) tue_14,
         NVL(SUM(CASE WHEN iwd + 94 = invoice_date THEN 1 ELSE 0 END),0) wed_14,
         NVL(SUM(CASE WHEN iwd + 95 = invoice_date THEN 1 ELSE 0 END),0) thu_14,
         NVL(SUM(CASE WHEN iwd + 96 = invoice_date THEN 1 ELSE 0 END),0) fri_14,
         NVL(SUM(CASE WHEN iwd + 97 = invoice_date THEN 1 ELSE 0 END),0) sat_14,
         NVL(SUM(CASE WHEN iwd + 98 = invoice_date THEN 1 ELSE 0 END),0) sun_15,
         NVL(SUM(CASE WHEN iwd + 99 = invoice_date THEN 1 ELSE 0 END),0) mon_15
    FROM (SELECT TO_CHAR(TRUNC(ih.invoice_date, 'mm'), 'yyyymm') ym,
                 NEXT_DAY(TRUNC(TO_DATE( date_from, 'DD-MON-YYYY'), 'mm'), 'Sun') iwd,
                 CASE
                     WHEN vp.inv_product_type = 'RBC' THEN 'Red Blood Cells'
                     WHEN vp.inv_product_type = 'LRBC' THEN 'Leukoreduced Red Blood Cells'
                     WHEN vp.inv_product_type = 'LPHER' THEN 'SDP'
                     WHEN vp.inv_product_type = 'PHER' THEN 'SDP'
                     WHEN vp.inv_product_type = 'FFP' THEN 'Fresh Frozen Plasma'
                     WHEN vp.inv_product_type = 'FP24' THEN 'Plasma Frozen withing 24hrs'
                     WHEN vp.inv_product_type = 'CRYO' THEN 'Cryoprecipitate'
                 END
                     inv_product_type,
                 ih.customer_id,
                 id.patient_name,
                 ih.invoice_date
            FROM tab1 ih,
                 tab2 id,
                 tab4 ip,
                 tab3 vp
           WHERE id.invoice_number = ih.invoice_number
             AND id.item_id = vp.product_code
             AND id.item_id = ip.item_id
             AND ip.item_type IN ('P')
             --AND ih.customer_id = 'WAD-EX0128'
             AND ih.invoice_date >= TO_DATE( ym, 'YYYYMM')
             AND ih.invoice_date < ADD_MONTHS(TO_DATE( ym, 'YYYYMM'), 3)
             AND vp.inv_product_type IN ('RBC',
                                         'LRBC',
                                         'LPHER',
                                         'PHER',
                                         'FFP',
                                         'FP24',
                                         'CRYO'))
GROUP BY inv_product_type, customer_id, patient_name
ORDER BY inv_product_type, customer_id, patient_name;
  VARC1 C1%ROWTYPE;
BEGIN
DECLARE
    v_header VARCHAR2(32767);
BEGIN
    SELECT REPLACE(
               'INV_PRODUCT_TYPE' || ',' || 'CUSTOMER_ID' || ',' || 'PATIENT_NAME' || ',' || days,
               ',',
               CHR(9)
           )
               data
      INTO v_header
      FROM (    SELECT SUBSTR(
                           SYS_CONNECT_BY_PATH(
                               TO_CHAR(
                                   NEXT_DAY(TO_DATE( ym, 'YYYYMM') - 1, 'Sun') + LEVEL - 1,
                                   'dd-Mon-yyyy'
                               ),
                               ','
                           ),
                           2
                       )
                           days
                  FROM DUAL
            CONNECT BY LEVEL <= 12 * 7
              ORDER BY LEVEL DESC)
     WHERE ROWNUM = 1;

   -- DBMS_OUTPUT.put_line(v_header);
END;
v_location_i := 1000;

  --FILENAME1   := 'hosp_usage' || '_' || SYSDATE || '.CSV';
  FILENAME1   := 'hosp_usage' || '_' || SYSDATE || '.xls';
v_location_i := 2000;
  FILENAME    := UTL_FILE.FOPEN('HOSP_USAGE_DIR', FILENAME1, 'W');

  /* THIS WILL CREATE THE HEADING IN EXCEL SHEET */
  v_location_i := 3000;
    UTL_FILE.PUT_LINE(FILENAME, 'INV PRODUCT TYPE'||chr(9)||'CUSTOMER ID'||chr(9)||'NAME'||chr(9)||V_HEADER);
             /*'SUN1'||chr(9)||'MON1'||chr(9)||'TUE1'||chr(9)||'WED1'||chr(9)||'THU1'||chr(9)||'FRI1'||chr(9)||'SAT1'||chr(9)||
			 'SUN2'||chr(9)||'MON2'||chr(9)||'TUE2'||chr(9)||'WED2'||chr(9)||'THU2'||chr(9)||'FRI2'||chr(9)||'SAT2'||chr(9)||
			 'SUN3'||chr(9)||'MON3'||chr(9)||'TUE3'||chr(9)||'WED3'||chr(9)||'THU3'||chr(9)||'FRI3'||chr(9)||'SAT3'||chr(9)||
			 'SUN4'||chr(9)||'MON4'||chr(9)||'TUE4'||chr(9)||'WED4'||chr(9)||'THU4'||chr(9)||'FRI4'||chr(9)||'SAT4'||chr(9)||
			 'SUN5'||chr(9)||'MON5'||chr(9)||'TUE5'||chr(9)||'WED5'||chr(9)||'THU5'||chr(9)||'FRI5'||chr(9)||'SAT5'||chr(9)||
			 'SUN6'||chr(9)||'MON6'||chr(9)||'TUE6'||chr(9)||'WED6'||chr(9)||'THU6'||chr(9)||'FRI6'||chr(9)||'SAT6'||chr(9)||
			 'SUN7'||chr(9)||'MON7'||chr(9)||'TUE7'||chr(9)||'WED7'||chr(9)||'THU7'||chr(9)||'FRI7'||chr(9)||'SAT7'||chr(9)||
			 'SUN8'||chr(9)||'MON8'||chr(9)||'TUE8'||chr(9)||'WED8'||chr(9)||'THU8'||chr(9)||'FRI8'||chr(9)||'SAT8'||chr(9)||
			 'SUN9'||chr(9)||'MON9'||chr(9)||'TUE9'||chr(9)||'WED9'||chr(9)||'THU9'||chr(9)||'FRI9'||chr(9)||'SAT9'||chr(9)||
			 'SUN10'||chr(9)||'MON10'||chr(9)||'TUE10'||chr(9)||'WED10'||chr(9)||'THU10'||chr(9)||'FRI10'||chr(9)||'SAT10'||chr(9)||
			 'SUN11'||chr(9)||'MON11'||chr(9)||'TUE11'||chr(9)||'WED11'||chr(9)||'THU11'||chr(9)||'FRI11'||chr(9)||'SAT11'||chr(9)||
			 'SUN12'||chr(9)||'MON12'||chr(9)||'TUE12'||chr(9)||'WED12'||chr(9)||'THU12'||chr(9)||'FRI12'||chr(9)||'SAT12'||chr(9)||
			 'SUN13'||chr(9)||'MON13'||chr(9)||'TUE13'||chr(9)||'WED13'||chr(9)||'THU13'||chr(9)||'FRI13'||chr(9)||'SAT13'||chr(9)||
			 'SUN14'||chr(9)||'MON14'||chr(9)||'TUE14'||chr(9)||'WED14'||chr(9)||'THU14'||chr(9)||'FRI14'||chr(9)||'SAT14'||chr(9)||
			 'SUN15'||chr(9)||'MON15');*/
	

  OPEN C1;
  v_location_i := 4000;
  LOOP
    FETCH C1
       INTO VARC1;
	   v_location_i := 5000;
    EXIT WHEN C1%NOTFOUND;
     /*  THIS WILL PRINT THE RECORDS IN EXCEL SHEET AS PER THE QUERY IN CURSOR */
   v_location_i := 6000;
    UTL_FILE.PUT_LINE(FILENAME,VARC1.inv_product_type||chr(9)||VARC1.customer_id||chr(9)||VARC1.patient_name||chr(9)||
	               VARC1.sun_1||chr(9)||VARC1.mon_1||chr(9)||VARC1.tue_1||chr(9)||VARC1.wed_1||chr(9)||VARC1.thu_1||chr(9)||VARC1.fri_1||chr(9)||VARC1.sat_1||chr(9)||
        		   VARC1.sun_2||chr(9)||VARC1.mon_2||chr(9)||VARC1.tue_2||chr(9)||VARC1.wed_2||chr(9)||VARC1.thu_2||chr(9)||VARC1.fri_2||chr(9)||VARC1.sat_2||chr(9)||
				   VARC1.sun_3||chr(9)||VARC1.mon_3||chr(9)||VARC1.tue_3||chr(9)||VARC1.wed_3||chr(9)||VARC1.thu_3||chr(9)||VARC1.fri_3||chr(9)||VARC1.sat_3||chr(9)||
				   VARC1.sun_4||chr(9)||VARC1.mon_4||chr(9)||VARC1.tue_4||chr(9)||VARC1.wed_4||chr(9)||VARC1.thu_4||chr(9)||VARC1.fri_4||chr(9)||VARC1.sat_4||chr(9)||
				   VARC1.sun_5||chr(9)||VARC1.mon_5||chr(9)||VARC1.tue_5||chr(9)||VARC1.wed_5||chr(9)||VARC1.thu_5||chr(9)||VARC1.fri_5||chr(9)||VARC1.sat_5||chr(9)||
				   VARC1.sun_6||chr(9)||VARC1.mon_6||chr(9)||VARC1.tue_6||chr(9)||VARC1.wed_6||chr(9)||VARC1.thu_6||chr(9)||VARC1.fri_6||chr(9)||VARC1.sat_6||chr(9)||
				   VARC1.sun_7||chr(9)||VARC1.mon_7||chr(9)||VARC1.tue_7||chr(9)||VARC1.wed_7||chr(9)||VARC1.thu_7||chr(9)||VARC1.fri_7||chr(9)||VARC1.sat_7||chr(9)||
				   VARC1.sun_8||chr(9)||VARC1.mon_8||chr(9)||VARC1.tue_8||chr(9)||VARC1.wed_8||chr(9)||VARC1.thu_8||chr(9)||VARC1.fri_8||chr(9)||VARC1.sat_8||chr(9)||
				   VARC1.sun_9||chr(9)||VARC1.mon_9||chr(9)||VARC1.tue_9||chr(9)||VARC1.wed_9||chr(9)||VARC1.thu_9||chr(9)||VARC1.fri_9||chr(9)||VARC1.sat_9||chr(9)||
				   VARC1.sun_10||chr(9)||VARC1.mon_10||chr(9)||VARC1.tue_10||chr(9)||VARC1.wed_10||chr(9)||VARC1.thu_10||chr(9)||VARC1.fri_10||chr(9)||VARC1.sat_10||chr(9)||
				   VARC1.sun_11||chr(9)||VARC1.mon_11||chr(9)||VARC1.tue_11||chr(9)||VARC1.wed_11||chr(9)||VARC1.thu_11||chr(9)||VARC1.fri_11||chr(9)||VARC1.sat_11||chr(9)||
				   VARC1.sun_12||chr(9)||VARC1.mon_12||chr(9)||VARC1.tue_12||chr(9)||VARC1.wed_12||chr(9)||VARC1.thu_12||chr(9)||VARC1.fri_12||chr(9)||VARC1.sat_12||chr(9)||
				   VARC1.sun_13||chr(9)||VARC1.mon_13||chr(9)||VARC1.tue_13||chr(9)||VARC1.wed_13||chr(9)||VARC1.thu_13||chr(9)||VARC1.fri_13||chr(9)||VARC1.sat_13||chr(9)||
				   VARC1.sun_14||chr(9)||VARC1.mon_14||chr(9)||VARC1.tue_14||chr(9)||VARC1.wed_14||chr(9)||VARC1.thu_14||chr(9)||VARC1.fri_14||chr(9)||VARC1.sat_14||chr(9)||
				   VARC1.sun_15||chr(9)||VARC1.mon_15);
	

  END LOOP;
v_location_i := 7000;
  UTL_FILE.FCLOSE(FILENAME);
 v_location_i := 8000; 
EXCEPTION
     WHEN OTHERS THEN
      dbms_output.put_line('Error in ' || v_procedure_c ||
                           ' at line: ' || v_location_i); 

END load_hosp_usage_05072015;
/

Open in new window

hosp-usage-07-MAY-15.xls
0
 
sdstuberCommented:
you didn't copy the declare and query,  you copied the entire block.
so you had 2 declarations of v_header,  one in your procedure, and another inside the block,  so the 2nd one was populated, the other was never touched, so you printed null.

Also, had the non-date column headers in the output and in the query, so if it had printed, it would have done so twice.

try this


CREATE OR REPLACE PROCEDURE load_hosp_usage_05072015(date_from IN VARCHAR, ym IN VARCHAR)
AS
    v_header               VARCHAR2(32767);
    filename               UTL_FILE.file_type;
    filename1              VARCHAR2(10000);
    v_procedure_c CONSTANT VARCHAR2(30) := 'load_hosp_usage';
    v_location_i           INTEGER;

    CURSOR c1
    IS
          SELECT inv_product_type,
                 customer_id,
                 patient_name,
                 SUM(
                     CASE
                         WHEN iwd = TRUNC(invoice_date, 'mm')
                         THEN
                             CASE WHEN iwd = invoice_date THEN 1 ELSE 0 END
                     END
                 )
                     sun_1,
                 SUM(
                     CASE
                         WHEN iwd + 1 >= TRUNC(invoice_date, 'mm')
                         THEN
                             CASE WHEN iwd + 1 = invoice_date THEN 1 ELSE 0 END
                     END
                 )
                     mon_1,
                 SUM(
                     CASE
                         WHEN iwd + 2 >= TRUNC(invoice_date, 'mm')
                         THEN
                             CASE WHEN iwd + 2 = invoice_date THEN 1 ELSE 0 END
                     END
                 )
                     tue_1,
                 SUM(
                     CASE
                         WHEN iwd + 3 >= TRUNC(invoice_date, 'mm')
                         THEN
                             CASE WHEN iwd + 3 = invoice_date THEN 1 ELSE 0 END
                     END
                 )
                     wed_1,
                 SUM(
                     CASE
                         WHEN iwd + 4 >= TRUNC(invoice_date, 'mm')
                         THEN
                             CASE WHEN iwd + 4 = invoice_date THEN 1 ELSE 0 END
                     END
                 )
                     thu_1,
                 SUM(
                     CASE
                         WHEN iwd + 5 >= TRUNC(invoice_date, 'mm')
                         THEN
                             CASE WHEN iwd + 5 = invoice_date THEN 1 ELSE 0 END
                     END
                 )
                     fri_1,
                 NVL(SUM(CASE WHEN iwd + 6 = invoice_date THEN 1 ELSE 0 END), 0) sat_1,
                 NVL(SUM(CASE WHEN iwd + 7 = invoice_date THEN 1 ELSE 0 END), 0) sun_2,
                 NVL(SUM(CASE WHEN iwd + 8 = invoice_date THEN 1 ELSE 0 END), 0) mon_2,
                 NVL(SUM(CASE WHEN iwd + 9 = invoice_date THEN 1 ELSE 0 END), 0) tue_2,
                 NVL(SUM(CASE WHEN iwd + 10 = invoice_date THEN 1 ELSE 0 END), 0) wed_2,
                 NVL(SUM(CASE WHEN iwd + 11 = invoice_date THEN 1 ELSE 0 END), 0) thu_2,
                 NVL(SUM(CASE WHEN iwd + 12 = invoice_date THEN 1 ELSE 0 END), 0) fri_2,
                 NVL(SUM(CASE WHEN iwd + 13 = invoice_date THEN 1 ELSE 0 END), 0) sat_2,
                 NVL(SUM(CASE WHEN iwd + 14 = invoice_date THEN 1 ELSE 0 END), 0) sun_3,
                 NVL(SUM(CASE WHEN iwd + 15 = invoice_date THEN 1 ELSE 0 END), 0) mon_3,
                 NVL(SUM(CASE WHEN iwd + 16 = invoice_date THEN 1 ELSE 0 END), 0) tue_3,
                 NVL(SUM(CASE WHEN iwd + 17 = invoice_date THEN 1 ELSE 0 END), 0) wed_3,
                 NVL(SUM(CASE WHEN iwd + 18 = invoice_date THEN 1 ELSE 0 END), 0) thu_3,
                 NVL(SUM(CASE WHEN iwd + 19 = invoice_date THEN 1 ELSE 0 END), 0) fri_3,
                 NVL(SUM(CASE WHEN iwd + 20 = invoice_date THEN 1 ELSE 0 END), 0) sat_3,
                 NVL(SUM(CASE WHEN iwd + 21 = invoice_date THEN 1 ELSE 0 END), 0) sun_4,
                 NVL(SUM(CASE WHEN iwd + 22 = invoice_date THEN 1 ELSE 0 END), 0) mon_4,
                 NVL(SUM(CASE WHEN iwd + 23 = invoice_date THEN 1 ELSE 0 END), 0) tue_4,
                 NVL(SUM(CASE WHEN iwd + 24 = invoice_date THEN 1 ELSE 0 END), 0) wed_4,
                 NVL(SUM(CASE WHEN iwd + 25 = invoice_date THEN 1 ELSE 0 END), 0) thu_4,
                 NVL(SUM(CASE WHEN iwd + 26 = invoice_date THEN 1 ELSE 0 END), 0) fri_4,
                 NVL(SUM(CASE WHEN iwd + 27 = invoice_date THEN 1 ELSE 0 END), 0) sat_4,
                 NVL(SUM(CASE WHEN iwd + 28 = invoice_date THEN 1 ELSE 0 END), 0) sun_5,
                 NVL(SUM(CASE WHEN iwd + 29 = invoice_date THEN 1 ELSE 0 END), 0) mon_5,
                 NVL(SUM(CASE WHEN iwd + 30 = invoice_date THEN 1 ELSE 0 END), 0) tue_5,
                 NVL(SUM(CASE WHEN iwd + 31 = invoice_date THEN 1 ELSE 0 END), 0) wed_5,
                 NVL(SUM(CASE WHEN iwd + 32 = invoice_date THEN 1 ELSE 0 END), 0) thu_5,
                 NVL(SUM(CASE WHEN iwd + 33 = invoice_date THEN 1 ELSE 0 END), 0) fri_5,
                 NVL(SUM(CASE WHEN iwd + 34 = invoice_date THEN 1 ELSE 0 END), 0) sat_5,
                 NVL(SUM(CASE WHEN iwd + 35 = invoice_date THEN 1 ELSE 0 END), 0) sun_6,
                 NVL(SUM(CASE WHEN iwd + 36 = invoice_date THEN 1 ELSE 0 END), 0) mon_6,
                 NVL(SUM(CASE WHEN iwd + 37 = invoice_date THEN 1 ELSE 0 END), 0) tue_6,
                 NVL(SUM(CASE WHEN iwd + 38 = invoice_date THEN 1 ELSE 0 END), 0) wed_6,
                 NVL(SUM(CASE WHEN iwd + 39 = invoice_date THEN 1 ELSE 0 END), 0) thu_6,
                 NVL(SUM(CASE WHEN iwd + 40 = invoice_date THEN 1 ELSE 0 END), 0) fri_6,
                 NVL(SUM(CASE WHEN iwd + 41 = invoice_date THEN 1 ELSE 0 END), 0) sat_6,
                 NVL(SUM(CASE WHEN iwd + 42 = invoice_date THEN 1 ELSE 0 END), 0) sun_7,
                 NVL(SUM(CASE WHEN iwd + 43 = invoice_date THEN 1 ELSE 0 END), 0) mon_7,
                 NVL(SUM(CASE WHEN iwd + 44 = invoice_date THEN 1 ELSE 0 END), 0) tue_7,
                 NVL(SUM(CASE WHEN iwd + 45 = invoice_date THEN 1 ELSE 0 END), 0) wed_7,
                 NVL(SUM(CASE WHEN iwd + 46 = invoice_date THEN 1 ELSE 0 END), 0) thu_7,
                 NVL(SUM(CASE WHEN iwd + 47 = invoice_date THEN 1 ELSE 0 END), 0) fri_7,
                 NVL(SUM(CASE WHEN iwd + 48 = invoice_date THEN 1 ELSE 0 END), 0) sat_7,
                 NVL(SUM(CASE WHEN iwd + 49 = invoice_date THEN 1 ELSE 0 END), 0) sun_8,
                 NVL(SUM(CASE WHEN iwd + 50 = invoice_date THEN 1 ELSE 0 END), 0) mon_8,
                 NVL(SUM(CASE WHEN iwd + 51 = invoice_date THEN 1 ELSE 0 END), 0) tue_8,
                 NVL(SUM(CASE WHEN iwd + 52 = invoice_date THEN 1 ELSE 0 END), 0) wed_8,
                 NVL(SUM(CASE WHEN iwd + 53 = invoice_date THEN 1 ELSE 0 END), 0) thu_8,
                 NVL(SUM(CASE WHEN iwd + 54 = invoice_date THEN 1 ELSE 0 END), 0) fri_8,
                 NVL(SUM(CASE WHEN iwd + 55 = invoice_date THEN 1 ELSE 0 END), 0) sat_8,
                 NVL(SUM(CASE WHEN iwd + 56 = invoice_date THEN 1 ELSE 0 END), 0) sun_9,
                 NVL(SUM(CASE WHEN iwd + 57 = invoice_date THEN 1 ELSE 0 END), 0) mon_9,
                 NVL(SUM(CASE WHEN iwd + 58 = invoice_date THEN 1 ELSE 0 END), 0) tue_9,
                 NVL(SUM(CASE WHEN iwd + 59 = invoice_date THEN 1 ELSE 0 END), 0) wed_9,
                 NVL(SUM(CASE WHEN iwd + 60 = invoice_date THEN 1 ELSE 0 END), 0) thu_9,
                 NVL(SUM(CASE WHEN iwd + 61 = invoice_date THEN 1 ELSE 0 END), 0) fri_9,
                 NVL(SUM(CASE WHEN iwd + 62 = invoice_date THEN 1 ELSE 0 END), 0) sat_9,
                 NVL(SUM(CASE WHEN iwd + 63 = invoice_date THEN 1 ELSE 0 END), 0) sun_10,
                 NVL(SUM(CASE WHEN iwd + 64 = invoice_date THEN 1 ELSE 0 END), 0) mon_10,
                 NVL(SUM(CASE WHEN iwd + 65 = invoice_date THEN 1 ELSE 0 END), 0) tue_10,
                 NVL(SUM(CASE WHEN iwd + 66 = invoice_date THEN 1 ELSE 0 END), 0) wed_10,
                 NVL(SUM(CASE WHEN iwd + 67 = invoice_date THEN 1 ELSE 0 END), 0) thu_10,
                 NVL(SUM(CASE WHEN iwd + 68 = invoice_date THEN 1 ELSE 0 END), 0) fri_10,
                 NVL(SUM(CASE WHEN iwd + 69 = invoice_date THEN 1 ELSE 0 END), 0) sat_10,
                 NVL(SUM(CASE WHEN iwd + 70 = invoice_date THEN 1 ELSE 0 END), 0) sun_11,
                 NVL(SUM(CASE WHEN iwd + 71 = invoice_date THEN 1 ELSE 0 END), 0) mon_11,
                 NVL(SUM(CASE WHEN iwd + 72 = invoice_date THEN 1 ELSE 0 END), 0) tue_11,
                 NVL(SUM(CASE WHEN iwd + 73 = invoice_date THEN 1 ELSE 0 END), 0) wed_11,
                 NVL(SUM(CASE WHEN iwd + 74 = invoice_date THEN 1 ELSE 0 END), 0) thu_11,
                 NVL(SUM(CASE WHEN iwd + 75 = invoice_date THEN 1 ELSE 0 END), 0) fri_11,
                 NVL(SUM(CASE WHEN iwd + 76 = invoice_date THEN 1 ELSE 0 END), 0) sat_11,
                 NVL(SUM(CASE WHEN iwd + 77 = invoice_date THEN 1 ELSE 0 END), 0) sun_12,
                 NVL(SUM(CASE WHEN iwd + 78 = invoice_date THEN 1 ELSE 0 END), 0) mon_12,
                 NVL(SUM(CASE WHEN iwd + 79 = invoice_date THEN 1 ELSE 0 END), 0) tue_12,
                 NVL(SUM(CASE WHEN iwd + 80 = invoice_date THEN 1 ELSE 0 END), 0) wed_12,
                 NVL(SUM(CASE WHEN iwd + 81 = invoice_date THEN 1 ELSE 0 END), 0) thu_12,
                 NVL(SUM(CASE WHEN iwd + 82 = invoice_date THEN 1 ELSE 0 END), 0) fri_12,
                 NVL(SUM(CASE WHEN iwd + 83 = invoice_date THEN 1 ELSE 0 END), 0) sat_12,
                 NVL(SUM(CASE WHEN iwd + 84 = invoice_date THEN 1 ELSE 0 END), 0) sun_13,
                 NVL(SUM(CASE WHEN iwd + 85 = invoice_date THEN 1 ELSE 0 END), 0) mon_13,
                 NVL(SUM(CASE WHEN iwd + 86 = invoice_date THEN 1 ELSE 0 END), 0) tue_13,
                 NVL(SUM(CASE WHEN iwd + 87 = invoice_date THEN 1 ELSE 0 END), 0) wed_13,
                 NVL(SUM(CASE WHEN iwd + 88 = invoice_date THEN 1 ELSE 0 END), 0) thu_13,
                 NVL(SUM(CASE WHEN iwd + 89 = invoice_date THEN 1 ELSE 0 END), 0) fri_13,
                 NVL(SUM(CASE WHEN iwd + 90 = invoice_date THEN 1 ELSE 0 END), 0) sat_13,
                 NVL(SUM(CASE WHEN iwd + 91 = invoice_date THEN 1 ELSE 0 END), 0) sun_14,
                 NVL(SUM(CASE WHEN iwd + 92 = invoice_date THEN 1 ELSE 0 END), 0) mon_14,
                 NVL(SUM(CASE WHEN iwd + 93 = invoice_date THEN 1 ELSE 0 END), 0) tue_14,
                 NVL(SUM(CASE WHEN iwd + 94 = invoice_date THEN 1 ELSE 0 END), 0) wed_14,
                 NVL(SUM(CASE WHEN iwd + 95 = invoice_date THEN 1 ELSE 0 END), 0) thu_14,
                 NVL(SUM(CASE WHEN iwd + 96 = invoice_date THEN 1 ELSE 0 END), 0) fri_14,
                 NVL(SUM(CASE WHEN iwd + 97 = invoice_date THEN 1 ELSE 0 END), 0) sat_14,
                 NVL(SUM(CASE WHEN iwd + 98 = invoice_date THEN 1 ELSE 0 END), 0) sun_15,
                 NVL(SUM(CASE WHEN iwd + 99 = invoice_date THEN 1 ELSE 0 END), 0) mon_15
            FROM (SELECT TO_CHAR(TRUNC(ih.invoice_date, 'mm'), 'yyyymm') ym,
                         NEXT_DAY(TRUNC(TO_DATE(date_from, 'DD-MON-YYYY'), 'mm'), 'Sun') iwd,
                         CASE
                             WHEN vp.inv_product_type = 'RBC' THEN 'Red Blood Cells'
                             WHEN vp.inv_product_type = 'LRBC' THEN 'Leukoreduced Red Blood Cells'
                             WHEN vp.inv_product_type = 'LPHER' THEN 'SDP'
                             WHEN vp.inv_product_type = 'PHER' THEN 'SDP'
                             WHEN vp.inv_product_type = 'FFP' THEN 'Fresh Frozen Plasma'
                             WHEN vp.inv_product_type = 'FP24' THEN 'Plasma Frozen withing 24hrs'
                             WHEN vp.inv_product_type = 'CRYO' THEN 'Cryoprecipitate'
                         END
                             inv_product_type,
                         ih.customer_id,
                         id.patient_name,
                         ih.invoice_date
                    FROM tab1 ih,
                         tab2 id,
                         tab4 ip,
                         tab3 vp
                   WHERE id.invoice_number = ih.invoice_number
                     AND id.item_id = vp.product_code
                     AND id.item_id = ip.item_id
                     AND ip.item_type IN ('P')
                     --AND ih.customer_id = 'WAD-EX0128'
                     AND ih.invoice_date >= TO_DATE(ym, 'YYYYMM')
                     AND ih.invoice_date < ADD_MONTHS(TO_DATE(ym, 'YYYYMM'), 3)
                     AND vp.inv_product_type IN ('RBC',
                                                 'LRBC',
                                                 'LPHER',
                                                 'PHER',
                                                 'FFP',
                                                 'FP24',
                                                 'CRYO'))
        GROUP BY inv_product_type, customer_id, patient_name
        ORDER BY inv_product_type, customer_id, patient_name;

    varc1                  c1%ROWTYPE;
BEGIN
    SELECT REPLACE(days, ',', CHR(9)) data
      INTO v_header
      FROM (    SELECT SUBSTR(
                           SYS_CONNECT_BY_PATH(
                               TO_CHAR(
                                   NEXT_DAY(TO_DATE(ym, 'YYYYMM') - 1, 'Sun') + LEVEL - 1,
                                   'dd-Mon-yyyy'
                               ),
                               ','
                           ),
                           2
                       )
                           days
                  FROM DUAL
            CONNECT BY LEVEL <= 12 * 7
              ORDER BY LEVEL DESC)
     WHERE ROWNUM = 1;

    -- DBMS_OUTPUT.put_line(v_header);

    v_location_i := 1000;

    --FILENAME1   := 'hosp_usage' || '_' || SYSDATE || '.CSV';
    filename1 := 'hosp_usage' || '_' || SYSDATE || '.xls';
    v_location_i := 2000;
    filename := UTL_FILE.fopen('HOSP_USAGE_DIR', filename1, 'W');

    /* THIS WILL CREATE THE HEADING IN EXCEL SHEET */
    v_location_i := 3000;
    UTL_FILE.put_line(
        filename,
        'INV PRODUCT TYPE' || CHR(9) || 'CUSTOMER ID' || CHR(9) || 'NAME' || CHR(9) || v_header
    );

    /*'SUN1'||chr(9)||'MON1'||chr(9)||'TUE1'||chr(9)||'WED1'||chr(9)||'THU1'||chr(9)||'FRI1'||chr(9)||'SAT1'||chr(9)||
    'SUN2'||chr(9)||'MON2'||chr(9)||'TUE2'||chr(9)||'WED2'||chr(9)||'THU2'||chr(9)||'FRI2'||chr(9)||'SAT2'||chr(9)||
    'SUN3'||chr(9)||'MON3'||chr(9)||'TUE3'||chr(9)||'WED3'||chr(9)||'THU3'||chr(9)||'FRI3'||chr(9)||'SAT3'||chr(9)||
    'SUN4'||chr(9)||'MON4'||chr(9)||'TUE4'||chr(9)||'WED4'||chr(9)||'THU4'||chr(9)||'FRI4'||chr(9)||'SAT4'||chr(9)||
    'SUN5'||chr(9)||'MON5'||chr(9)||'TUE5'||chr(9)||'WED5'||chr(9)||'THU5'||chr(9)||'FRI5'||chr(9)||'SAT5'||chr(9)||
    'SUN6'||chr(9)||'MON6'||chr(9)||'TUE6'||chr(9)||'WED6'||chr(9)||'THU6'||chr(9)||'FRI6'||chr(9)||'SAT6'||chr(9)||
    'SUN7'||chr(9)||'MON7'||chr(9)||'TUE7'||chr(9)||'WED7'||chr(9)||'THU7'||chr(9)||'FRI7'||chr(9)||'SAT7'||chr(9)||
    'SUN8'||chr(9)||'MON8'||chr(9)||'TUE8'||chr(9)||'WED8'||chr(9)||'THU8'||chr(9)||'FRI8'||chr(9)||'SAT8'||chr(9)||
    'SUN9'||chr(9)||'MON9'||chr(9)||'TUE9'||chr(9)||'WED9'||chr(9)||'THU9'||chr(9)||'FRI9'||chr(9)||'SAT9'||chr(9)||
    'SUN10'||chr(9)||'MON10'||chr(9)||'TUE10'||chr(9)||'WED10'||chr(9)||'THU10'||chr(9)||'FRI10'||chr(9)||'SAT10'||chr(9)||
    'SUN11'||chr(9)||'MON11'||chr(9)||'TUE11'||chr(9)||'WED11'||chr(9)||'THU11'||chr(9)||'FRI11'||chr(9)||'SAT11'||chr(9)||
    'SUN12'||chr(9)||'MON12'||chr(9)||'TUE12'||chr(9)||'WED12'||chr(9)||'THU12'||chr(9)||'FRI12'||chr(9)||'SAT12'||chr(9)||
    'SUN13'||chr(9)||'MON13'||chr(9)||'TUE13'||chr(9)||'WED13'||chr(9)||'THU13'||chr(9)||'FRI13'||chr(9)||'SAT13'||chr(9)||
    'SUN14'||chr(9)||'MON14'||chr(9)||'TUE14'||chr(9)||'WED14'||chr(9)||'THU14'||chr(9)||'FRI14'||chr(9)||'SAT14'||chr(9)||
    'SUN15'||chr(9)||'MON15');*/

    OPEN c1;

    v_location_i := 4000;

    LOOP
        FETCH c1 INTO varc1;

        v_location_i := 5000;
        EXIT WHEN c1%NOTFOUND;
        /*  THIS WILL PRINT THE RECORDS IN EXCEL SHEET AS PER THE QUERY IN CURSOR */
        v_location_i := 6000;
        UTL_FILE.put_line(
            filename,
               varc1.inv_product_type
            || CHR(9)
            || varc1.customer_id
            || CHR(9)
            || varc1.patient_name
            || CHR(9)
            || varc1.sun_1
            || CHR(9)
            || varc1.mon_1
            || CHR(9)
            || varc1.tue_1
            || CHR(9)
            || varc1.wed_1
            || CHR(9)
            || varc1.thu_1
            || CHR(9)
            || varc1.fri_1
            || CHR(9)
            || varc1.sat_1
            || CHR(9)
            || varc1.sun_2
            || CHR(9)
            || varc1.mon_2
            || CHR(9)
            || varc1.tue_2
            || CHR(9)
            || varc1.wed_2
            || CHR(9)
            || varc1.thu_2
            || CHR(9)
            || varc1.fri_2
            || CHR(9)
            || varc1.sat_2
            || CHR(9)
            || varc1.sun_3
            || CHR(9)
            || varc1.mon_3
            || CHR(9)
            || varc1.tue_3
            || CHR(9)
            || varc1.wed_3
            || CHR(9)
            || varc1.thu_3
            || CHR(9)
            || varc1.fri_3
            || CHR(9)
            || varc1.sat_3
            || CHR(9)
            || varc1.sun_4
            || CHR(9)
            || varc1.mon_4
            || CHR(9)
            || varc1.tue_4
            || CHR(9)
            || varc1.wed_4
            || CHR(9)
            || varc1.thu_4
            || CHR(9)
            || varc1.fri_4
            || CHR(9)
            || varc1.sat_4
            || CHR(9)
            || varc1.sun_5
            || CHR(9)
            || varc1.mon_5
            || CHR(9)
            || varc1.tue_5
            || CHR(9)
            || varc1.wed_5
            || CHR(9)
            || varc1.thu_5
            || CHR(9)
            || varc1.fri_5
            || CHR(9)
            || varc1.sat_5
            || CHR(9)
            || varc1.sun_6
            || CHR(9)
            || varc1.mon_6
            || CHR(9)
            || varc1.tue_6
            || CHR(9)
            || varc1.wed_6
            || CHR(9)
            || varc1.thu_6
            || CHR(9)
            || varc1.fri_6
            || CHR(9)
            || varc1.sat_6
            || CHR(9)
            || varc1.sun_7
            || CHR(9)
            || varc1.mon_7
            || CHR(9)
            || varc1.tue_7
            || CHR(9)
            || varc1.wed_7
            || CHR(9)
            || varc1.thu_7
            || CHR(9)
            || varc1.fri_7
            || CHR(9)
            || varc1.sat_7
            || CHR(9)
            || varc1.sun_8
            || CHR(9)
            || varc1.mon_8
            || CHR(9)
            || varc1.tue_8
            || CHR(9)
            || varc1.wed_8
            || CHR(9)
            || varc1.thu_8
            || CHR(9)
            || varc1.fri_8
            || CHR(9)
            || varc1.sat_8
            || CHR(9)
            || varc1.sun_9
            || CHR(9)
            || varc1.mon_9
            || CHR(9)
            || varc1.tue_9
            || CHR(9)
            || varc1.wed_9
            || CHR(9)
            || varc1.thu_9
            || CHR(9)
            || varc1.fri_9
            || CHR(9)
            || varc1.sat_9
            || CHR(9)
            || varc1.sun_10
            || CHR(9)
            || varc1.mon_10
            || CHR(9)
            || varc1.tue_10
            || CHR(9)
            || varc1.wed_10
            || CHR(9)
            || varc1.thu_10
            || CHR(9)
            || varc1.fri_10
            || CHR(9)
            || varc1.sat_10
            || CHR(9)
            || varc1.sun_11
            || CHR(9)
            || varc1.mon_11
            || CHR(9)
            || varc1.tue_11
            || CHR(9)
            || varc1.wed_11
            || CHR(9)
            || varc1.thu_11
            || CHR(9)
            || varc1.fri_11
            || CHR(9)
            || varc1.sat_11
            || CHR(9)
            || varc1.sun_12
            || CHR(9)
            || varc1.mon_12
            || CHR(9)
            || varc1.tue_12
            || CHR(9)
            || varc1.wed_12
            || CHR(9)
            || varc1.thu_12
            || CHR(9)
            || varc1.fri_12
            || CHR(9)
            || varc1.sat_12
            || CHR(9)
            || varc1.sun_13
            || CHR(9)
            || varc1.mon_13
            || CHR(9)
            || varc1.tue_13
            || CHR(9)
            || varc1.wed_13
            || CHR(9)
            || varc1.thu_13
            || CHR(9)
            || varc1.fri_13
            || CHR(9)
            || varc1.sat_13
            || CHR(9)
            || varc1.sun_14
            || CHR(9)
            || varc1.mon_14
            || CHR(9)
            || varc1.tue_14
            || CHR(9)
            || varc1.wed_14
            || CHR(9)
            || varc1.thu_14
            || CHR(9)
            || varc1.fri_14
            || CHR(9)
            || varc1.sat_14
            || CHR(9)
            || varc1.sun_15
            || CHR(9)
            || varc1.mon_15
        );
    END LOOP;

    v_location_i := 7000;
    UTL_FILE.fclose(filename);
    v_location_i := 8000;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line('Error in ' || v_procedure_c || ' at line: ' || v_location_i);
END load_hosp_usage_05072015;
/

Open in new window

0
 
sdstuberCommented:
Is it ok for you publish your data publicly as you have in the spreadsheet above?

if it's just sample data that's fine, but we have no way of knowing what is real and what is fake unless it's obvious
0
 
anumosesAuthor Commented:
its sample data
0
 
anumosesAuthor Commented:
Error in load_hosp_usage at line: 6000
0
 
sdstuberCommented:
what's the error?
what is the data being processed at line 6000?
0
 
anumosesAuthor Commented:
UTL_FILE.put_line(
            filename,
               varc1.inv_product_type
            || CHR(9)
            || varc1.customer_id
            || CHR(9)
            || varc1.patient_name
            || CHR(9)
            || varc1.sun_1
            || CHR(9)
            || varc1.mon_1
            || CHR(9)
            || varc1.tue_1
            || CHR(9)
            || varc1.wed_1
            || CHR(9)
            || varc1.thu_1
            || CHR(9)
            || varc1.fri_1
            || CHR(9)
            || varc1.sat_1
            || CHR(9)
            || varc1.sun_2
            || CHR(9)
            || varc1.mon_2
            || CHR(9)
            || varc1.tue_2
            || CHR(9)
            || varc1.wed_2
            || CHR(9)
            || varc1.thu_2
            || CHR(9)
            || varc1.fri_2
            || CHR(9)
            || varc1.sat_2
            || CHR(9)
            || varc1.sun_3
            || CHR(9)
            || varc1.mon_3
            || CHR(9)
            || varc1.tue_3
            || CHR(9)
            || varc1.wed_3
            || CHR(9)
            || varc1.thu_3
            || CHR(9)
            || varc1.fri_3
            || CHR(9)
            || varc1.sat_3
            || CHR(9)
            || varc1.sun_4
            || CHR(9)
            || varc1.mon_4
            || CHR(9)
            || varc1.tue_4
            || CHR(9)
            || varc1.wed_4
            || CHR(9)
            || varc1.thu_4
            || CHR(9)
            || varc1.fri_4
            || CHR(9)
            || varc1.sat_4
            || CHR(9)
            || varc1.sun_5
            || CHR(9)
            || varc1.mon_5
            || CHR(9)
            || varc1.tue_5
            || CHR(9)
            || varc1.wed_5
            || CHR(9)
            || varc1.thu_5
            || CHR(9)
            || varc1.fri_5
            || CHR(9)
            || varc1.sat_5
            || CHR(9)
            || varc1.sun_6
            || CHR(9)
            || varc1.mon_6
            || CHR(9)
            || varc1.tue_6
            || CHR(9)
            || varc1.wed_6
            || CHR(9)
            || varc1.thu_6
            || CHR(9)
            || varc1.fri_6
            || CHR(9)
            || varc1.sat_6
            || CHR(9)
            || varc1.sun_7
            || CHR(9)
            || varc1.mon_7
            || CHR(9)
            || varc1.tue_7
            || CHR(9)
            || varc1.wed_7
            || CHR(9)
            || varc1.thu_7
            || CHR(9)
            || varc1.fri_7
            || CHR(9)
            || varc1.sat_7
            || CHR(9)
            || varc1.sun_8
            || CHR(9)
            || varc1.mon_8
            || CHR(9)
            || varc1.tue_8
            || CHR(9)
            || varc1.wed_8
            || CHR(9)
            || varc1.thu_8
            || CHR(9)
            || varc1.fri_8
            || CHR(9)
            || varc1.sat_8
            || CHR(9)
            || varc1.sun_9
            || CHR(9)
            || varc1.mon_9
            || CHR(9)
            || varc1.tue_9
            || CHR(9)
            || varc1.wed_9
            || CHR(9)
            || varc1.thu_9
            || CHR(9)
            || varc1.fri_9
            || CHR(9)
            || varc1.sat_9
            || CHR(9)
            || varc1.sun_10
            || CHR(9)
            || varc1.mon_10
            || CHR(9)
            || varc1.tue_10
            || CHR(9)
            || varc1.wed_10
            || CHR(9)
            || varc1.thu_10
            || CHR(9)
            || varc1.fri_10
            || CHR(9)
            || varc1.sat_10
            || CHR(9)
            || varc1.sun_11
            || CHR(9)
            || varc1.mon_11
            || CHR(9)
            || varc1.tue_11
            || CHR(9)
            || varc1.wed_11
            || CHR(9)
            || varc1.thu_11
            || CHR(9)
            || varc1.fri_11
            || CHR(9)
            || varc1.sat_11
            || CHR(9)
            || varc1.sun_12
            || CHR(9)
            || varc1.mon_12
            || CHR(9)
            || varc1.tue_12
            || CHR(9)
            || varc1.wed_12
            || CHR(9)
            || varc1.thu_12
            || CHR(9)
            || varc1.fri_12
            || CHR(9)
            || varc1.sat_12
            || CHR(9)
            || varc1.sun_13
            || CHR(9)
            || varc1.mon_13
            || CHR(9)
            || varc1.tue_13
            || CHR(9)
            || varc1.wed_13
            || CHR(9)
            || varc1.thu_13
            || CHR(9)
            || varc1.fri_13
            || CHR(9)
            || varc1.sat_13
            || CHR(9)
            || varc1.sun_14
            || CHR(9)
            || varc1.mon_14
            || CHR(9)
            || varc1.tue_14
            || CHR(9)
            || varc1.wed_14
            || CHR(9)
            || varc1.thu_14
            || CHR(9)
            || varc1.fri_14
            || CHR(9)
            || varc1.sat_14
            || CHR(9)
            || varc1.sun_15
            || CHR(9)
            || varc1.mon_15
        );
    END LOOP;
0
 
sdstuberCommented:
what is the error?

what is the DATA for that line?
0
 
anumosesAuthor Commented:
I ran the query for just one customer and the data is in excel spread sheet. line 6000
data-line-6000.xls
0
 
sdstuberCommented:
what is the error?
0
 
anumosesAuthor Commented:
No error.

0ORA-0000: normal, successful completion
0ORA-0000: normal, successful completion
0ORA-0000: normal, successful completion
0ORA-0000: normal, successful completion
Error in load_hosp_usage at line: 6000

File created 0 byte. No data.
0
 
anumosesAuthor Commented:
0ORA-0000: normal, successful completion
0ORA-0000: normal, successful completion
0ORA-0000: normal, successful completion
0ORA-0000: normal, successful completion
-29285ORA-29285: file write error
Error in load_hosp_usage at line: 6000
0
 
anumosesAuthor Commented:
9i problem

utl_file.fopen(
                    location => path,
                    filename => filename,
                    open_mode => read_write_append,
                    max_linesize => 32767);

Now it works.
0
 
anumosesAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.