Execute Pipelined Functions in ODP.NET

Question

I want to select data from a pipelined function in C# "just in time". That means the function pipes a row every second (like a status report) an I would like to fetch the data in C# immediately.

So far I have the following:

        Oracle.DataAccess.Client.OracleConnection con = new Oracle.DataAccess.Client.OracleConnection("my_connection_string");

        con.Open();

        Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("SELECT * FROM TABLE(MYPACKAGE.TEST_PIPELINE(10))", con);
        cmd.CommandType = CommandType.Text;

        Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader();

        reader.FetchSize = 244;  //record-size in Bytes

        while (reader.Read())
        {
            System.Diagnostics.Debug.WriteLine("Now: " + DateTime.Now.ToString("HH:mm:ss.ffff"));
            System.Diagnostics.Debug.WriteLine("ID: " + reader.GetValue(0));
            System.Diagnostics.Debug.WriteLine("Text: " + reader.GetValue(1));
        }

My sample function returns n (the only Function-Parameter) rows with a sleep of one second before the PIPE ROW. If I run this code I have to wait ten seconds until I get ten rows at once.

BUT if I run it a second time it works perfectly, I get one row every second (ten rows in total). Maybe just because of Statement Caching, when I add the line

cmd.AddToStatementCache = false; 

I get a blocks of ten lines even at the second run.

So the question is: Anyone has an idea how to get the ten lines "just in time" (line by line every second) when I execute the code for the first time?

Thanks a lot!

Cheers Christian

Answer 1

I tried to reproduce your function.

CREATE OR REPLACE PACKAGE BODY PHXDBA.PIPETEST as
    FUNCTION TENSECOND RETURN TENSECOND_TT 
    PIPELINED AS
    ctr NUMBER;
    ts_ot TENSECOND_OT := TENSECOND_OT(NULL);
    BEGIN
        FOR ctr IN 1..10
        LOOP
            ts_ot.CNT := ctr;
            PIPE ROW (ts_ot);
            SYS.DBMS_LOCK.SLEEP(1);
        END LOOP;
    END;
END PIPETEST;
/

Unfortunately this always returns after 10 seconds, even in RapidSQL. So either I've implemented it wrong or the SLEEP function disrupts the piped rows coming back.