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

ado.netoracle

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 ?

Best Answer

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

Related Question