PowerShell连接查询Oracle数据库
$USER = 'USER'
$PASSWD = 'PASSWD'
$SERVER = 'SERVER'
$DB = 'DB'
# create connection object
Add-Type -AssemblyName 'System.Data.OracleClient'
$CONNECTION_STR = "User Id=$USER;Password=$PASSWD;Data Source=$SERVER/$DB"
$connection = New-Object System.Data.OracleClient.OracleConnection($CONNECTION_STR)
# query
Function Oracle-Query {
param ([String] $SQL, [HashTable] $parameters, [Bool] $scalar)
PrintEnv
$connection.Open()
$ora_cmd = $connection.CreateCommand()
$ora_cmd.CommandText = $SQL
ForEach($key in $parameters.Keys) {
$param = New-Object System.Data.OracleClient.OracleParameter($key, '')
$param.Value = $parameters[$key]
$ora_cmd.Parameters.Add($param) | Out-Null
}
If($scalar) {
$val = $ora_cmd.ExecuteScalar()
$connection.Close()
Return $val
} Else {
$reader = $ora_cmd.ExecuteReader()
$columns = @()
For($i = 0; $i -lt $reader.FieldCount; $i++) {
$columns += $reader.GetName($i)
}
$data = @()
While($reader.Read()) {
$row = New-Object -TypeName psobject
$columns | % { $index = 0 } {
If($reader.IsDBNull($index)) {
$row | Add-Member $_ 'NULL'
} Else {
$row | Add-Member $_ $reader.GetValue($index)
}
$index += 1
}
$data += $row
}
$connection.Close()
Return $data
}
}
更多推荐
PowerShell连接查询Oracle数据库
发布评论