4

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 ?

5

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.