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.