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 |
|
OraFerro
Messages: 433 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 #676405 is a reply to message #676404] |
Sun, 09 June 2019 01:06 |
|
Michel Cadot
Messages: 68658 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 |
|
OraFerro
Messages: 433 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 |
|
Michel Cadot
Messages: 68658 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 #676436 is a reply to message #676432] |
Sun, 09 June 2019 07:42 |
|
Michel Cadot
Messages: 68658 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 |
|
OraFerro
Messages: 433 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 |
cookiemonster
Messages: 13925 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 |
|
OraFerro
Messages: 433 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 |
cookiemonster
Messages: 13925 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.
|
|
|
|
Goto Forum:
Current Time: Mon Jun 10 06:56:03 CDT 2024
|