Read from C# script output from Oracle

Question

I can't figure out by myself how I can read script output from Oracle in my c# app. Please help someone with that. The code sample is below:
...

 List<DateTime> dtList = new List<DateTime>();                                         
    using (OracleConnection connection = new OracleConnection (connectionString))
         {      
         connection.Open ();
         string sqlText = @" SET SERVEROUTPUT ON;
            Declare  
            start_date        TIMESTAMP;
            return_date_after TIMESTAMP;
            next_run_date     TIMESTAMP;
            BEGIN 
            start_date :=
            to_timestamp_tz('01-JAN-2003 10:00:00','DD-MON-YYYY HH24:MI:SS');
            return_date_after := start_date;
            FOR i IN 1..5 LOOP
            DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(  
            'freq=weekly; BYDAY=MON,TUE,WED',
            start_date, return_date_after, next_run_date);
            DBMS_OUTPUT.PUT_LINE('next_run_date: ' || next_run_date);
            return_date_after := next_run_date;
            END LOOP;
            END;";                                          
        OracleCommand command = new OracleCommand (sqlText, connection);
        OracleDataReader reader = command.ExecuteReader ();
           while (reader.Read ())
             {
                 dtList.Add((DateTime)reader [ "next_run_date" ]);
             }   
        }           
    ... But it just doesn't enter the while loop, because script output is not in rows. How can I put the following output in rows of some table or maybe read them directly from my app. Thanks 
next_run_date: 06-JAN-03 10.00.00.000000 AM
next_run_date: 07-JAN-03 10.00.00.000000 AM
next_run_date: 08-JAN-03 10.00.00.000000 AM
next_run_date: 13-JAN-03 10.00.00.000000 AM
next_run_date: 14-JAN-03 10.00.00.000000 AM

There's an answer on this here: stackoverflow.com/a/12303416/570191 — Adriano Carneiro
– Adriano Carneiro, Commented Sep 13, 2013 at 15:35

Vadim K. · Accepted Answer · 2013-09-18 18:06:19Z

1

I need just to create some table, let's say 'INTERVALS' with some columns, let's say 'To_Task_ID' and 'Trigs' and insert to the procedure simple Insert command as follows.

        DBMS_OUTPUT.PUT_LINE('next_run_date: ' || next_run_date);
        Insert into INTERVALS (to_task_id, trigs) values (i, next_run_date);
        return_date_after := next_run_date;
    END LOOP;
END;  // After which I can easily get this data.

edited Sep 18, 2013 at 18:06

Vadim K.

2,61122 silver badges30 bronze badges

answered Sep 13, 2013 at 22:02

Dima

511 silver badge11 bronze badges

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

1 Comment

Saqib Over a year ago

nice quick trick. Specially if you just want to debug plsql function from C#.

Collectives™ on Stack Overflow

Read from C# script output from Oracle

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related