Retreiving a subset of data from an Oracle database-CodePudding

I have an Oracle 19c database that I am trying to pull data from using a package procedure. It is working, but I am new to Oracle, previously very experienced in Microsoft SQL Server. The C# code I have below works and calls my stored procedure successfully. However, the stored procedure returns over one million rows. I do not want to have a DataSet filled with over a million rows because obviously this is very slow. I would like to return is a subset, like offset X rows and take N rows. Basically I want to do something like this:

SELECT * FROM STORED_PROCEDURE OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY

But I want to do it using my package procedure. Here is my C# code:

    public async Task<List<DbModels.DocumentWipList>> GetWipDocumentsAsync(string sort = "limited_dodiss ASC")
    {
        using (var connection = new OracleConnection(_configuration.GetConnectionString("OracleDev")))
        {
            using (var command = connection.CreateCommand())
            {
                connection.Open();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "PKG_GET_COMPONENT_DETAIL.pr_get_wip_comp_list_sorted";
                command.Parameters.Add("arg_sort", OracleDbType.Varchar2).Value = sort;
                command.Parameters.Add("io_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output; 

                using (var da = new OracleDataAdapter())
                {
                    da.SelectCommand = command;
                    var dt = new DataTable();
                    await Task.Run(() => da.Fill(dt)); 

                    return MapDocumentWipList(dt);
                }
            }
        }
    }

It should be noted that I cannot modify the package procedure. I am hoping there is an easy way to do this, perhaps by somehow wrapping the package procedure as a subquery for a SELECT query.

CodePudding user response：

In Oracle, a cursor is effectively a pointer to a memory address on the database server where the database stores the query the cursor executes and the current execution state of the cursor (a cursor never stores the result set waiting to be read, that is effectively generated on the fly as each row is read). You can read from a cursor once but you cannot change the cursor or rewind it.

I would like to return is a subset, like offset X rows and take N rows.

Don't use await Task.Run(() => da.Fill(dt));. Instead, read and ignore X rows for the cursor and then read and store N rows.

However, it would be better to change the procedure to allow pagination.

What about filtering? One of the columns that comes back is OWNER_NAME. What if I wanted to pull just the rows WHERE OWNER_NAME LIKE 'R%' or something like that?

It is impossible to modify a cursor, if you have to read it then you will need to read ALL the rows for the cursor and discard the rows that do not match your condition. So again, don't use await Task.Run(() => da.Fill(dt));, which would load all the rows into memory, instead read the rows one-by-one and only keep the ones you want in memory and forget the rest.

You can write a second procedure in PL/SQL to wrap around the cursor or you can do the processing in a third-party application like C# but you will need to read the cursor. All that changes between doing it in PL/SQL or C# is whether the processing occurs on the database server or on the third-party application server.

For example, if you have the table:

CREATE TABLE table_name (a, b, c) AS
SELECT LEVEL, CHR(64   LEVEL), DATE '1970-01-01'   LEVEL - 1
FROM   DUAL
CONNECT BY LEVEL <= 10;

And an existing procedure (that cannot be changed):

CREATE PROCEDURE get_cursor (
  o_cursor OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN o_cursor FOR
    SELECT * FROM table_name;
END;
/

Then you can create the types:

CREATE TYPE table_name_type IS OBJECT(
  a NUMBER,
  b VARCHAR2(1),
  c DATE
);

CREATE TYPE table_name_array IS TABLE OF table_name_type;

Which allows you to create a pipelined function:

CREATE FUNCTION wrap_cursor_fn (
  i_cursor IN  SYS_REFCURSOR
) RETURN table_name_array PIPELINED
IS
  v_a table_name.a%TYPE;
  v_b table_name.b%TYPE;
  v_c table_name.c%TYPE;
BEGIN
  LOOP
    FETCH i_cursor INTO v_a, v_b, v_c;
    EXIT WHEN i_cursor%NOTFOUND;
    PIPE ROW (table_name_type(v_a, v_b, v_c));
  END LOOP;
  CLOSE i_cursor;
EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    CLOSE i_cursor;
  WHEN OTHERS THEN
    CLOSE i_cursor;
    RAISE;
END;
/

Which then allows you to use the returned pipelined collection in an SQL statement and read one cursor into another cursor and apply filters to it:

CREATE PROCEDURE wrap_cursor_proc (
  i_cursor IN  SYS_REFCURSOR,
  o_cursor OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN o_cursor FOR
    SELECT *
    FROM   TABLE(wrap_cursor_fn(i_cursor))
    WHERE  MOD(a, 3) = 0;
END;
/

Which you can then read:

DECLARE
  v_cur1 SYS_REFCURSOR;
  v_cur2 SYS_REFCURSOR;
  v_a table_name.a%TYPE;
  v_b table_name.b%TYPE;
  v_c table_name.c%TYPE;
BEGIN
  get_cursor(v_cur1);
  wrap_cursor_proc(v_cur1, v_cur2);

  LOOP
    FETCH v_cur2 INTO v_a, v_b, v_c;
    EXIT WHEN v_cur2%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( v_a || ', ' || v_b || ', ' || v_c );
  END LOOP;
  CLOSE v_cur2;
END;
/

and outputs:

3, C, 03-JAN-70
6, F, 06-JAN-70
9, I, 09-JAN-70

fiddle

It is a lot of work to read a cursor within the database and get it back into a format that can be used in an SQL query so that you can wrap it in another cursor just to apply a filter. It would be much simpler to just duplicate the original procedure and add the necessary filter condition to it.