Home » SQL & PL/SQL » SQL & PL/SQL » Loop after fetch on generic SYS_REFCURSOR (11.2.0.1.0)
Loop after fetch on generic SYS_REFCURSOR [message #676170] Sun, 19 May 2019 01:17 Go to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member

Hi All,

I have a procedure that returns a SYS_REFCURSOR out of a general select that is not based on a specific table where I can generate a type using %Type.
I want to return both the result cursor and also an aggregation (example number of rows in the below procedure). However I fail to generate an array of SYS_REFCURSOR to use it in the FETCH before looping.

I need help to know what I am missing and/or dont know in order to avoid repeating the select again to get the aggregate result.



CREATE OR REPLACE  PROCEDURE P_GET_DATA_TEST
  ( 
    I_NUM     IN NUMBER, 
    CUR_REF   OUT SYS_REFCURSOR, 
    I_TOTAL_ROWS OUT NUMBER  
  )
  AS
    TYPE ARY_ARRAY IS TABLE OF SYS_REFCURSOR;
    AR_MY_ARRAY ARY_ARRAY;
    
  BEGIN
  

    OPEN CUR_REF FOR 
    SELECT  ROWNUM 
    FROM ALL_OBJECTS
    WHERE ROWNUM <= i_num ;

   I_total_rows:=0;

   FETCH CUR_REF BULK COLLECT INTO ar_My_Array;
   FOR I IN 1..ar_My_Array.COUNT LOOP
   I_total_rows := I_total_rows + 1;
   END LOOP;

/*
I want to avoid:
    OPEN CUR_REF FOR 
    SELECT  count(ROWNUM ) into I_total_rows 
    FROM ALL_OBJECTS
    WHERE ROWNUM <= i_num ;
*/

   CLOSE CUR_REF;    


  END;
Re: Loop after fetch on generic SYS_REFCURSOR [message #676172 is a reply to message #676170] Sun, 19 May 2019 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you want the count of the rows returned by a query and a cursor on the result of this one to loop on it outside the procedure then you have to execute the query twice (actually, a query with COUNT and the real query) but take care that, in default transaction mode, Oracle does not guarantee you will get the same count twice if you have concurrent sessions modifying the tables you are querying.

The best way is then to add a "COUNT() OVER()" column to the ones you want but then you have the count only when you fetch the first row of the result set.

If your procedure is called from a program written in a programming language that supports scrollable cursor, you can then fetch the first row to get the count and scroll back to the beginning of the result set to handle the result set as usual.

Re: Loop after fetch on generic SYS_REFCURSOR [message #676173 is a reply to message #676172] Sun, 19 May 2019 02:14 Go to previous messageGo to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
Hi Michel,

Thanks for your reply.

In the real case I need to:
1- return a cursor with data (numeric and text)
2- sum the total of some numeric colums

The cursor runs a relatively expensive query (calculating and applying equations and business rules to get the final columns) . I wanted to avoid running the query again to get the needed aggregation.

Which is why I asked if reusing the cursor in a loop is possible the way I tried in the example.
Do you have any suggestions?

Thanks,
Ferro
Re: Loop after fetch on generic SYS_REFCURSOR [message #676174 is a reply to message #676173] Sun, 19 May 2019 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I already posted my suggestions.
If you want more you have to:
1/ Post a test case
2/ Specify clearly and in details the input and output of the procedure
3/ Tell the programming language you use calling the procedure

Do you need, outside the procedure, the aggregations BEFORE looping the cursor or not?

Re: Loop after fetch on generic SYS_REFCURSOR [message #676177 is a reply to message #676174] Sun, 19 May 2019 04:45 Go to previous messageGo to next message
OraFerro
Messages: 363
Registered: July 2011
Senior Member
Thanks Michel for your reply,

1- Please see the test case below.
2- One cursor carrying the select results and a variable that presents the sum of adding two variables and multiplying with the third.
3- .net C#
4- In the real case, there is no risk of data changes and the sum can be determined after the select.
In fact, in the real case, the sum is inserted is stored as parity to make sure that the data being inserted (same function is called to retrieve the data and parity) is similar to the data received in the first stage.
The actual data is an account statement which one user reviews and then sends a task for another user to approve and submit (insert). the two processes do not happen in the same time and the parity is used as a control.

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

create table test2 
  (
    id number,
    value1 number,
    value2 number,
    fk_test1 number references test1(id)
  );
  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)
insert all 


  INTO test2 values (1, 400, 70, 1)
  INTO test2 values (2, 400, 70, 2)
  INTO test2 values (3, 400, 70, 3)
  INTO test2 values (4, 400, 70, 4)
  INTO test2 values (5, 400, 70, 5)
select * from dual;

CREATE OR REPLACE  PROCEDURE P_GET_DATA_TEST
  ( 
    I_NUM     IN NUMBER, 
    CUR_REF   OUT SYS_REFCURSOR, 
    I_TOTAL_ROWS OUT NUMBER  
  )
  AS
    TYPE ARY_ARRAY IS TABLE OF SYS_REFCURSOR;
    AR_MY_ARRAY ARY_ARRAY;
    
  BEGIN
  

    OPEN CUR_REF FOR 
    SELECT  test1.id, value0, value1, value2 
    FROM  test1, test2
    WHERE test1.id = test2.FK_TEST1 ;

   I_total_rows:=0;

   FETCH CUR_REF 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;

  /* instead of using another cursor and 
    OPEN CUR_ANOTHER_CURSOR FOR 
    SELECT  sum( value0+ value1* value2 ) into I_total_rows
    FROM  test1, test2
    WHERE test1.id = test2.FK_TEST1 ;    
*/
   CLOSE CUR_REF;    


  END;

Thanks,
Ferro
Re: Loop after fetch on generic SYS_REFCURSOR [message #676178 is a reply to message #676177] Sun, 19 May 2019 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
4- In the real case, there is no risk of data changes and the sum can be determined after the select.

So you don't need the sum to be computed by the procedure, you can do it in the client program itself as you do it in your example procedure.


You can also do it in the following way:
SQL> select * from test1;
        ID     VALUE0
---------- ----------
         1        500
         2        600
         3        700
         4        800
         5        900

5 rows selected.

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

5 rows selected.

SQL> CREATE OR REPLACE  PROCEDURE P_GET_DATA_TESTCUR_REF (CUR_REF OUT SYS_REFCURSOR)
  2  AS
  3  BEGIN
  4    OPEN CUR_REF FOR
  5    SELECT  test1.id, value0, value1, value2,
  6            sum( value0+ value1* value2 ) over () TOTAL
  7    FROM  test1, test2
  8    WHERE test1.id = test2.FK_TEST1 ;
  9  END;
 10  /

Procedure created.

SQL> var c refcursor
SQL> exec P_GET_DATA_TESTCUR_REF (:c)

PL/SQL procedure successfully completed.

SQL> print c
        ID     VALUE0     VALUE1     VALUE2      TOTAL
---------- ---------- ---------- ---------- ----------
         1        500        400         70     143500
         2        600        400         70     143500
         3        700        400         70     143500
         4        800        400         70     143500
         5        900        400         70     143500

5 rows selected.
You get the total on the first fetch (and any subsequent one) instead of an OUT parameter of the procedure.

Re: Loop after fetch on generic SYS_REFCURSOR [message #676184 is a reply to message #676178] Mon, 20 May 2019 02:58 Go to previous messageGo to next message
cookiemonster
Messages: 13724
Registered: September 2008
Location: Rainy Manchester
Senior Member
A few general points:
1) Once you fetch a row it is fetched. You can't fetch it again without re-opening the cursor.
a) So that means the time the query takes is doubled because you are running it twice.
b) As Michel mentioned above oracle read consistency means that you may get different results the second time the query has run if the underlying data has changed.
c) To avoid a and b you should either do the calculation in the query itself as Michel showed above, or just have the client work it out.

2) table of sys_refcursor is meaningless - Oracle can't possibly define an array based on a refcuror in the declare section as it has no idea what the ref cursor looks like.
You need to know what contents of the ref cursor is and define appropriate variables.
Re: Loop after fetch on generic SYS_REFCURSOR [message #676186 is a reply to message #676184] Mon, 20 May 2019 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
1) Once you fetch a row it is fetched. You can't fetch it again without re-opening the cursor.

Unless your programming language supports "scrollable cursors" like Pro*C; I don't know if this is the case with C#.

[Updated on: Mon, 20 May 2019 03:27]

Report message to a moderator

Re: Loop after fetch on generic SYS_REFCURSOR [message #676187 is a reply to message #676186] Mon, 20 May 2019 03:31 Go to previous messageGo to next message
cookiemonster
Messages: 13724
Registered: September 2008
Location: Rainy Manchester
Senior Member
To be honest I was specifically talking about fetching in PL/SQL there.
Re: Loop after fetch on generic SYS_REFCURSOR [message #676199 is a reply to message #676187] Mon, 20 May 2019 11:47 Go to previous message
Bill B
Messages: 1909
Registered: December 2004
Senior Member
As michel said, simply use an analytic to add total columns at the end of the row. The totals columns would be for the entire query. You can just use them when you hit the end of the cursor.
Previous Topic: Error: ORA-00980: synonym translation is no longer valid
Next Topic: minus query using dblink
Goto Forum:
  


Current Time: Tue Nov 19 09:25:02 CST 2019