Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

Querying Oracle from Powershell Part 1

In this two part blog post we will demonstrate how to query an Oracle database from Powershell. Before we can run queries against Oracle we need to install the Oracle client on our Windows machine. Unlike SQL Server, the drivers for connecting to Oracle are not included with the operating systems. The drivers are however freely available from Oracle. You can find the client software on the Oracle Database Software Downloads Page.

Downloading the Oracle Client

You’ll notice several versions of Oracle software on the download page. The software you choose will varying depending on your operating system. Generally when with dealing Oracle client software it is safe to choose the latest client version even if the Oracle database you will be connecting to is a lower version.

At the time of this blog post the following versions were the latest available:

  • 11.1.0.7.0 Windows 2008 and Windows 2008 R2
  • 11.1.0.6.0 Windows 2003

However, check the download page and choose a later version if listed. I’ve installed both the Windows 2008 and 2003 x64 versions, but for this blog series I’m using the Windows 2003 x64 version. To complete the download

  • Select See All
  • Select Oracle Database 11g Release 1 Client (11.1.0.6.0) for Microsoft Windows (x64). Note: Be sure you select the Client download and not the full Oracle database software!

Note: When you attempt to download Oracle software you will be prompted to login to the Oracle Technology Network (OTN). If you don’t have an account you’ll need to create one—It’s free.

We’re now ready to install and configure the Oracle client software.

Installing the Oracle Client

Many of the components included with the Oracle client are not needed. The following steps are used to perform a minimal Oracle client installation.

Run setup.exe

oracleClient1

Click next on the Install Welcome Screen.

oracleClient2

Select Custom installation type and click next.

oracleClient3

The Oracle base directory should be off of a root drive of your choosing. I’m using C:\Oracle. Change the path and ensure the name field is auto populated correctly and then click next.

oracleClient4

Ensure all the requirement checks succeed and click next (Note: you may receive warnings on Windows 2008 R2 when using the Windows 2008 installation software. The install will still succeed even with these warnings).

oracleClient5

Select SQL Plus and scroll down to select more components.

oracleClient6

Select Oracle Windows Interfaces and ensure the first three components are NOT selected. Ensure all other Windows Interface ARE checked and scroll down to select additional components.

oracleClient7

Select the Oracle Net component and click next.

oracleClient8

Select Install.

oracleClient9

Once the installation is complete the configuration utility will be launched by the installer.

Configuring the Oracle Client

Select next from the Oracle Net Configuration Assistant Welcome screen.

oracleClient10

Select Next.

oracleClient11

Enter the Oracle database service name. Note: I’m using Oracle Express on Ubuntu Linux. The service name is XE, your service name may differ.

oracleClient12

 

Select Next.

oracleClient13

Enter the Oracle database server host name or IP address.

oracleClient14

Select Next to test connectivity.

oracleClient15

The test will fail, you’ll need to change the login and password by selecting Change Login

oracleClient16

The test should succeed and if not use the error message to troubleshoot.

oracleClient17

Enter an alias name and select next.

oracleClient18

Select Next.

oracleClient19 

Select Next.

oracleClient20

Select Next.

oracleClient21

Select Finish.

oracleClient22

Select Exit.

oracleClient23

Select Yes.

oracleClient24

Congratulations you’ve installed the Oracle client! My thanks to an Oracle colleague who wishes to remain anonymous. He was a big help with the installation and putting together this guide. In part two of this blog series we’ll look at querying an Oracle database from Powershell.

Comments

Posted by hrvoje.piasevoli on 4 March 2010

Thank you for putting this howto - i recently went through this procedure for the first time and i had no time to think through the installation options.

Will use your guide to install on my dev server today.

Best regards,

Hrvoje

Posted by mikeg 31960 on 4 March 2010

Very good, useful and timely stuff. Thanks!

Posted by Brnbngls on 4 March 2010

Where was this about a month ago?  Had to figure it out.  I actually put together a similar guide and posted in the forums.  But we're querying using OPENQUERY.  I'll be interested to read part 2 and see how Powershell works.

Posted by mikeg 31960 on 4 March 2010

I just tried and the client driver doesn't work on Windows Server 2008 R2. The Warnings Chad refers to are actually errors which prevent you from proceeding with the install.

Posted by cmille19 on 4 March 2010

@mikeg Did you install the 11.1.0.7.0 version? I installed that version on a Windows 2008 R2 x64. I did receive a couple of warning but unfortunately I didn't save what they were. I didn't receive any warnings installing version 11.1.0.6.0 on Windows 2003 SP2 x64. I'm not saying you should ignore all errors on the requirement check, just that if you choose the right version you may see warnings and still succeed.

Leave a Comment

Please register or log in to leave a comment.