PowerShell and Oracle – a good team

I use PowerShell almost daily to access data in Oracle databases. Why, especially in this combination, I want to present by means of an example. But first there will be a few words about the used technology. 

Why PowerShell? 

Quite simply: Because it's there. Completely without the installation of additional components, PowerShell is present on every Windows system. This is especially important for my customers; I cannot always wish the installation of additional software. 

The used technology 

I use the library "Oracle.ManagedDataAccess.dll" provided by Oracle, which should be already installed on many systems as part of the Oracle Client. It is located in the path "odp.net\managed\common" below the Oracle Home. If not, this library can also be downloaded as part of the NuGet package "Oracle.ManagedDataAccess". An installation is not necessary. The package downloaded from the website only needs to be unpacked. Change the file extension to ".zip", then the file can be unpacked directly with Windows. The library can be found in the path "lib\net462".

Only one line of code is needed to integrate with PowerShell:

Add-Type -Path '<PASTE PATH HERE>\Oracle.ManagedDataAccess.dll'

This means that all the classes we need to access the Oracle database are now available within this PowerShell session. However, if you are using the current version from the NuGet package, you will see an error message. This can be ignored, as the necessary parts of the library were successfully loaded. If you want to suppress the message, use this line instead:

try { Add-Type -Path '<PASTE PATH HERE>\OracleManagedDataAccess.dll'} catch { } 

To make the code clear for daily work, I recommend the use of two own commands as wrappers, around the actual calls of the .NET classes: "Connect-OraInstance" and "Invoke-OraQuery". You can start with my implementation first and then make your own extensions as needed. Just download the two files "Connect-OraInstance.ps1" and "Invoke-OraQuery.ps1" from my repository on GitHub. There you can also find more information about this topic and more examples. 

The use case 

My customer has an application running in different test environments whose Oracle databases "don't see" each other, so I can't set up a database link between them. Nevertheless, the customer wants to show any differences in the configuration table used by the application between the environments. Fortunately, I have a central computer from which I can reach all databases.

The advantage of PowerShell over, for example, sql*plus here is the format of the received data. With PowerShell I get structured data, so in a structure of rows and columns with the correct data types. Numbers are still numbers, also date values are date values. With many other tools, including sql*plus, I only get text, which I then have to format again. 

A few sample data 

So that you can try the whole thing out directly with yourself, here is the structure of the table and a few sample data: 

CREATE TABLE config (key VARCHAR2(50), value VARCHAR2(50), CONSTRAINT config_pk PRIMARY KEY (key)); 
INSERT INTO config VALUES ('key1', 'Sample'); 
INSERT INTO config VALUES ('key2', '0815'); 
INSERT INTO config VALUES ('key3', 'true'); 
INSERT INTO config VALUES ('key4', 'false'); 
COMMIT;  

To make the environments different and give my script something to show, make one more change on one of the environments:

UPDATE config SET value = 'true' WHERE key = 'key4';

COMMIT; 

Establish the connections 

Here come the first lines of PowerShell code, which first load the library, create the two wrapper commands and then create the two connections. To do this, you need to modify the instances in the code accordingly, of course you can use TNS aliases. The users and their passwords are queried interactively, after all, there should be no password in the code. 

Add-Type -Path '< Insert path here>\Oracle.ManagedDataAccess.dll' 
. < Insert path here>\Connect-OraInstance.ps1 
. < Insert path here>\Invoke-OraQuery.ps1 
$instance1 = '< Configure the connection to instance 1 here>' 
$instance2 = '< Configure the connection to instance 2 here>' 
$credential1 = Get-Credential -Message ' Please enter user and password for instance 1' 
$credential2 = Get-Credential -Message Please enter user and password for instance 2' 
$connection1 = Connect-OraInstance -Instance $instance1 -Credential $credential1 
$connection2 = Connect-OraInstance -Instance $instance2 -Credential $credential2  

Query and compare the data 

First, the data is queried via both connections and stored in variables. By storing the data in variables, I can further access the data in this PowerShell session and process it in different ways or save it in different formats.

In a first processing step, I determine all keys, because a certain key could be used only on one environment. In the variable $keys I have a list of keys without duplicates, which I can now run through in a foreach loop. In it, I first determine the values for the current key for the two environments considered here. The syntax with "Where" reminds of SQL, but the database is not queried here. The access to a certain column of my result is done here with the syntax ".".

The data in $data1 and $data2 already exist as an array of PowerShell objects, and the result of my processing should again be an array of PowerShell objects. Because this is the best way to work within PowerShell. Therefore, I create a new object for each key with the two values from the two environments. As an additional property (like another column in the database world) I add the information if the values differ. For two environments this is not necessary, but if you query five or more environments in the same way, then it helps already.

Finally, the list of comparison objects is output to the screen in the form of a table. 

$query = "SELECT * FROM config" 
$data1 = Invoke-OraQuery -Connection $connection1 -Query $query 
$data2 = Invoke-OraQuery -Connection $connection2 -Query $query 
$keys = $data1.key + $data2.key | Select-Object -Unique 
$comparison = foreach ($key in $keys) { 
    $value1 = ($data1 | Where-Object KEY -eq $key).VALUE 
    $value2 = ($data2 | Where-Object KEY -eq $key).VALUE 
    [PSCustomObject]@{ 
        Key    = $key 
        Value1 = $value1 
        Value2 = $value2 
        Diff   = $value1 -ne $value2 
    } 
} 
$comparison | Format-Table  

An outlook 

The output can of course be further restricted, for example, to the detected differences. Sorting is also possible, in order to have a good overview with larger amounts of data.

Furthermore, this data could also be stored, for example also in the popular JSON format. I also like to use the PowerShell module "ImportExcel" by Doug Finke to create appealing Excel tables with very few lines of code. You can find more code examples here.

If you can now think of a few tables in your area that you have always wanted to compare clearly, please contact us. We are glad to help.