Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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

share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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