Oracle 11g - Add query results to SYS_REFCURSOR in a LOOP

Question

Currently, I'm working on creating a stored procedure that I can pass a list of strings to from my C# application using a DataReader. So part of it is also properly declaring that Associative Array so the stored procedure can accept the data.

Here is what I currently have (I'm not too concerned about the C# side of things at the moment):

CREATE OR REPLACE PACKAGE TEST_PACKAGE_01
AS
    TYPE t_string_list is table of VARCHAR2(4000) index by pls_integer;

    PROCEDURE SP_TEST_01(in_list IN t_string_list, out_cursor OUT SYS_REFCURSOR);

END TEST_PACKAGE_01;
/

CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE_01
AS
    PROCEDURE SP_TEST_01(in_list IN t_string_list, out_cursor OUT SYS_REFCURSOR)
    IS

    BEGIN
        OPEN out_cursor;
        FORALL indx IN 1..in_list.COUNT
            -- I have tried numerous different things here with no success, this is just what I have in my latest iteration
            INSERT INTO out_cursor Values (SELECT * FROM SOME_TABLE WHERE SOME_COLUMN = in_list(indx));
        CLOSE out_cursor;

    END SP_TEST_01;
END TEST_PACKAGE_01;

Within the body of the procedure, I managed to get it to return a SYS_REFCURSOR just fine, albeit just a test without any input. Something kind of like:

OPEN out_cursor FOR
    SELECT * FROM SOME_TABLE;

I can't use a simple IN statement for the query against SOME_TABLE since in_list can potentially contain thousands of records. Ideally, I'd like to populate out_cursor with one statement, instead of performing a loop.

Cursors are pretty new territory for me, so maybe it isn't possible to do what I'm thinking with them, but I haven't seen anything to the contrary.

You can't use a PL/SQL table from SQL (in 11g, things improve a bit in 12c); which is what you're attempting to do here. And you can't insert into a cursor. Are you able to create a schema-level type instead? — Alex Poole
– Alex Poole, Commented Aug 20, 2019 at 17:42
Yeah, I think so. I'm not really familiar with creating types either though. — Dortimer
– Dortimer, Commented Aug 20, 2019 at 17:44

Alex Poole · Accepted Answer · 2019-08-20 18:02:42Z

3

In 11g you can't access the PL/SQL table type from SQL, even if that SQL is within a PL/SQL block. You can create a schema-level type instead:

CREATE OR REPLACE TYPE t_string_list AS TABLE OF VARCHAR2(4000)
/

Then your package becomes:

CREATE OR REPLACE PACKAGE TEST_PACKAGE_01
AS
    PROCEDURE SP_TEST_01(in_list IN t_string_list, out_cursor OUT SYS_REFCURSOR);
END TEST_PACKAGE_01;
/

CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE_01
AS
    PROCEDURE SP_TEST_01(in_list IN t_string_list, out_cursor OUT SYS_REFCURSOR)
    IS
    BEGIN
        OPEN out_cursor FOR
            SELECT *
            FROM SOME_TABLE
            WHERE SOME_COLUMN IN (
              SELECT * FROM TABLE(in_list)
            );
    END SP_TEST_01;
END TEST_PACKAGE_01;
/

or with a join instead of IN:

CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE_01
AS
    PROCEDURE SP_TEST_01(in_list IN t_string_list, out_cursor OUT SYS_REFCURSOR)
    IS
    BEGIN
        OPEN out_cursor FOR
            SELECT st.*
            FROM TABLE(in_list) t
            JOIN SOME_TABLE st ON st.SOME_COLUMN = t.COLUMN_VALUE;
    END SP_TEST_01;
END TEST_PACKAGE_01;
/

The TABLE() is a table collection expression.

db<>fiddle with made-up table and anonymous block to test.

edited Aug 20, 2019 at 18:02

answered Aug 20, 2019 at 17:52

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dortimer Over a year ago

This is a bit off-topic I suppose, but are you familiar at all with making parameters in ODP.Net (specifically C#, but I can read VB.Net just fine)?

Alex Poole Over a year ago

Not really, but this might give you pointers, or other questions have probably been asked about it.

Collectives™ on Stack Overflow

Oracle 11g - Add query results to SYS_REFCURSOR in a LOOP

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related