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

score 0 · Answer 1 · answered Nov 20 '14 at 19:23

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.

Execute Pipelined Functions in ODP.NET

1 Answers1