Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Windows PowerShell for Oracle Tips

Oracle tips by Burleson
July 7, 2015

Question:  I want to change the PATH variable for all users on my Windows server, and I don't want to have to go through the registry one-at-a-time.  How can I use PowerShell to chage all PATH statements on my whole Windows environment?

Answer:  To change all with a PowerShell you start by opening a PowerShell window.  Press the Start button and write "cmd" in the command window.  You should now see a DOS prompt, where you enter the PowerShell command. 

c:\users> powershell

PS c:\users>

As an alternative, (if you have no right to edit the machine-wide environment), you can run Powershekll as admin:

runas /user:yourdomain\youruserplusadmin powershell

Now that you are connected to the Windows PowerShell, then you can access the environment assembly

[environment]::setEnvironmentVariable("PATH","c:\oracle\product\11.2.0\client_1\bin;C:\WINDOWS\;C:\WINDOWS\system32;C:\Program Files\Putty;C:\Program Files\Perforce;C:\Program Files\TortoiseSVN\bin;C:\WINDOWS\system32\WindowsPowerShell\v1.0","MACHINE")

MACHINE is a for a Machine-wide setting and USER is for a USER-wide setting.

Using PowerShell on Oracle

One of the greatest shortcomings of running Windows with Oracle is the serious limitations of the DOS command line scripting.  There are 3rd party Windows scripting tools such as

  •  UNIXDos, a toolkit from Professional Software Solutions that provides all of the UNIX-like functions. 

  • There is also the MKS toolkit for running shell scripts in Windows.

  • Microsoft released Windows Services For UNIX (SFU) to more closely emulate a variety of UNIX shells and UNIX utilities to ease the migration from a UNIX to a Windows environment.  While SFU is certainly a more comprehensive solution than the native command prompt, it is a more complicated and does not provide total compatibility for porting UNIX scripts to Windows.

  • Microsoft has introduced Windows PowerShell for Oracle, a new command line interface, developed by Microsoft, the same people who brought you Windows Vista.  Windows PowerShell  is based on object-oriented programming and the Microsoft .NET framework.

Here is an example of Windows PowerShell script making a connection Oracle to execute SQL:

= "Data Source=XE;User Id=oracle;Password=mypass;
   Integrated Security=no"
$connection = New-Object
$command = new-Object
   System.Data.OracleClient.OracleCommand($queryString, $connection)
$employeesNames = $command.ExecuteScalar()
echo "Number of employees: "$employeesNames

Result of the script:

C:\scripts> .\connec_oracle.ps1

GAC   Version        Location
---   -------        --------
True  v2.0.50727     C:\WINDOWS\assembly\GAC_32\System.Data.

Number of employees: 107

Let's see the detail of the script:

   = "Data Source=XE;User Id=oracle;Password=mypass;
   Integrated Security=no"

We create a variable containing the connection string to the database:

  • Data Source: the name of the database.
  • User Id: username with whom you want to connect.
  • Password: password for the user.
  • Integrated Security = no.

Guy Harrison has these notes on connecting to Oracle with Windows PowerShell:

You need to install the Oracle Data Provider for .NET.  Once that is done, you load it in PowerShell using this command:
# Load the ODP assembly

Of course, "c:\oracle\10g" represents my Oracle home.  It's non-trivial to get this from the registry so make sure that this path is valid for you.

Now we can use the ODP.NET variants of standard ADO.NET  calls to connect to Oracle and extract results.  I setup my connection to Oracle as follows:

#connect to Oracle
$constr = "User Id=system;Password=manager;Data Source=gh10gb"
$conn= New-Object Oracle.DataAccess.Client.OracleConnection($constr)

My TNS alias was "gh10gb".  Now I have a connection handle $conn, I can create a data reader object for a SQL query:

# Create a datareader for a SQL statement
$sql="select * from all_users"
$command = New-Object Oracle.DataAccess.Client.OracleCommand( $sql,$conn)

From this point on, everything is ADO.NET standard.  You can look at the structure of the result set as so:

# Write out the result set structure
for ($i=0;$i -lt $reader.FieldCount;$i++) {
    Write-Host  $reader.GetName($i) $reader.GetDataTypeName($i)

And write out the results like this:

# Write out the results
while ($ {
    Write-Host "$userid $username $createDate "



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.