Display .sql output from OracleConnection C#

2019-08-03 04:10发布

问题:

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.