0

I am attempting to write an Oracle stored procedure that will call five (5) sub-stored procedures for all of the Bill's (bill_id_tab) in a particular result set. I am using a FOR loop, and the only set of data being returned is the very last Bill. I am assuming that it is writing out the data AFTER the FOR..LOOP has completed, and not during. I am not sure how to get it to write out each resultset during the processing.

Here is my code:

BEGIN
  DECLARE
     v_bill_id           CHAR(12);
     v_batch_nbr         NUMBER(10);

  --define cursor
  cursor bill_id_tab is (SELECT BILL_ID
                         FROM CI_BILL_ROUTING
                         WHERE BATCH_NBR = (SELECT MAX(BATCH_NBR)
                                            FROM CI_BILL_ROUTING
                                            WHERE BATCH_CD = 'DGRTPOST')
                           AND BATCH_CD = 'DGRTPOST')
                         ORDER BY BILL_ID;
BEGIN
    /******************************************************************************
      get bill_id's for all bills generated in the max Batch_nbr.  
      Batch_cd will be used to seperate
     ******************************************************************************/

BEGIN
    FOR each BILL_ID IN bill_id_tab LOOP
      BEGIN
        RPTUSER.CM_DGBILLPB_HP2(p_info_cursor1, NULL, rec.BILL_ID, NULL, NULL);
        RPTUSER.CM_DGBILLPR_HP(p_info_cursor2, rec.BILL_ID);
        RPTUSER.CM_DGBILLMS_HP(p_info_cursor3, rec.BILL_ID, 'C');
        RPTUSER.CM_DGBILLCG_HP(p_info_cursor4, rec.BILL_ID);
        RPTUSER.CM_DGBILLMR_HP(p_info_cursor5, rec.BILL_ID);
      END;
    END LOOP;
END;
END;

The bills that I would expect to see data for is:

819244555299
819246299764
819248391148
951891843093
951896713417
951897314849

The only resultset that is returned is Bill_ID - 951897314849.

Any guidance will be appreciated.

Thanks in advance.

Anthony DAgostino
  • 1
  • 1
  • Anthony, welcome to SO! :) Please, take [the tour](http://stackoverflow.com/tour) to earn your first shiny badge ;) I reformatted your question and added `sql` tag to help some sql guru find your question. – Jan 'splite' K. Aug 11 '15 at 21:02
  • Which RDBMS is this for? It often does make a difference whether you're using MySQL, PostgreSQL, Oracle, SQL Server or IBM DB2 - or something else even. Please add a relevant tag to your question! – marc_s Aug 12 '15 at 05:09
  • The code you posted is missing some context. We don't see where you return the data, or where you write it out, etc.. – sstan Aug 12 '15 at 14:05
  • This SP is called by an external application. It is writing out "something", just not what I am expecting. If I need to add a write-out step, then I would appreciate some example of what that would look like. – Anthony DAgostino Aug 12 '15 at 14:17

0 Answers0