PL/SQL- How to run a 3rd party Stored Proc for each SYS_REFCURSOR row and still pass the cursor to 3rd party app (C# WPF) as efficiently as possible

Question

Trying to accomplish 2 things, while avoiding any unnecessary overhead:

1. Query multiple tables with Joins to pass SYS_REFCURSOR to a C# WPF for order processing.
2. For each item in that same query result, send to a 3rd party Stored Procedure, of which a workaround is not an option, nor is even viewing the code. Its function is to update a status of each record.

I can get either to work, but not both. I realize I may have no choice but to use global temporary table or other option (which is best?) So the resulting answer may be more of "what path should I choose", as much as "how to do it".

Tried a single SP: #2 worked, but #1 did not - no error, just returned no results, when there are results. Then tried an autonomous function:

PROCEDURE SP_GET_ORDERS( O_ORDERS SYS_REFCURSOR ) AS
v_ticket_id  NUMBER; -- used for attempt using loop inside this SP
cursor_id    NUMBER; -- same
BEGIN
OPEN O_ORDERS FOR  --(simplified)
  SELECT
        a.TICKET_ID, a.ORDER_NO, a.CURR_STATUS, 
        b.STATUS
  FROM tableA@DBLINK_A a
      JOIN tableB@DBLINK_B b ON a.ORDER_NO = b.ORDER_NO
  WHERE
        a.ORDER_TYPE = 'MINE'
        AND a.CURR_STATUS IN ('OPEN', 'PENDING');
--Above alone will work for #1, of course

--Loop  (When added this will work for #2, but then #1 breaks - does it 'wreck' the sys refcursor ?)
  dbms_sql.define_column(curid,1,v_ticket_id);
  WHILE DBMS_SQL.FETCH_ROWS(cursor_id) > 0 LOOP
    DBMS_SQL.COLUMN_VALUE(cursor_id, 1, v_ticket_id);
    REMOTE_SCHEMA.SP_REMOTE_UPDATE@MY_REMOTE_DBLINK(v_ticket_id);
  END LOOP;

--Attempt #3 (after many searches here and elsewhere) I wrote an autonomous transaction 
--and replaced the loop above with the call to it, passing the sys refcursor:
SP_ASSIGN_STATUS(O_ORDERS);
END SP_GET_ORDERS;

PROCEDURE SP_ASSIGN_STATUS( O_ORDERS IN SYS_REFCURSOR )
IS PRAGMA autonomous_transaction;
lv_cursor  SYS_REFCURSOR;
v_ticket_id  NUMBER;
cursor_id        NUMBER;

BEGIN
  lv_cursor := O_ORDERS;
  cursor_id := DBMS_SQL.TO_CURSOR_NUMBER(lv_cursor);
  
  dbms_sql.define_column(cursor_id,1,v_ticket_id); 
  WHILE DBMS_SQL.FETCH_ROWS(cursor_id) > 0 LOOP
    DBMS_SQL.COLUMN_VALUE(cursor_id, 1, v_ticket_id);
    REMOTE_SCHEMA.SP_REMOTE_UPDATE@MY_REMOTE_DBLINK(v_ticket_id);
  END LOOP;
END SP_ASSIGN_STATUS;

The autonomous transaction does the same thing as the loop in the original SP-- it runs #2 updates, but does not return any results for #1

Ideally I'd like to use 1 query, retrieve the 1st column (TICKET_ID), pass that to the loop to update the SP/remote table, while preserving the SYS REFCURSOR (that works) to pass same result to the WPF app.

Any suggestions? New to PL/SQL and still grasping at the objects, scope, lifespan, use, etc. Thanks in advance!

Answer 1

I think you're thinking that a cursor is a set of rows already predefined and fetched into some sort of internal array. It's not; it's a set of instructions on how to fetch the rows at the point the cursor was opened. Once you fetch a row, the cursor moves on to how to fetch the next row, meaning you cannot fetch the previous row again.

Think of it like a bucket of multicoloured balls - if I tell you to hand me the first red ball in the bucket, once I have the ball in my hand, I can't instruct anyone else to get me that exact ball, since it's no longer in the bucket. (It's not a great analogy, as what happens in the db is way more complicated, but hopefully it gets the point across!)

In your case, it seems like you're processing the rows one-by-one (i.e. procedurally rather than set-based), and therefore it makes more sense for the thing that starts the transaction (your app) to handle all the processing for that row, including updating the status of that row, especially since the procedure that updates the row is doing row-by-row processing rather than one big update statement.

It's good that you're trying to think set-based and maximise the work the db does, but it doesn't really seem the best approach here, particularly in the case when there's a failure in the processing of a row - how would the app be able to control the status of that row if it's already been updated by the db code? Much more sensible to let the app set the appropriate status, be that success or failure.