I Just Wanna Connect to Oracle!

thatjeffsmith Database Stuff 10 Comments

Tell Others About This Story:

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.

Tell Others About This Story:

Comments 10

  1. Hi Jeff,

    After downloading SQL Developer and when we try to use it, at first time it asks for java.exe, am not sure which is the correct path to give, where to download the supporting software to run SQL Developer.

    But however, Trust me SQL Developer is an awesome free ware tool available.

    1. thatjeffsmith Post
      Author
  2. Why is it that SQL Developer asks for Java.exe in the first place? I don’t understand why it can’t detect the location of the dependencies.
    Is this a platform limitation of Java or should SQL Dev be looking in the Windows registry for this information?

    1. thatjeffsmith Post
      Author

      It probably could, although we have to worry about proper versions and bit levels of the JDK, and we have to make sure it’s a JDK and NOT a JRE, and then we have to make sure it works everywhere, and not JUST Windows. So there’s a reason, but probably not a great one.

      Let’s see what we can do 🙂

  3. JeffS Post
    Author
    1. JeffS Post
      Author

      Pretty sure I did that on purpose. Of course you know I’m too honest to claim any edumacation from NC. I learned my reading and writing in the great state of WBGV.

  4. Thank you google for high ranking with setting up instant client registry path!

    Toad is a fantastic tool to work with but Microsoft’s SQL Development Studio comes free…

    Excellent post summarizing it all. Thank you for the link too. Sadly I’m not working on Oracle anymore.
    I’m in internet business since the last 3 years and spend more time on MySQL these days.
    I miss Oracle’s Sequence the most.

    1. JeffS Post
      Author

      Oracle’s new GUI management and query tool also comes for free, and is much more user-friendly than SQL*Plus…but yes, Toad is fantastic 🙂

      Sorry to hear you’re no longer in Oracle. I bet you would miss the LIMIT clause if you moved away from MySQL back to Oracle though?

Leave a Reply

Your email address will not be published. Required fields are marked *