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.

HNA
  • 107
  • 2
  • 14
  • which line did you get the exception? – er-sho Mar 11 '19 at 06:23
  • this might answer your question : [Answer here](https://stackoverflow.com/questions/50884783/can-we-have-2-different-oracle-dependency-from-2-different-database-in-a-single/50965911#50965911) – ARr0w Mar 11 '19 at 06:25
  • @er-sho right after this line: `Console.Write(result.ToString());` it goes directly to catch execption block. – HNA Mar 11 '19 at 06:27
  • ***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. – madreflection Mar 11 '19 at 06:33
  • 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. – Tetsuya Yamamoto Mar 11 '19 at 06:36
  • @madreflection do you have any link related to this? – HNA Mar 11 '19 at 06:46
  • No, it's an assumption based on how `SqlConnection` works. – madreflection Mar 11 '19 at 06:46
  • 2
    I found a reference about how to use `dbms_output.get_line` in ODP.NET, you can check it [here](https://oradim.blogspot.com/2007/05/odpnet-tip-retrieving-dbmsoutput-text.html). You need to provide `OracleParameter` with output direction to get output text from PL/SQL command. – Tetsuya Yamamoto Mar 11 '19 at 06:47
  • 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(); con.InfoMessage += (s, e) => outputLines.Add(e.Message);` and then use `ExecuteNonQuery` to run your original script. – madreflection Mar 11 '19 at 07:09
  • The documentation for [InfoMessage](https://docs.oracle.com/cd/E85694_01/ODPNT/ConnectionInfoMessage.htm) 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`. – madreflection Mar 11 '19 at 07:11
  • @madreflection i'm using OracleConnection instead of SqlConnection can it still be done? – HNA Mar 11 '19 at 07:29
  • 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. – madreflection Mar 11 '19 at 07:34
  • @madreflection i've tried it inside the using block. it does not show the output. – HNA Mar 11 '19 at 07:56
  • 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. – madreflection Mar 11 '19 at 08:10
  • @madreflection yes, after `ExecuteNonQuery()`, the outputlines does not contains anything. – HNA Mar 11 '19 at 08:20

0 Answers0