Oracle database script with Windows PowerShell

by , under Oracle

If Oracle database is running on Windows environment, PowerShell would be a very good tool for automated scripting for the database such as monitoring or backup script. So the following script would be an example:

 

Add-Type -Assembly System.Data.OracleClient
$connectionString = "Data Source=HOSTNAME:1521/SID; User ID=USERNAME; Password=PASSWORD"
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
$connection.Open()

#$queryString = "SELECT db_link FROM dba_db_links"
$queryString = "SELECT instance_name FROM v`$instance"
$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
$reader = $command.ExecuteReader();
$name = $(
 while ($reader.Read())
 {
 $reader.GetValue(0) 
 }
)

write-host "Instance Name :" $name | ft
$connection.Close()

Leave a Reply