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

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 
 

Windows PowerShell for Oracle Tips

Oracle Tips by Burleson Consulting
July 7, 2009

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:

$connectionString
= "Data Source=XE;User Id=oracle;Password=mypass;
   Integrated Security=no"
[System.Reflection.Assembly]::
   LoadWithPartialName("System.Data.OracleClient")
$connection = New-Object
   System.Data.OracleClient.OracleConnection($connectionString)
$queryString = "SELECT COUNT(LAST_NAME) FROM EMPLOYEES"
$command = new-Object
   System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()
$employeesNames = $command.ExecuteScalar()
echo "Number of employees: "$employeesNames
$connection.Close()

Result of the script:

PS
C:\scripts> .\connec_oracle.ps1

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

Number of employees: 107

Let’s see the detail of the script:

$connectionString
   = "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
[Reflection.Assembly]::LoadFile("C:\oracle\10g\ODP.NET\bin\2.x\Oracle.DataAccess.dll")

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)
$conn.Open()


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)
$reader=$command.ExecuteReader()


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 ($reader.read()) {
    $username=$reader.GetString(0) 
    $userid=$reader.GetDecimal(1)
    $createDate=$reader.GetDateTime(2)
   
    Write-Host "$userid $username $createDate "
}


 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
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 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.