CODEWITHSUNDEEP
oracle-databasepowershell
GettingStarted
GettingStarted

Reputation: 7605

why is no data being returned in my PowerShell script

add-type -AssemblyName System.Data.OracleClient
$username = "SYSTEM"
$password = "password"
$data_source = "production"
$connection_string = "User Id=$username;Password=$password;Data Source=$data_source"

try{
$statement = "SELECT SYSDATE FROM DUAL"
$con = New-Object System.Data.OracleClient.OracleConnection($connection_string)

$con.Open()

$cmd = $con.CreateCommand()
$cmd.CommandText = $statement

$result = $cmd.ExecuteReader()
# Do something with the results...
Write-Host $result + "data"
If($result.HasRows) {
    try {
    while ($result.Read())
        {
            "[0] : " + $result.GetValue(0)
        }
    }
    catch
    {
        #log error
    }
    finally
    {
        $con.Close() 
    }
}

} catch {
    Write-Error (“Database Exception: {0}`n{1}” -f `
        $con.ConnectionString, $_.Exception.ToString())
} finally{
    if ($con.State -eq ‘Open’) { $con.close() }
}

I am executing SELECT SYSDATE FROM DUAL

I am expecting 21-MAY-19

However no data is returned. (no error is presented either)

Upvotes: 0

Views: 382

Answers (1)

Moerwald
Moerwald

Reputation: 11274

As mentioned in the above comments, you've to send the content of $result to PowerShells output stream. The output stream is used to realize the pipeline feature of Powershell. If you wrap your code in e.g. "myCode.ps1" and invoke it via:

.\myCode.ps1

The content of $result is pushed in the output stream (pipeline). Since no other cmdlet is attached to the call of myCode.ps1 the Powershell host (= your command line) will receive the content. The default behavior of the host is to dump the content.

So add the following to your code:

$result = $cmd.ExecuteReader()
# Return $result to the pipeline
$result

Read more about pipelines here and more about streams here.

UPDATE1: This link describes more or less the code sample of the question. Maybe the Orcale .NET data provider is missing. Add it via:

 Add-Type -Path "PathToDll\Oracle.ManagedDataAccess.dll"

Hope that helps.

Upvotes: 1

Related Questions