Report Generation Tabbing error

Question

I have a procedure using a util file and cursor where i have to generate a report for the table cl_masd_payments in the format given in the code

I have written the procedure but its still giving errors...don know what is the problem

Any help will be highly appreciated.

Anand

The procedure i have written is such:

CREATE OR REPLACE PROCEDURE p_Anand_REP
IS
pnum_id varchar2(12);
p_labmat_acc varchar2(30);
p_pmt_dt date;
p_tot_amt number(9,2);
p_sd_amt number(10);
p_labmat_amt number(15);
p_add varchar2(100);
p_util_rec utl_file.file_type;
CURSOR cur_pmt_anand
IS
select rpad(ma_cons,10,' ')||rpad(ma_cons_chk_dgt,10,' '),
       rpad(ma_ac_lab,10,' ')||rpad(ma_ac_mat,10,' '),
       ma_pmt_dt,
       ma_pmt_amt,
       ma_sd_amt,
       rpad(ma_sc_amt_lab,10,' ')||rpad(ma_sc_amt_mat,10,' '),
       rpad(ma_add_one,40,' ')||rpad(ma_add_two,40,' ')||rpad(ma_add_three,40,' ')||rpad(ma_add_four,40,' ')||rpad(ma_add_five,40,' ')||rpad(ma_add_six,40,' ')
from   cl_masd_payments
where  ma_cons=9600100100;
order  by ma_pmt_dt;

BEGIN
p_util_rec:=utl_file.fopen('/san3/work/test1/ananda/plsql','PAY_REP','w');
OPEN cur_pmt_anand;
utl_file.put_line(p_util_rec,'CONS_NO/CD  LAB/MAT_ACC  PMT_DT  PMT_AMT  SD_AMT  LAB/MAT_AMT  NAME_ADDR  ');
LOOP
fetch cur_pmt_anand into pnum_id,p_labmat_acc,p_pmt_dt,p_tot_amt,p_sd_amt,p_labmat_amt,p_add;
BEGIN
utl_file.put_line(p_util_rec,rpad(pnum_id,15,' ')||rpad(p_labmat_acc,15,' ')||rpad(p_pmt_dt,15,' ')||rpad(p_tot_amt,15,' ')||rpad(p_sd_amt,15,' ')||rpad(p_labmat_amt,15,' ')||rpad(p_add,15,' '));
exit when cur_pmt_anand%NOTFOUND;
END LOOP;
utl_file.fclose(p_util_rec);
CLOSE cur_pmt_anand;
END p_Anand_REP;
/

Answer 1

Your procedure contains syntax errors, this is a formatted version of your procedure:

CREATE OR REPLACE PROCEDURE p_Anand_REP
IS
   pnum_id        VARCHAR2 (12);
   p_labmat_acc   VARCHAR2 (30);
   p_pmt_dt       DATE;
   p_tot_amt      NUMBER (9, 2);
   p_sd_amt       NUMBER (10);
   p_labmat_amt   NUMBER (15);
   p_add          VARCHAR2 (100);
   p_util_rec     UTL_FILE.file_type;

   CURSOR cur_pmt_anand
   IS
        SELECT RPAD (ma_cons, 10, ' ') || RPAD (ma_cons_chk_dgt, 10, ' '),
               RPAD (ma_ac_lab, 10, ' ') || RPAD (ma_ac_mat, 10, ' '),
               ma_pmt_dt,
               ma_pmt_amt,
               ma_sd_amt,
               RPAD (ma_sc_amt_lab, 10, ' ') || RPAD (ma_sc_amt_mat, 10, ' '),
                  RPAD (ma_add_one, 40, ' ')
               || RPAD (ma_add_two, 40, ' ')
               || RPAD (ma_add_three, 40, ' ')
               || RPAD (ma_add_four, 40, ' ')
               || RPAD (ma_add_five, 40, ' ')
               || RPAD (ma_add_six, 40, ' ')
          FROM cl_masd_payments
         WHERE ma_cons = 9600100100
      ORDER BY ma_pmt_dt;
BEGIN
   p_util_rec :=
      UTL_FILE.fopen ('/san3/work/test1/ananda/plsql', 'PAY_REP', 'w');

   OPEN cur_pmt_anand;

   UTL_FILE.
   put_line (
      p_util_rec,
      'CONS_NO/CD  LAB/MAT_ACC  PMT_DT  PMT_AMT  SD_AMT  LAB/MAT_AMT  NAME_ADDR  ');

   LOOP
      FETCH cur_pmt_anand
      INTO pnum_id,
           p_labmat_acc,
           p_pmt_dt,
           p_tot_amt,
           p_sd_amt,
           p_labmat_amt,
           p_add;

      EXIT WHEN cur_pmt_anand%NOTFOUND;

      UTL_FILE.
      put_line (
         p_util_rec,
            RPAD (pnum_id, 15, ' ')
         || RPAD (p_labmat_acc, 15, ' ')
         || RPAD (p_pmt_dt, 15, ' ')
         || RPAD (p_tot_amt, 15, ' ')
         || RPAD (p_sd_amt, 15, ' ')
         || RPAD (p_labmat_amt, 15, ' ')
         || RPAD (p_add, 15, ' '));
   END LOOP;

   CLOSE cur_pmt_anand;

   UTL_FILE.fclose (p_util_rec);
END p_Anand_REP;