Aiming for elegance, one thought at a time

Installing Oracle Instant Client (and connecting to Oracle from Excel)

Posted: May 1st, 2010 | Author: | Filed under: IT | 2 Comments »

‘Instant’ probably overstates it somewhat, but the Oracle Instant Client does let you connect to an Oracle database in a reasonably snappy way. It’s pretty straight-forward too, but there are a few hoops to jump through. Here’s how I got it working.

Installing Instant Client

  1. Download the Instant Client. You’ll need the ‘basic’ or ‘basiclite’ packages, and you’ll probably want one of the add-ons, like the jdbc driver (for Java) or the odbc driver. I grabbed the odbc driver, because I’m going to connect via Excel. You’ll need to sign up to the Oracle website to access the downloads. I’ve been a member for a while, and it seems pretty harmless – no spam that I’ve noticed. Once you’ve downloaded the packages, unzip them to the same directory.
  2. The current packages ship without some necessary DLLs, as detail on the OTN Discussion Forum. The missing DLLs are MFC71.dll, msvcr71.dll and MFC71ENU.dll. I believe they’re part of the Visual Studio install, and I had them on my PC, but I needed to drag them into the install directory. If you don’t have them, you can google them (if you’re feeling lucky.) Update: looks like they’ve updated the packages, and you shouldn’t need to track down these dlls any longer.
  3. Place this directory where you want it and run odbc_install.exe. The install adds some registry settings to register to odbc driver, and it points to the driver in the directory you’re using.
  4. Create an environment variable called TNS_ADMIN. The value should be the path to the directory that contains tnsnames.ora, which lets the Oracle driver know what servers are available. Managing tnsnames.ora can be frustrating, especially for the uninitiated (that is, me), and in a subsequent post, I’ll detail how to connect without tnsnames.ora.
  5. You’ll also need to create an NLS_LANG environmental value. Oracle recommends you set this in the registry, but the instantclient doesn’t create the registry structures needed. You could create them, but it’s easier to create the environmental variable. Oracle provides a list of possible values.
  6. You can now connect to your Oracle DB using Microsoft Query.

Update: The promised post to connect using VBA is on it’s way! In the meantime, to connect with Microsoft Query, there’s a few things to be aware of.

Connecting with Microsoft Query

Firstly, your TNS_ADMIN environment variable must point to a valid file – or this won’t work. If you’re connecting to Oracle Express Edition, then you’re tnsnames.ora will look like this:

XE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = XE)
)
)

If you’re connecting to another Oracle database, you’ll need to find the appropriate tnsnames.ora. It might be under a path like C:/oracle/network/admin/. Without TNS_ADMIN pointing to a directory with a valid tnsnames.ora, you won’t be able to connect using this method.

There are two ways of setting up the connection. The first is directly through Microsoft Excel. The second is through ODBC Data Source Administrator. ODBC Data Source Administrator is probably the better way, but I’ll look at setting it up through Excel first.

New connection through excel

  1. In Excel, start Microsoft Query. In Office 2007, go to the Data ribbon and click on Get External Data -> From Other Sources -> Microsoft Query.
    The Choose Data Source dialog shown in Excel when you choose to import external data using Microsoft Query
  2. Leave <New Data Source> selected and click OK. The Create New Data Source dialogue will be displayed. Enter a name for the data source, and the driver drop-down becomes enabled. Select Oracle in instantclient_11_2 (or similar).
    The Create New Data Source window in Microsoft Query
  3. Click connect. The service name must much a valid service name in the tnsnames.ora. If you’re using the Oracle Express example above (and have installed the Oracle Express client with default settings) this will be XE. The username and password will be whatever you or the sysadmin set.
  4. Click OK. If you cannot connect at this point, but you can connect to the database by other means, it most likely means that your tnsnames.ora is wrong or that TNS_ADMIN is not pointing to the right directory (note that if you change the environmental variable, you’ll need to restart Excel for the change to take effect.)
  5. All being well, you will now be able to select a default table (if you choose to) and use Microsoft Query as you normally would.

Congratulations! You’re now connected to Oracle using Microsoft Query!

Creating the connection in ODBC Data Source Administrator

It’s often easier and more convenient to set up the new data source through the ODBC Data Source Administrator. This way, the new data source will be available whenever you want to use it, rather than needing to recreate it every time.

  1. Open the ODBC Data Source Administrator. This is in Control Panels. Under Windows 7 64bit you’ll need to choose the appropriate version: odbcad32.exe underĀ either system32 or SysWOW64, depending on whether you’re setting up a connection for 32bit or 64 bit applications.
  2. Click Add. The Create New Data Source window appears. Choose the Oracle in instantclient_11_2 driver and click OK.
  3. The Oracle ODBC Driver Configuration page will open. This page gives you far more options and is more intelligent than the equivalent if you create the connection in Excel. The TNS Service Names drop-down box will populate with the databases specified in tnsnames.ora: if no options appear, then either your tnsnames.ora file is invalid, or TNS_ADMIN is not specified correctly. Again, if you change TNS_ADMIN, you’ll need to restart ODBC Data Source Administrator for the change to take effect.
  4. Click ‘Test Connection’. You’ll be prompted to enter a password, and all being well, you’ll get this dialogue:
  5. Click OK in the Data Source Configuration dialogue, and open Microsoft Excel. The new Data Source will appear when you open Microsoft Query.
  6. Click OK and you can use the connection in Microsoft Query as usual.

Still to come…

So that’s two different ways to connect to Oracle in Excel using Microsoft Query. As soon as I have time, I’ll be posting a sample workbook and instructions on how to connect to Oracle using VBA instead of Microsoft Query, which is especially handy if you want to distribute the workbook.

Tags: , , , ,

These are some of my favourite things

Posted: April 2nd, 2010 | Author: | Filed under: IT | No Comments »

Over the past year, I’ve spent a lot of time extracting and manipulating data in Oracle databases. Powerful things, them. These are some of the small-ticket, but kinda cool, features that I’ve found useful – the type of thing that doesn’t make the sales brochures, but can save time when you need it.

wm_concat
wm_concat is an unsupported string aggregate function, so it’s not often mentioned. In a grouping query, wm_concat will concatenate up to 255 (I believe) string values, and return a comma separated list. I used wm_concat when I had a table of operations that could be linked to multiple errors, and I wanted a summary of the most common combinations. You can achieve the same thing with a user defined aggregate function, but it’s nice that it’s just built in (unless the DBAs have disabled it.)

xmlelement, xmlforest, xmlagg
Need to get xml out of your database? Sure you do. Yes, you could write something in whatever language, or better yet, use a case tool to autogen that code, but it’s pretty neat to get it straight from the DB. xmlelement, predicably, takes some parameters and makes an xml element. xmlforest returns a whole bunch of elements. xmlagg is an aggregation function to wrap a number of rows up together. You can combine these three functions (plus there are others) and build some very complex xml. The downside: you get a query that’s really not pretty. These function are part of the SQL/XML standard, which seems to have pretty much languished since 2003. Anyone using this in a production environment?

case statements in sql
Case statements within sql queries are ugly (they break with the sql paradigm – but maybe it’s the SQL that’s ugly, and the case statement just brings that home?) but they sure are useful. They can be easier to understand than decode() or some of the more creative hacks combining sign() and other functions in ways that were never intended. So I guess it’s not all bad.
Tags: , , , , ,