How can I catch the output of DBMS_OUTPUT.PUT_LINE when executing a block using [Oracle.DataAccess.Client]?

Question

In my oracle Database if have the following table:

Create table Test_call_count (
    count number(10)
);
insert into Test_call_count values (0);

Now I can run the following PowerShell script

if ($ora_loaded -eq $null)
{
    $ora_loaded = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess") 
}

$ConnectionString = "Data Source=YOUR_TNS;User ID=YOUR_ID;Password=YOUR_Password" 


function Execute-NonQuery
{
    param (
        $sql
    )

        $conn = new-object Oracle.DataAccess.Client.OracleConnection 
        $conn.ConnectionString = $ConnectionString 
        $cmd = new-object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
        $conn.open()
        $cmd.ExecuteNonQuery()
        $conn.close()
}

Execute-NonQuery "begin update  Test_call_count set count = count + 1;DBMS_OUTPUT.PUT_LINE('Test...'); end;"

Looking at the the database I can verify, that count increments with each call.

But is there a way to extend this, so that I can get the data send to DBMS_OUTPUT ?

Answer 1

DBMS_OUTPUT.GET_LINES is the way to get them back programmatically. Call it the same way as any other procedure. Or for a single line (which is probably easier to code) DBMS_OUTPUT.GET_LINE

Answer 2

Here is the complete PowerShell code. It is inspired by this C# example. It was a bit difficult to find, because an unsolved question was spamlike reposted a hundred times.

if ($ora_loaded -eq $null)
{
    $ora_loaded = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess") 
}

$ConnectionString = "Data Source=YOUR_TNS;User ID=YOUR_ID;Password=YOUR_Password" 


function Execute-NonQuery_WITH_DBMS_OUTUT
{
    param (
        $sql
    )

        $conn = new-object Oracle.DataAccess.Client.OracleConnection 
        $conn.ConnectionString = $ConnectionString 
        $conn.open()

        $cmd = new-object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
        $p_1 = new-object Oracle.DataAccess.Client.OracleParameter("1", [Oracle.DataAccess.Client.OracleDbType]::Varchar2, 32000, "", [System.Data.ParameterDirection]::Output)
        $p_2 = new-object Oracle.DataAccess.Client.OracleParameter("2", [Oracle.DataAccess.Client.OracleDbType]::Decimal, [System.Data.ParameterDirection]::Output);
        $cmd.Parameters.Add($p_1) | out-NULL
        $cmd.Parameters.Add($p_2) | out-NULL


        $cmd.ExecuteNonQuery()
        $p_1.Value.ToString()
        $p_2.Value.ToString()
        $conn.close()
}

$anonymous_block = "begin " +
"  dbms_output.enable; " +
"  begin " +
"  update  Test_call_count set count = count + 1;" +
"  DBMS_OUTPUT.PUT_LINE('DBMS_OUTPUT Test...');" +
"  dbms_output.get_line(:1, :2); " +
"  end;" +
" end;"
Execute-NonQuery_WITH_DBMS_OUTUT $anonymous_block