Hello All,
This is my first post. I want to query multiple oracle databases inside powershell script
can you please help me to do it. I am successful in querying one database.
============================================================
This is what I use currently for single database query
#Function to Open connection to oracle DB
function Connect-Oracle{
[CmdletBinding()]
param(
[string] $connectionString = $(throw "connectionString is required")
)
$conn= New-Object Oracle.DataAccess.Client.OracleConnection($connectionString)
$conn.Open()
Write-Output $conn
}
#Create connection string with database settings
function Get-ConnectionString{
[CmdletBinding()]
param(
[string]$user = "fdcdb",
[string]$pass = "bistel",
[string]$hostName = "apk0bistel-scan",
[string]$port = "1521",
[string]$sid = "apkbstl.tqs.com"
)
$dataSource = ("(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})))" -f $hostName, $port, $sid)
Write-Output ("Data Source={0};User Id={1};Password={2}" -f $dataSource, $user, $pass)
}
# Starts the connection
$conn = Connect-Oracle (Get-ConnectionString)
# Get connection state and Server version
"Connection state is {0}, Server Version is {1}" -f $conn.State, $conn.ServerVersion
function Get-DataTable{
[CmdletBinding()]
Param(
[Parameter(Mandatory=$true)]
[Oracle.DataAccess.Client.OracleConnection]$conn,
[Parameter(Mandatory=$true)]
[string]$sql
)
$cmd = New-Object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
$da = New-Object Oracle.DataAccess.Client.OracleDataAdapter($cmd)
$dt = New-Object System.Data.DataTable
[void]$da.Fill($dt)
return ,$dt
}
