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

by

[ad_1]

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

[ad_2]

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