ThatJeffSmith

I Just Wanna Connect to Oracle!

I have had quite a few SQL Server folks tell me their first experience with Oracle (database) was a very bad one. It’s easy to understand why. Microsoft makes it very easy to connect to SQL Server from a Windows OS. If you have installed SQL Server on your machine, you also get a very nice and rich set of GUI tools.

For the past decade, if you asked how to get started, or even connected, to Oracle you were probably told to ‘go download the client’ and ‘use SQL*Plus.’ If you managed to figure this out, you were left with a command-line tool that would have seemed barbaric compared to SQL Server Management Studio (SSMS).

It’s not fair today to compare SSMS to SQL*Plus. The fair comparison would be to compare SSMS to SQL Developer.

If you were unable to get anywhere a few years ago, I invite you to come back and try again. Read on if you want some help in getting started! You should be able to accomplish all of this under an hour, and that includes the download time for the binaries.

Step 1: Go Download Stuff from Oracle

Here is the magical link where you can find EVERYTHING you need to work with Oracle databases, OTN Database Downloads

Step 0: Figure Out What You Need

If you just want a GUI to run queries, debug PL/SQL programs, manage your database, model your data, or migrate from SQL Server, Teradata, DB2, Sybase, Access, or MySQL to Oracle, then download SQL Developer. It comes with everything you need to connect via JDBC. You’ll only have a couple of choices from this point on. Your OS and 32 or 64 bit – that should be obvious to you. The ‘hard’ part will be to decide between getting the download with the Java Development Kit (JDK) or not. The JDK includes the Java Runtime Environment (JRE) which makes Java programs ‘Go’ on your machine. If you’re not sure, just grab the larger download, you’ll be OK.

Watch how you can be up and running with SQL Developer in less than 5 minutes!

If you need an Oracle client to get your 3rd party program to connect OR if you want to actually use SQL*Plus or SQL*Loader, then click here: You’ll notice I am directing you to the 11gR2 downloads. An 11gR2 client will work with 9iR2 and 10gR2 databases. General rule of thumb is to have at least the same version client as the database server or higher. Notice I said ‘general’, there are exceptions. The good news is that you can have multiple versions of the Oracle client installed on your machine!

Once you navigate to the full download list for 11g, you’ll see the Client option. If you are going to be using a 32 bit application, you’ll want the 32 bit client. Same for 64 bit apps. 32 bit clients work just fine when connecting to 64 bit databases.

Clear as mud? Excellent, let’s continue!

If you want just the basic connectivity components so your 3rd part application can connect, then click here:.

The Instant Client is a compact version of the Oracle client, and just gives you the bare bones of what you need to work with an Oracle database. There is no installer, and there are few manual steps you will need to take. Nicolas de Fontenay has a good blog post on this (he can thank Google for high page ranking for ‘setting up intant client registry path’ keywords!).

Step 2: Connect!

Actually, before you do this, you’ll want to ensure that:

  • You have access to a database (username and password please!)
  • You know where the database is OR you have a TNSNAMES entry
  • You understand relational databases and SQL. If you do not pass this requirement, then allow for a lot of growing pains and frustration. But GOOD NEWS, that’s what the Internet is for! Just ID yourself as a NOOB and go register for an account on the Oracle Technology Network (OTN). Then you can participate on their message boards. Be sure to search before ye ask.

Step 1.5: Add Your TNSNAMES Entry

If your application doesn’t support EZ CONNECT (bypasses TNSNAMES), make sure you installed the full Oracle Client and not the Instant Client. If you downloaded the full client, then you also got access to this:

It Has a Wizard!

It’s a wizard. Just answer the questions and be sure to test your connection once you’ve defined the entry. If you have gotten this far, then you should be good to go.