I thought l could give a short summary on how to quickly install the oracle sql developer in other to access an oracle database. This is something that might come up for people who are used to and work regularly on only Microsoft SQL Server which comes with the SQL Server Management Studio tool that makes it very convenient to access and write queries just after installation of the Microsoft sql server. There are other tools to access oracle database such as TOAD which l think its not free but SQL developer is a free tool provided by oracle and available at http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html?ssSourceSiteId=ocomdk.
However, you would need registration at the oracle site to get a copy and that’s not too much to ask, l guess.
After downloading the software, the installation is not straight forward if you don’t know how its done. Google searches might not help sometimes so I decided to put this up so that even a kid can do it lol.
First of all, this guide is based on using a TNS file which holds oracle server configurations and can contain configuration for multiple oracle servers. This file must be saved as tnsnames.ora. The file can be saved at any location on your computer but l recommend to create a folder on the root of your C drive called Sqldeveloper and save this file in it.
Going forward, I will assume you save the tnsnames.ora in this folder C:sqldeveloper.
Ok, I know what you are thinking about, how do l create the content of the tns file?
Its quite simple but you need the server information to create it. The content is like below
Alias =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server_ip) (PORT = port_number))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = database_name))
)
The default port number is 1521 and the Alias is the name you want to use to recognize the server when connecting to it in sqldeveloper. Instead of the ip address, if the server has a name set up to resolve it’s ip, you can simply use it.The port number could be different so you have to get that right. if you want to put comments in this file, then use # in front of the statement.
After creating the file and saving it as tnsnames.ora at the location mentioned above, l recommend you copy your downloaded sql developer and put it inside the same folder at C:sqldeveloper. This is just my way of organizing things.
Next, You are going to create an environment variable that the sql developer uses to locate the tnsnames.ora file that you have just created. That is why l said you can put this file any where because the sql developer uses a variable to locate it. The variable name the sql developer will look for is TNS_ADMIN. It is case sensitive and it will point to the folder in which the tnsnames.ora file resides, C:sqldeveloper in our case.
Ok, l think l know what question you are asking again, how do l create the environment variable?
I’m on windows 7 right now so let me quickly give guide to creating the environment variable on windows 7.
Click on the start button, right click computer and click on properties. When the properties window comes up, click on Advanced system settings on the left upper part of that window. This will pop up the system properties window. Click on the Advanced tab and click environment variables button at the bottom of that window. This will bring you to the environment variables window, with the user environment section at the top and the system variables section below it. You are going to create a user environment variable so click new button below the user variables section and it will pop up the user variables properties window. Give the variable name as TNS_ADMIN and Variable Value as C:sqldeveloper . Click OK buttons and exit out of the environment variable settings and close all that computer properties stuff.
Now what? Go to the C:sqldeveloper and open the folder containing the sql developer that you have downloaded. Look for the sqldeveloper.exe in the download folder and double click it.
During the first time of starting, it will ask you to associate files to open with sql developer and there are multiple options available. I usually do not associate sql developer to any file type but you can do as you wish so that any time you double click any file with that type, the sql developer will open it automatically. The sqldeveloper.exe is what you will use all the time to open the application so you can create a shortcut to your desktop for convenience.
Once sql developer starts, you will see the connections symbol on the left upper part where you define connections to the servers you have created in your TNS file.
So another question l’m thinking you are asking, how do l connect to the servers in the TNS file?
Here we go, right click the connections on the left upper corner of the application and click on new connection. The new connection window will pop up. Fill in a name you want to use for your connection, example 2smart4school. Then go down and click the drop down button next to the connection type and choose TNS from the drop down window. As soon as you select TNS as the connection type, you will see below network alias check marked with a drop down menu to the right to allow you to choose one of the aliases you put in the TNS file.
Click the drop down arrow next to the network alias and choose one of your aliases in the TNS file. Then Provide the username and the password at the top below the connection name and check mark the save password box below that. This password and username must be what the oracle server and database is set to accept as the accurate authentication properties.
Test the connection to see if it’s successful and then save it or click connect to go right into the server.
The next time you come back to sql server, your connection names will be available under the connections on the left pane and you don’t have to do all the above again, you simple have to double click a connection name to connect to that server.
There are other settings on the sql developer but this is how to install and quickly connect to an oracle database using the free sql developer tool provided by oracle.
Hope this helps someone.
