Home » SQL & PL/SQL » SQL & PL/SQL » Aggregate operation on array (11.2.0.1.0)
Aggregate operation on array [message #676310] Thu, 30 May 2019 04:10 Go to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
Hi All,

I use a type to create a cursor and use this cursor to run a query and fill its data into an array of the same type.
I need to use this array multiple times, each time to do a different insert.
1- Op1 and Op2 (as per the below example) insert the needed data however they give a (ORA-01002: fetch out of sequence) and I need to understand why and how to avoid that.
3- For Op3 I have the option to close and reopen the cursor and reuse it to fill the array with aggregate data that I need to insert of learn if there is a way to reuse the first array to achieve this goal.

My test case:

create table test1 
  (
    id number primary key,
    value0 number
  );

create table test2 
  (
    id number,
    value1 number,
    value2 number,
    fk_test1 number references test1(id)
  );

insert all 

  INTO test1 values (1, 500)
  INTO test1 values (2, 600)
  INTO test1 values (3, 700)
  INTO test1 values (4, 800)
  INTO test1 values (5, 900)
  INTO test2 values (1, 400, 70, 1)
  INTO test2 values (2, 400, 70, 2)
  INTO test2 values (3, 400, 70, 5)
  INTO test2 values (4, 400, 70, 5)
  INTO test2 values (5, 400, 70, 5)
select * from dual;

-- TESTPAC specification
CREATE OR REPLACE PACKAGE AFESD2.testPac AS

  TYPE REC_COMtest IS RECORD
    (
       ID      NUMBER(4),
      value0  NUMBER(6),
      value1         NUMBER(6),
      value2              NUMBER(6)
    );

  -- 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
  ( 
    I_NUM     IN NUMBER, 
    MyCur   OUT CUR_COMtest, 
    I_TOTAL_ROWS OUT NUMBER  
  );
end testpac;

CREATE OR REPLACE PACKAGE BODY AFESD2.testPac AS

  PROCEDURE P_GET_DATA_TEST 
  ( 
    I_NUM     IN NUMBER, 
    MyCur   OUT CUR_COMtest, 
    I_TOTAL_ROWS OUT NUMBER  
  )
  AS
    AR_MY_ARRAY ARY_COMtest;
    I_total_sum number;
  BEGIN
  
   OPEN MyCur FOR 
    SELECT  test1.id, value0, value1, value2 
    FROM  test1, test2
    WHERE test1.id = test2.FK_TEST1 ;

   I_total_rows:=0;

   --Op1: Normal fetch and use of ar_my_array to calculate a total value
   FETCH MyCur BULK COLLECT INTO ar_My_Array;
   FOR I IN 1..ar_My_Array.COUNT LOOP
    I_total_rows := I_total_rows+ ( ar_My_Array(I).value0 + ar_My_Array(I).value1 * ar_My_Array(I).value2);
   END LOOP;

   FOR I IN 1..ar_My_Array.COUNT LOOP
     INSERT INTO test1 (id, VALUE0) 
          SELECT 99+I , I_total_rows
     FROM DUAL; 
   END LOOP;
   
   I_total_sum :=0;
  
   --Op2: resue the array to calculate an aggregate value I_total_sum and insert it
   FOR I IN 1..ar_My_Array.COUNT LOOP
       I_total_sum := I_total_sum + ar_My_Array(I).value2;
   END LOOP;

   INSERT INTO test1 (id, VALUE0) 
          SELECT 77 , I_total_sum
   FROM DUAL; 
 
   --Op3: My main question, if there is a way to reuse the same array and get the needed resutl instead of running another query
--   CLOSE MyCur;    
--   OPEN MyCur FOR 
--    SELECT  FK_TEST1, sum(value0), sum(value1), sum(value2) 
--    FROM  test1, test2
--    WHERE test1.id = test2.FK_TEST1 
--    group by FK_TEST1;
--
--   FETCH MyCur BULK COLLECT INTO ar_My_Array;
--   FOR I IN 1..ar_My_Array.COUNT LOOP
--    INSERT INTO test1 (id, VALUE0) 
--          SELECT 1000+I , ar_My_Array(I).value2
--     FROM DUAL; 
--   END LOOP;

  END;

end testPac;


Thanks,
Ferro
Re: Aggregate operation on array [message #676313 is a reply to message #676310] Thu, 30 May 2019 05:02 Go to previous messageGo to next message
cookiemonster
Messages: 13726
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're getting fetch out of sequence because you've fetched all the rows already.
Once they're fetched they're fetched.
Re: Aggregate operation on array [message #676314 is a reply to message #676313] Thu, 30 May 2019 05:08 Go to previous messageGo to next message
cookiemonster
Messages: 13726
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also remember to remove schema names when posting code - we don't have your users.
Re: Aggregate operation on array [message #676317 is a reply to message #676310] Thu, 30 May 2019 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What about feedback in your previous topic(s)?

Re: Aggregate operation on array [message #676404 is a reply to message #676310] Sun, 09 June 2019 00:07 Go to previous messageGo to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
@cookiemonster

Thanks for your reply and sorry for my late response (emergency).
Quote:
Once they're fetched they're fetched.
I understand but I am only fetching once and then reusing the array. The last fetch in Opt3 is commented.

@Michel
Right you are, I did not respond with feedback in my past two cases due to priority shifts and load. Will try to fix that.

Thanks,
Ferro
Re: Aggregate operation on array [message #676405 is a reply to message #676404] Sun, 09 June 2019 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE OR REPLACE PACKAGE testPac AS
  2
  3    TYPE REC_COMtest IS RECORD
  4      (
  5         ID      NUMBER(4),
  6        value0  NUMBER(6),
  7        value1         NUMBER(6),
  8        value2              NUMBER(6)
  9      );
 10
 11    -- Define cursor and table(array) of to hold COM CHARGE data
 12    TYPE CUR_COMtest IS REF CURSOR RETURN REC_COMtest;
 13    TYPE ARY_COMtest IS TABLE OF REC_COMtest;
 14
 15  PROCEDURE P_GET_DATA_TEST
 16    (
 17      I_NUM     IN NUMBER,
 18      MyCur   OUT CUR_COMtest,
 19      I_TOTAL_ROWS OUT NUMBER
 20    );
 21  end testpac;
 22  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY testPac AS
  2
  3    PROCEDURE P_GET_DATA_TEST
  4    (
  5      I_NUM     IN NUMBER,
  6      MyCur   OUT CUR_COMtest,
  7      I_TOTAL_ROWS OUT NUMBER
  8    )
  9    AS
 10      AR_MY_ARRAY ARY_COMtest;
 11      I_total_sum number;
 12    BEGIN
 13
 14     OPEN MyCur FOR
 15      SELECT  test1.id, value0, value1, value2
 16      FROM  test1, test2
 17      WHERE test1.id = test2.FK_TEST1 ;
 18
 19     I_total_rows:=0;
 20
 21     --Op1: Normal fetch and use of ar_my_array to calculate a total value
 22     FETCH MyCur BULK COLLECT INTO ar_My_Array;
 23     FOR I IN 1..ar_My_Array.COUNT LOOP
 24      I_total_rows := I_total_rows+ ( ar_My_Array(I).value0 + ar_My_Array(I).value1 * ar_My_Array(I).value2);
 25     END LOOP;
 26
 27     FOR I IN 1..ar_My_Array.COUNT LOOP
 28       INSERT INTO test1 (id, VALUE0)
 29            SELECT 99+I , I_total_rows
 30       FROM DUAL;
 31     END LOOP;
 32
 33     I_total_sum :=0;
 34
 35     --Op2: resue the array to calculate an aggregate value I_total_sum and insert it
 36     FOR I IN 1..ar_My_Array.COUNT LOOP
 37         I_total_sum := I_total_sum + ar_My_Array(I).value2;
 38     END LOOP;
 39
 40     INSERT INTO test1 (id, VALUE0)
 41            SELECT 77 , I_total_sum
 42     FROM DUAL;
 43
 44    END;
 45
 46  end testPac;
 47  /

Package body created.

SQL> var num number
SQL> var cur refcursor
SQL> var total number
SQL> exec :num := 1

PL/SQL procedure successfully completed.

SQL> exec testPac.P_GET_DATA_TEST(:num, :cur, :total)

PL/SQL procedure successfully completed.

SQL> print
       NUM
----------
         1

ERROR:
ORA-01002: fetch out of sequence



no rows selected

     TOTAL
----------
    143800

SQL> select * from test1;
        ID     VALUE0
---------- ----------
         1        500
         2        600
         3        700
         4        800
         5        900
       100     143800
       101     143800
       102     143800
       103     143800
       104     143800
        77        350

11 rows selected.

SQL> select * from test2;
        ID     VALUE1     VALUE2   FK_TEST1
---------- ---------- ---------- ----------
         1        400         70          1
         2        400         70          2
         3        400         70          5
         4        400         70          5
         5        400         70          5

5 rows selected.
As cookiemonster said, you get ORA-01002 when you try to fetch the cursor another time (like SQL*Plus here).
This is the first point.

Re: Aggregate operation on array [message #676406 is a reply to message #676405] Sun, 09 June 2019 01:41 Go to previous messageGo to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
@Michel,

Thanks for the reply.


I thought I can have one Fetch to fill ar_My_Array and multiple loops over the filled array. Now I understand that I can use ar_My_Array in a loop once and inn case I need to loop over it again I will get ORA-01002.

Q1: So is there a way to perform multiple loops over a Fetched array? do I have to close and reopen the cursor? What do you recommend.

In fact my observation is that if I added
  Close MyCur;  --<-- here is the close that if added (ORA-01002) will not appear 
  --but will cause (ORA-01001: invalid cursor) when running P_insert_DATA_TEST

The error ORA-01002 disappears and I can have my multiple loops, however if I called this procedure (P_GET_DATA_TEST) from another procedure (P_insert_DATA_TEST) it will give ORA-01001: invalid cursor (which is logic as I already closed the cursor in P_GET_DATA_TEST)

so the actual question should be:
Q2: how can I use a fetched array of record type in multiple loops and still pass the cursor as a SP output parameter to a calling procedure?

Here is the updated example:
CREATE OR REPLACE PACKAGE BODY testPac AS

  PROCEDURE P_GET_DATA_TEST 
  ( 
    I_NUM     IN NUMBER, 
    MyCur   OUT CUR_COMtest, 
    I_TOTAL_ROWS OUT NUMBER  
  )
  AS
    AR_MY_ARRAY ARY_COMtest;
    I_total_sum number;
  BEGIN
  
   OPEN MyCur FOR 
    SELECT  test1.id, value0, value1, value2 
    FROM  test1, test2
    WHERE test1.id = test2.FK_TEST1 ;

   I_total_rows:=0;

   --Op1: Normal fetch and use of ar_my_array to calculate a total value
   FETCH MyCur BULK COLLECT INTO ar_My_Array;
   FOR I IN 1..ar_My_Array.COUNT LOOP
    I_total_rows := I_total_rows+ ( ar_My_Array(I).value0 + ar_My_Array(I).value1 * ar_My_Array(I).value2);
   END LOOP;

   FOR I IN 1..ar_My_Array.COUNT LOOP
     INSERT INTO test1 (id, VALUE0) 
          SELECT 99+I , I_total_rows
     FROM DUAL; 
   END LOOP;
   
   I_total_sum :=0;
  
   --Op2: resue the array to calculate an aggregate value I_total_sum and insert it
   FOR I IN 1..ar_My_Array.COUNT LOOP
       I_total_sum := I_total_sum + ar_My_Array(I).value2;
   END LOOP;

   INSERT INTO test1 (id, VALUE0) 
          SELECT 77 , I_total_sum
   FROM DUAL; 
 
   --Op3: My main question, if there is a way to reuse the same array and get the needed resutl instead of running another query
   CLOSE MyCur;    
   OPEN MyCur FOR 
    SELECT  FK_TEST1, sum(value0), sum(value1), sum(value2) 
    FROM  test1, test2
    WHERE test1.id = test2.FK_TEST1 
    group by FK_TEST1;

   FETCH MyCur BULK COLLECT INTO ar_My_Array;
   FOR I IN 1..ar_My_Array.COUNT LOOP
    INSERT INTO test1 (id, VALUE0) 
          SELECT 1000+I , ar_My_Array(I).value2
     FROM DUAL; 
   END LOOP;

  --Close MyCur;  --<-- here is the close that if added (ORA-01002) will not appear 
  --but will cause (ORA-01001: invalid cursor) when running P_insert_DATA_TEST

  END;


  PROCEDURE P_insert_DATA_TEST 
  ( 
    I_NUM     IN NUMBER 
  )
  AS
    AR_MY_ARRAY ARY_COMtest;
    MyCur CUR_COMtest;
    I_total_rows number;

  BEGIN
   I_total_rows:=0;

   P_GET_DATA_TEST(1, MyCur, I_total_rows);

   

   --Op1: Normal fetch and use of ar_my_array to calculate a total value
   FETCH MyCur BULK COLLECT INTO ar_My_Array;

   FOR I IN 1..ar_My_Array.COUNT LOOP
     INSERT INTO test1 (id, VALUE0) 
          SELECT 5000+I , I_total_rows
     FROM DUAL; 
   END LOOP; 
 
   close MyCur;

  END;

end testPac;



Thanks,
Ferro

Re: Aggregate operation on array [message #676407 is a reply to message #676405] Sun, 09 June 2019 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For the second point, as you already work on the cursor result array then you can build the result of the second one yourself:
SQL> delete test2;

5 rows deleted.

SQL> delete test1;

11 rows deleted.

SQL> insert all
  2    INTO test1 values (1, 500)
  3    INTO test1 values (2, 600)
  4    INTO test1 values (3, 700)
  5    INTO test1 values (4, 800)
  6    INTO test1 values (5, 900)
  7    INTO test2 values (1, 400, 70, 1)
  8    INTO test2 values (2, 400, 70, 2)
  9    INTO test2 values (3, 400, 70, 5)
 10    INTO test2 values (4, 400, 70, 5)
 11    INTO test2 values (5, 400, 70, 5)
 12  select * from dual;

10 rows created.

SQL> commit;

Commit complete.

SQL> CREATE OR REPLACE PACKAGE BODY testPac AS
  2
  3    PROCEDURE P_GET_DATA_TEST
  4    (
  5      I_NUM     IN NUMBER,
  6      MyCur   OUT CUR_COMtest,
  7      I_TOTAL_ROWS OUT NUMBER
  8    )
  9    AS
 10      AR_MY_ARRAY ARY_COMtest;
 11      I_total_sum number;
 12      ar_sum ARY_COMtest := ARY_COMtest();
 13      l_prev_id pls_integer := -1;
 14    BEGIN
 15
 16     OPEN MyCur FOR
 17      SELECT  test1.id, value0, value1, value2
 18      FROM  test1, test2
 19      WHERE test1.id = test2.FK_TEST1 ;
 20
 21     I_total_rows:=0;
 22
 23     --Op1: Normal fetch and use of ar_my_array to calculate a total value
 24     FETCH MyCur BULK COLLECT INTO ar_My_Array;
 25     FOR I IN 1..ar_My_Array.COUNT LOOP
 26      I_total_rows := I_total_rows+ ( ar_My_Array(I).value0 + ar_My_Array(I).value1 * ar_My_Array(I).value2);
 27      if ar_My_Array(I).id != l_prev_id then
 28        ar_sum.extend;
 29        ar_sum(ar_sum.count).value0 := 0;
 30        ar_sum(ar_sum.count).value1 := 0;
 31        ar_sum(ar_sum.count).value2 := 0;
 32        l_prev_id := ar_My_Array(I).id;
 33      end if;
 34      ar_sum(ar_sum.count).value0 := ar_sum(ar_sum.count).value0 + ar_My_Array(I).value0;
 35      ar_sum(ar_sum.count).value1 := ar_sum(ar_sum.count).value1 + ar_My_Array(I).value1;
 36      ar_sum(ar_sum.count).value2 := ar_sum(ar_sum.count).value2 + ar_My_Array(I).value2;
 37     END LOOP;
 38
 39     FOR I IN 1..ar_My_Array.COUNT LOOP
 40       INSERT INTO test1 (id, VALUE0)
 41            SELECT 99+I , I_total_rows
 42       FROM DUAL;
 43     END LOOP;
 44
 45     I_total_sum :=0;
 46
 47     --Op2: resue the array to calculate an aggregate value I_total_sum and insert it
 48     FOR I IN 1..ar_My_Array.COUNT LOOP
 49         I_total_sum := I_total_sum + ar_My_Array(I).value2;
 50     END LOOP;
 51
 52     INSERT INTO test1 (id, VALUE0)
 53            SELECT 77 , I_total_sum
 54     FROM DUAL;
 55
 56    --Op3: My main question, ...
 57    FOR I IN 1..ar_sum.COUNT LOOP
 58      INSERT INTO test1 (id, VALUE0) values(1000+I, ar_sum(I).value2);
 59    END LOOP;
 60
 61    END;
 62
 63
 64  end testPac;
 65  /

Package body created.

SQL> exec testPac.P_GET_DATA_TEST(:num, :cur, :total)

PL/SQL procedure successfully completed.

SQL> select * from test1;
        ID     VALUE0
---------- ----------
         1        500
         2        600
         3        700
         4        800
         5        900
       100     143800
       101     143800
       102     143800
       103     143800
       104     143800
        77        350
      1001         70
      1002         70
      1003        210

14 rows selected.
Re: Aggregate operation on array [message #676409 is a reply to message #676407] Sun, 09 June 2019 03:04 Go to previous messageGo to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
Thanks Michel but its strange that your updated procedure runs without (ORA-01002: fetch out of sequence)! it produces the same error at my end. Actually we've done nothing to stop this error as (according to my case) it only stops when the cursor is closed!
Re: Aggregate operation on array [message #676410 is a reply to message #676409] Sun, 09 June 2019 03:07 Go to previous messageGo to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
I mean I understand what you've done by creating ar_sum to be filled during the first loop and hold the sum of all columns and use it in the last loop to avoid another query. However I dont understand how did this stop (ORA-01002: fetch out of sequence). the same example actually gives error at my end.
Re: Aggregate operation on array [message #676412 is a reply to message #676410] Sun, 09 June 2019 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to copy and paste what you do as I did otherwise we can't help not knowing what you actually do.

But, if Oracle says that you are fetching out of sequence, you can bet it is right and this is what you do.

Re: Aggregate operation on array [message #676414 is a reply to message #676412] Sun, 09 June 2019 04:44 Go to previous messageGo to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
@Michel,

Sure but regarding:
Quote:

You have to copy and paste what you do as I did otherwise we can't help not knowing what you actually do.
I am using an Oracle client that has a GUI (dbForge) that's why I did not post also depending on my comment
Quote:
Actually we've done nothing to stop this error as
as I observe there is nothing changed from your previous example that produced the same error.

Regarding:
Quote:

But, if Oracle says that you are fetching out of sequence, you can bet it is right and this is what you do.
I didnt get what you mean. In fact this is my question. Is there a way to get rid of this error without having to close the cursor (as closing the cursor will affect the calling procedure). Or do you mean to just accept the error as it does not affect the insertion or the calling procedure?

Thanks,
Ferro
Re: Aggregate operation on array [message #676421 is a reply to message #676414] Sun, 09 June 2019 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Get rid of the error? Yes, change the code.
As I showed, your posted code does NOT result to the error UNLESS you are fetching the output cursor which has already fetched all rows.
The problem is in the calling program NOT in what you posted.

Re: Aggregate operation on array [message #676429 is a reply to message #676421] Sun, 09 June 2019 06:09 Go to previous messageGo to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
Maybe I am missing something or cant see something!

Quote:
As I showed, your posted code does NOT result to the error UNLESS you are fetching the output cursor which has already fetched all rows.
You already had the same error as shown in your message (dated Sun, 09 June 2019 01:06) with a similar code (except for the added ar_sum which is not related to Fetches).

I will review and compare the two codes once more and see why I am getting the same error after copying and pasting your procedure to my package.

Thanks a lot,
Ferro
Re: Aggregate operation on array [message #676432 is a reply to message #676429] Sun, 09 June 2019 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
You already had the same error as shown in your message (dated Sun, 09 June 2019 01:06) with a similar code (except for the added ar_sum which is not related to Fetches).
Once more, I have NOT the error because I called the procedure.
See:
SQL> exec testPac.P_GET_DATA_TEST(:num, :cur, :total)

PL/SQL procedure successfully completed.
I have the error BECAUSE I fetch in SQL*Plus the cursor that has already fetched all rows (in both cases):
SQL> print
       NUM
----------
         1

ERROR:
ORA-01002: fetch out of sequence



no rows selected

     TOTAL
----------
    143800
Your output cursor variable is just useless, the only thing you do is to close it, remove it from the procedure specification and close the cursor inside this procedure.

Re: Aggregate operation on array [message #676436 is a reply to message #676432] Sun, 09 June 2019 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think I understand, your tool has a kind of "autoprint" which may you think there is an error is in the code when it is the tool itself that generates the error afterwards:
SQL> set autoprint on
SQL> exec testPac.P_GET_DATA_TEST(:num, :cur, :total)

PL/SQL procedure successfully completed.

     TOTAL
----------
    143800

ERROR:
ORA-01002: fetch out of sequence



no rows selected

       NUM
----------
         1

[Updated on: Sun, 09 June 2019 07:44]

Report message to a moderator

Re: Aggregate operation on array [message #676450 is a reply to message #676436] Mon, 10 June 2019 00:49 Go to previous messageGo to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
Thanks a lot Michel
Now I learned the reason for this silly error.

For case completion purpose, here is the updated example after I followed your recommended logic (single-fetch) approach (I just made it simpler by removing ar_sum aggregation example):
Quote:

For the second point, as you already work on the cursor result array then you can build the result of the second one yourself
My updated example:
-- TESTPAC specification
CREATE OR REPLACE PACKAGE testPac AS

  TYPE REC_COMtest IS RECORD
    (
       ID      NUMBER(4),
      value0  NUMBER(6),
      value1         NUMBER(6),
      value2              NUMBER(6)
    );

  -- 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
      (
        I_NUM     IN NUMBER,
        ar_My_Array out ARY_COMtest,
        I_TOTAL_ROWS OUT NUMBER
      );

  PROCEDURE P_insert_DATA_TEST 
  ( 
    I_NUM     IN NUMBER 
  );
   
end testpac;

CREATE OR REPLACE PACKAGE BODY testPac AS


  PROCEDURE P_GET_DATA_TEST
      (
        I_NUM     IN NUMBER,
        ar_My_Array out ARY_COMtest,
        I_TOTAL_ROWS OUT NUMBER
      ) 
      AS 
       MyCur    CUR_COMtest;
       I_total_sum number;
     BEGIN
 
      OPEN MyCur FOR
       SELECT  test1.id, value0, value1, value2
       FROM  test1, test2
       WHERE test1.id = test2.FK_TEST1 ;
 
      I_total_rows:=0;
 
      --Op1: Normal fetch and use of ar_my_array to calculate a total value
      FETCH MyCur BULK COLLECT INTO ar_My_Array;
      FOR I IN 1..ar_My_Array.COUNT LOOP
       I_total_rows := I_total_rows+ ( ar_My_Array(I).value0 + ar_My_Array(I).value1 * ar_My_Array(I).value2);
      END LOOP;
 
      FOR I IN 1..ar_My_Array.COUNT LOOP
        INSERT INTO test1 (id, VALUE0)
             SELECT 99+I , I_total_rows
        FROM DUAL;
      END LOOP;
 
      I_total_sum :=0;
 
      --Op2: resue the array to calculate an aggregate value I_total_sum and insert it
      FOR I IN 1..ar_My_Array.COUNT LOOP
          I_total_sum := I_total_sum + ar_My_Array(I).value2;
      END LOOP;
 
      INSERT INTO test1 (id, VALUE0)
             SELECT 77 , I_total_sum
      FROM DUAL;
 
     --Op3: My main question, ...
     FOR I IN 1..ar_My_Array.COUNT LOOP
       INSERT INTO test1 (id, VALUE0) values(1000+I, ar_My_Array(I).value0);
     END LOOP;
     
     Close MyCur;
 
  END;

  PROCEDURE P_insert_DATA_TEST 
  ( 
    I_NUM     IN NUMBER 
  )
  AS
    AR_MY_ARRAY ARY_COMtest;
    --MyCur CUR_COMtest;
    I_total_rows number;

  BEGIN
   I_total_rows:=0;

   P_GET_DATA_TEST(1, AR_MY_ARRAY, I_total_rows);


   --Op4: Normal fetch and use of ar_my_array to calculate a total value
   --FETCH MyCur BULK COLLECT INTO ar_My_Array;
   FOR I IN 1..ar_My_Array.COUNT LOOP
     INSERT INTO test1 (id, VALUE0) 
          SELECT 5000+I , I_total_rows
     FROM DUAL; 
   END LOOP; 
 

  END;

end testPac;

Thanks a lot,
Ferro
Re: Aggregate operation on array [message #676458 is a reply to message #676450] Mon, 10 June 2019 04:35 Go to previous messageGo to next message
cookiemonster
Messages: 13726
Registered: September 2008
Location: Rainy Manchester
Senior Member
You don't need to loop over the array twice to calculate I_total_rows and I_total_sum, they can both be done in a single pass.

And this:
FOR I IN 1..ar_My_Array.COUNT LOOP
  INSERT INTO test1 (id, VALUE0)
    SELECT 99+I , I_total_rows
  FROM DUAL;
END LOOP;
Would be better coded as:
INSERT INTO test1 (id, VALUE0)
SELECT 99 + rownum , I_total_rows
FROM DUAL CONNECT BY LEVEL <= ar_My_Array.COUNT;
Re: Aggregate operation on array [message #676467 is a reply to message #676458] Mon, 10 June 2019 23:52 Go to previous messageGo to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
Thanks cookiemonster for the idea

Now I have a new unexpected problem after I changed the parameter from cursor to array.
I face no problem when I execute P_insert_DATA_TEST (which calls P_GET_DATA_TEST), however when I run P_GET_DATA_TEST independently I get OCI-22303: type "TESTPAC.ARY_COMTEST" not found.

I read about the error (https://forums.devart.com/viewtopic.php?t=17216) and failed to create a global record type and I think if I followed this track I will need to modify my type to Object type and use a table of this object type (lot of changes that I am not sure of).

My questions are:
1- Is this the only solution to avoid this error while calling P_GET_DATA_TEST independently or there is another explanation/solution?
2- If I followed the track recommended by the link, is there a way to create a global record type?
3- If not, is creating an Object and using a table of object (my suggested solution if I followed this track) the recommended approach?

Frankly I hope there is a solution that does not involve a big change.

Thanks,
Ferro

[Updated on: Tue, 11 June 2019 00:11]

Report message to a moderator

Re: Aggregate operation on array [message #676469 is a reply to message #676467] Tue, 11 June 2019 03:15 Go to previous messageGo to next message
cookiemonster
Messages: 13726
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you're using a front-end that calls this over OCI? What is the front-end?

PL/SQL types have the scope they have - they can only be used in PL/SQL. If they're declared in a package spec then everything that can see the package can use them, if they're in the body then they can only be used in that package.

If you want a type that's accessible outside of PL/SQL then you need to use a SQL object type.
Re: Aggregate operation on array [message #676470 is a reply to message #676469] Tue, 11 June 2019 03:56 Go to previous message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
Dear cookiemonster,

Yes I am using C# .net to call a web-service that calls the stored procedure.

I will try to use ojbect type in my example and will post the case.

Thanks a lot,
Ferro
Previous Topic: PLS-00306: wrong number or types of arguments in call to 'FOPEN' (CM merged 2)
Next Topic: Backup
Goto Forum:
  


Current Time: Thu Nov 21 23:23:07 CST 2019