Display .sql output from OracleConnection C#

Question

There's simple .sql file like this:-

helloworld.sql

DECLARE 
   message  varchar2(20):= 'Hello, World!'; 
BEGIN 
   dbms_output.put_line(message); 
END;

I want to call the file in C# console app, catch and display the output in console. Supposedly, it should just output Hello, world! in the console. This is what I've tried so far.

Program.cs

string sqlConnectionString = @"User Id=user;Password=password123;Data Source=xxxx.xxx.COM";
string script = File.ReadAllText(@"\\test\\helloworld.sql");
try
{
    using (OracleConnection con = new OracleConnection())
    {
        con.ConnectionString = sqlConnectionString;
        con.Open();
        var cmd = new OracleCommand(script, con);
        var result = cmd.ExecuteScalar();
        Console.Write(result.ToString());
    }
}
catch (SqlException e)
{
    Console.WriteLine("SQL Exception: " + e.Message);
}
catch (Exception e)
{
    Console.WriteLine("Exception: " + e.Message);
}

But I got an Exception: Object reference not set to an instance of an object. Then I'm aware about this, so I tried using ExecuteNonQuery() but because there's actually nothing I retrieve from database, the output is -1.

I just want to know how can I capture the output Hello, world! from the .sql file and print it in the C# console?

UPDATE:

After go through the comments, I got the idea to try search about DBMS_OUTPUT. This answer help me to print the output successfully. Thanks.

Naturalist

which line did you get the exception? – Lofty

this might answer your question : Answer here – Longsighted

@Lofty right after this line: Console.Write(result.ToString()); it goes directly to catch execption block. – Naturalist

If whatever's written by dbms_output.put_line is going to be made available to you, I believe it'll be sent to the handler you attach to the connection's InfoMessage event. – Belshin

The result may return null if no data is present in result set when ExecuteScalar executed. Also as far as I know, you can retrieve the messages with DBMS_OUTPUT.GET_LINE, but make sure that it runs on the same Oracle session. – Claudianus

@Belshin do you have any link related to this? – Naturalist

No, it's an assumption based on how SqlConnection works. – Belshin

I found a reference about how to use dbms_output.get_line in ODP.NET, you can check it here. You need to provide OracleParameter with output direction to get output text from PL/SQL command. – Claudianus

I confirmed that SQL Server's PRINT statements are sent to SqlConnection's InfoMessage event handler. I believe it's reasonable that ODP.NET might do the same with dbms_output.put_line. Try this: at the start of your using block, var outputLines = new List<string>(); con.InfoMessage += (s, e) => outputLines.Add(e.Message); and then use ExecuteNonQuery to run your original script. – Belshin

The documentation for InfoMessage says "This event is triggered for any message or warning sent by the database." so that test should confirm if that includes what's written using dbms_output.put_line. – Belshin

@Belshin i'm using OracleConnection instead of SqlConnection can it still be done? – Naturalist

I understand that, which is why I said "it's reasonable that ODP.NET might do the same," in order to recognize that you're using something different. You should try what I suggested. I don't have an Oracle installation available, or else I would have tried it and made it an answer if it worked. You have enough information to run the test yourself. – Belshin

@Belshin i've tried it inside the using block. it does not show the output. – Naturalist

Did you look at what's in outputLines after it ran? All that code is supposed to do is accumulate the lines. After ExecuteNonQuery, you can iterate through outputLines and do anything you want with them, such as writing them to the console. – Belshin

@Belshin yes, after ExecuteNonQuery(), the outputlines does not contains anything. – Naturalist