[Solved] How to run cursor in a procedure if we have already set of queries defined?

by


You haven’t given any indication of error message or any details that could help pinpoint your issue. I’m not sure why you have an embedded DECLARE…BEGIN…END, why you need a COMMIT in the middle of the code or why you have a COMMIT at all – it should be committed by the calling procedure.

I think the following ‘feels’ more correct to me;

CREATE OR REPLACE PROCEDURE report_generate_proc_reg(
   reportyyyymm        NUMBER
 , report_region       VARCHAR2
 ,                                                                 --NR/WR/ETC
  meetingdate          DATE
 ,                                                                      --date
  meetingdesc          VARCHAR2
 ,                                                                      --desc
  generateby           VARCHAR2
 ,                                                                    --userid
  companycode          VARCHAR2
 ,                                                                    --userid
  ret_int          OUT INT
 ,                                     --- 0 if error report number if success
  ret_msg          OUT VARCHAR2     -- error message if error blank if success
                               )
AS
   final_report_id   INTEGER := 0;
   total_column_no   INTEGER := 0;

   CURSOR adi_curr
   IS
      SELECT DISTINCT row_no
      FROM   hindi_test
      WHERE  report_no = final_report_id
      AND    row_no NOT IN (0, 37, 38);

   v_no              INTEGER;
BEGIN
   SELECT MAX(rid) INTO final_report_id FROM hindi_report_gen_new; --max rid is stored in final_report_id

   final_report_id   := final_report_id + 1;

   INSERT INTO hindi_report_gen_new
      VALUES      (  final_report_id
                   ,    'Report generated for '
                     || companycode
                     || 'for Quarter ending in '
                     || reportyyyymm
                     || '\n Meeting Held on '
                     || meetingdate
                     || '\n Desc is '
                     || meetingdesc
                   , reportyyyymm
                   , meetingdate
                   , meetingdesc
                   , generateby
                   , SYSDATE
                   , companycode);

   -- inserting the data for reports
   FOR rec_adi_curr IN adi_curr
   LOOP
      INSERT INTO hindi_region_report
         SELECT a.report_no
              , a.row_no
              , c.state_cord
              , a.VALUE
         FROM   hindi_test a
                JOIN hindi_trn_report_header b ON a.column_no = b.report_no
                JOIN hindi_mst_cost_centre c
                   ON c.cost_centre_code = b.cost_centre_code
         WHERE  row_no = v_no
         AND    a.report_no = final_report_id;
   END LOOP;

   -- inserting sum of all rows
   INSERT INTO test_test
      SELECT   final_report_id
             , row_no
             , column_no
             , SUM(VALUE)
      FROM     hindi_region_report
      WHERE    report_no = final_report_id
      AND      row_no NOT IN (0 , 22 , 25 , 28 , 37 , 38)
      GROUP BY final_report_id, row_no, column_no;

   COMMIT;
END report_generate_proc_reg;

The cursor loop could probably use BULK COLLECT but with no indication of data volumes I can’t say if it’s worth it.

2

solved How to run cursor in a procedure if we have already set of queries defined?