PowerShell: Connecting and Querying Oracle DataBase contents from a Windows 7 x64 system, Display results row by row

Important points to consider from the environment of this automation:

• Your base Windows is a x64 bit OS
• Your remote Oracle DB is of a x64 bit version on a x64 system
• You install Oracle Client which is available ONLY in x86 version
• Thus, your DSN to Oracle client is as well a x86 version, can be viewed in C:WindowsSysWOW64odbcad32.exe
• Thus, you can connect to your Oracle from a x86 applications only by using the Oracle Client driver
• Thus, you need to run your PowerShell script from x86 version only: "C:Windowssyswow64Windowspowershellv1.0powershell.exe"

Configuring DSN to Oracle DB on Windows 7 x86: refer to “Setting up System DSN to connect to a Oracle Database from a Windows 7 x64 bit system” below

Step-By-Step Code to connect to Oracle DB:

• Building a Connection string:                                                                        $connectionString = "Data Source=ORALAB01.TESTLAB.LAN;User Id=reportsadmin;Password=Rep0rTs;Integrated Security=no"
• Keeping your Oracle query ready:                                                                 $queryString =     "SELECT * FROM USERS Order by firstname desc"
• Load Oracle Driver:                                    [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
• Connecting to Oracle DB:

[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()

• Executing the Query/Command:                                                                              $users = $command.ExecuteReader()
• Get the number of columns returned, this is needed since we using the select * which will get all of the columns/fields in the Database                                             $Counter = $users.FieldCount 
• Fetch the results/rows one after the other and then print all the values:

while ($users.Read()) {
    for ($i = 0; $i -lt $Counter; $i++) {
         @{ $users.GetName($i) = $users.GetValue($i); }
    }
}

• Close the connection the DB: $connection.Close()                  

Complete Code:

$connectionString = "Data Source=ORALAB01.TESTLAB.LAN;User Id=reportsadmin;Password=Rep0rTs;Integrated Security=no"
$queryString =     "SELECT * FROM USERS Order by firstname desc"

[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()
$users = $command.ExecuteReader()
$Counter = $users.FieldCount

while ($users.Read()) {
     for ($i = 0; $i -lt $Counter; $i++) {
          @{ $users.GetName($i) = $users.GetValue($i); }
    }
}

$connection.Close()

 

PS C:> powershell "TempQuery-Oracle-DB-FromPS.ps1"

Name                           Value
—-                           —–
UserID                         4545
Email                          [email protected]
Firstname                      TestUser
UserID                         45454
Email                          [email protected]
Firstname                      TestUser2
UserID                         545445
Email                          [email protected]
Firstname                      TestUser9

PS C:>

 

 

 

 

Setting up System DSN to connect to a Oracle Database
from a Windows 7 x64 bit system:

• For creating a DSN to establish connection a DataBase server, you need to have respective DataBase (Oracle/SQL) drivers
• In order to get the respective drivers, you need to install the respective database connection tools/software.
• For installing Oracle Drivers, you need to install Oracle Client software of version appropriate to your Oracle Database version
• Now create a System DSN as shown below. Ensure you enter correct Service Name that matches with your Database service name.
• Then ensure you have the respective DNS entry created in tnsnames.ra file, which in my case is located at "C:Oracleora81networkADMINtnsnames.ora"
• The Important things to note are:
• You are attempting to connect from a x64 Windows system to your Oracle system installed on a x64 system
• Your Oracle Client software is a x86 (32bit)  software, and thus your Oracle Driver as well
• You’ll need to create a x86 System DSN by using x86 version of ODBC Admin too located at: C:WindowsSysWOW64odbcad32.exe
• Since the DSN is if x86, you can connect to the Database using it only from a x86 software
• d