Skip to Main Content

General Development Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Unable to get results from a refcursor when using C# OracleDataReader

Derick RossellDec 6 2023 — edited Dec 6 2023

I have a query that uses a Sys_RefCursor to get rows from a stored procedure and then returns those rows using the DBMS_SQL.RETURN_RESULT function. I've noticed some strange behavior when trying to get the results of this type of query back from my C# application code using OracleCommand.ExecuteReader().

If I take a simple query like this, for example:

DECLARE
    P_CURSOR SYS_REFCURSOR;

BEGIN
    OPEN P_CURSOR FOR
        SELECT * FROM MY_TABLE
        WHERE ELEM_TYPE = 2;

    DBMS_SQL.RETURN_RESULT(P_CURSOR);
END;

I can create an OracleCommand for that query and read back the results using the OracleDataReader just fine. However, if I add a bind variable into the query and a parameter of the same name to the OracleCommand, I suddenly get 0 rows returned.

DECLARE
P_CURSOR SYS_REFCURSOR; 

BEGIN 
    OPEN P_CURSOR FOR 
    SELECT * FROM MY_TABLE 
    WHERE ELEM_TYPE = :V_ELEM_TYPE; 
    DBMS_SQL.RETURN_RESULT(P_CURSOR); 
END;

I can get results back as expected from queries that don't use refcursors, even when using parameters and bind variables, so I don't think there's anything wrong with the way I'm setting up and executing the OracleCommand on the C# side. I'm confused as to why the queries I have that use refcursors seem to work fine when I hardcode the variable values, but then stop returning rows when I pass the same values in using DbParameters. Why does the introduction of bind variables seems to break the DBMS_SQL.RETURN_RESULT function (or prevents the OracleDataReader from finding and reading the returned results).

Comments
Post Details
Added on Dec 6 2023
0 comments
75 views