UTL_FILE.FOPEN in Oracle

The code works fine and also creates an excel spread sheet. But column headings are not right. I am enclosing the output. Let me know where in my code I am incorrect in passing the heading/data. Also including script for sample date. This is an extension I am working to my previous few queries.
ym = 201501
date_from = 01-jan-2015

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

hosp-usage-06-MAY-15.xls
sample-date.txt
LVL 6
anumosesAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
The headers aren't right because you are missing some TAB characters...


'SUN1'||chr(9)||'MON1'||'TUE1'

Notice the missing CHR(9) between MON1 and TUE1?
0
 
anumosesAuthor Commented:
oops. Correct. Its all copy and paste problem!! That is why I feel second set of eyes are so important. Thanks for looking.
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.