1

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.

15
  • which line did you get the exception? Commented Mar 11, 2019 at 6:23
  • this might answer your question : Answer here Commented Mar 11, 2019 at 6:25
  • @er-sho right after this line: Console.Write(result.ToString()); it goes directly to catch execption block. Commented Mar 11, 2019 at 6:27
  • 2
    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. Commented Mar 11, 2019 at 6:47
  • 1
    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. Commented Mar 11, 2019 at 7:34

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.