Aggregate operation on array - get totals (11.2.0.1.0)

[Ferro]

Hi All,

I have a query (complex and resource consuming in the real world) that calculates interest rate per withdrawal transaction for each loan. I need to insert the values in Interest (t_Interest) table, meanwhile I need to insert the total interest per loan per date in accounting table (t_ledger_trans).

So I have three options:
1- Create another Stored Procedure (P_GET_DATA_TEST_GRP) to return the same output of P_GET_DATA_TEST but with a group by added in the select:

SELECT loan_id, withd_date, SUM(withd_amount * rate/100) tran_total FROM t_withdrawal tw, t_rate
WHERE tw.withd_date = t_rate.rate_date
GROUP BY loan_id, withd_date;

But this way I will be repeating the expensive query twice which I am trying to avoid.

2- Commit after insertion in T_Interest and query the data I need to fill t_ledger_trans (the accounting table). But this way I will commit data that might not be final which can have serious drawbacks.

3- Use multiset casting in Insert SP to get the sum of each loan (but I get
ORA-00902: invalid datatype and I dont know what I am doing wrong) :

PROCEDURE P_insert_DATA_TEST
  IS
    AR_MY_ARRAY ARY_COMtest;
  BEGIN

   testPac1.P_GET_DATA_TEST( AR_MY_ARRAY);

   FOR I IN 1..ar_My_Array.COUNT LOOP
     INSERT INTO t_interest (Loan_id, Interest_date, Interest_amount)
          SELECT ar_My_Array(I).Loan_id, ar_My_Array(I).t_date, ar_My_Array(I).t_amount
     FROM DUAL;
   END LOOP;

  SELECT CAST ( multiset( SELECT Loan_id, SUM(t_amount) amount
              FROM TABLE( AR_MY_ARRAY )
              GROUP BY loan_id
             ) AS ARY_COMtest)  --<-- ORA-00902: invalid datatype
  INTO AR_MY_ARRAY
  FROM dual;

   FOR I IN 1..ar_My_Array.COUNT LOOP

     INSERT INTO t_ledger_trans (Loan_id, Trans_date, tran_total)
          SELECT ar_My_Array(I).Loan_id, sysdate, ar_My_Array(I).t_amount
     FROM DUAL;

   END LOOP;

  END;

4- Calculate the total of each loan inside the loop (but I dont know how to order AR_MY_ARRAY to make sure that each loan occurrences are listed after each other).

5- Find a smarter way to get the totals in P_insert_DATA_TEST while looping on AR_MY_ARRAY.

My example (basic code without any option):

CREATE TABLE t_rate
  (
    Rate_date DATE,
    rate NUMBER(15,3)
  );

CREATE TABLE t_withdrawal
  (
    Loan_id NUMBER,
    withd_date DATE,
    Withd_amount NUMBER
  );

CREATE TABLE t_interest
  (
    Loan_id NUMBER,
    Interest_date DATE,
    Interest_amount NUMBER(15,3)
  );

CREATE TABLE t_ledger_trans
  (
    Loan_id NUMBER,
    Trans_date DATE,
    tran_total NUMBER(15,3)
  );

INSERT ALL
  INTO t_rate VALUES (to_date('01-01-2000','dd-mm-yyyy'), 1.5)
  INTO t_rate VALUES (to_date('02-01-2000','dd-mm-yyyy'), 2.5)
  INTO t_rate VALUES (to_date('03-01-2000','dd-mm-yyyy'), 0.5)
  INTO t_rate VALUES (to_date('04-01-2000','dd-mm-yyyy'), 0.5)
  INTO t_withdrawal VALUES (1, to_date('01-01-2000','dd-mm-yyyy'), 120)
  INTO t_withdrawal VALUES (2, to_date('02-01-2000','dd-mm-yyyy'), 170)
  INTO t_withdrawal VALUES (3, to_date('03-01-2000','dd-mm-yyyy'), 55)
  INTO t_withdrawal VALUES (4, to_date('01-01-2000','dd-mm-yyyy'), 310)
  INTO t_withdrawal VALUES (5, to_date('03-01-2000','dd-mm-yyyy'), 110)
  INTO t_withdrawal VALUES (1, to_date('01-01-2000','dd-mm-yyyy'), 70)
  INTO t_withdrawal VALUES (2, to_date('02-01-2000','dd-mm-yyyy'), 695)
  INTO t_withdrawal VALUES (5, to_date('03-01-2000','dd-mm-yyyy'), 153)
  INTO t_withdrawal VALUES (1, to_date('04-01-2000','dd-mm-yyyy'), 320)
SELECT * FROM dual;

-- TESTPAC1 specification
CREATE OR REPLACE PACKAGE testPac1 AS

  TYPE REC_COMtest IS RECORD
    (
      Loan_id NUMBER,
      T_date DATE,
      t_amount NUMBER(15,3)
    );

  -- Define cursor and table(array) of to hold COM CHARGE data
  TYPE CUR_COMtest IS REF CURSOR RETURN REC_COMtest;
  TYPE ARY_COMtest IS TABLE OF REC_COMtest;

  PROCEDURE P_GET_DATA_TEST
      (
        ar_My_Array OUT ARY_COMtest
      );

  PROCEDURE P_insert_DATA_TEST
  ;

END testPac1;

CREATE OR REPLACE PACKAGE BODY testPac1 AS

  PROCEDURE P_GET_DATA_TEST
      (
        ar_My_Array OUT ARY_COMtest
      )
      AS
       MyCur    CUR_COMtest;
       MyCur1   Cur_COMtest;
       I_total_sum NUMBER;
     BEGIN

      OPEN MyCur FOR
      SELECT loan_id, withd_date, withd_amount * rate/100 Interest_amount FROM t_withdrawal tw, t_rate
      WHERE tw.withd_date = t_rate.rate_date ;

      FETCH MyCur BULK COLLECT INTO ar_My_Array;

      close MyCur;

  END;

  PROCEDURE P_insert_DATA_TEST
  IS
    AR_MY_ARRAY ARY_COMtest;
  BEGIN

   testPac1.P_GET_DATA_TEST( AR_MY_ARRAY);

   FOR I IN 1..ar_My_Array.COUNT LOOP
     INSERT INTO t_interest (Loan_id, Interest_date, Interest_amount)
          SELECT ar_My_Array(I).Loan_id, ar_My_Array(I).t_date, ar_My_Array(I).t_amount
     FROM DUAL;
   END LOOP;

  END;

END testPac1;

Thanks,
Ferro