Install and Configure Oracle SQL Developer

In the previous post I described how to install Oracle and configure HR schema. If you haven't installed and configured Oracle database yet, please go to the previous post and do that.

In this post we'll download, run and configure Oracle SQL Developer.

Go to www.oracle.com and find the download page for SQL Developer. At the time of this writing the url for this page is http://www.oracle.com/technology/products/database/sql_developer/index.html

Press FREE DOWNLOAD on this page; accept license agreement and download the product. Select the correct download depending on whether you have JDK (Java Development Toolkit) installed on your machine or not. It's easier to select the first option so that it also downloads the JDK and uses it (but it's up to you :)) )

You can see on this picture that it also has the installation instructions: To install and run:
- Download the file above
- Extract sqldeveloper.zip into any folder, using folder names
- Within that folder, open the sqldeveloper folder
- Double-click sqldeveloper.exe

Now we are ready to connect our SQL developer to schema HR on our Oracle database. Right-click on Connections and select "New Connection".

On this page you need to enter the following information:

• Connection name - you can call it whatever you want
• Username - HR
• Password - HR (you remember we set it during Oracle installation? See previous post)
• Check the "Save Password" checkbox so that you don't have to do it again later on
• Leave all other fields as they are: Hostname: localhost; Port: 1521; SID: xe

Press Test button - you should see the Status changing to Success. Now press Connect button and now you are ready for the excitement of practicing SQL on your own! Congratulations!!!

Note: at our class we entered little bit different information on the "New Connection" screen:

• Instead of localhost we entered stu100 - don't forget this is the name of the computer where Oracle is installed. locahost is the name of your local computer
• Instead of xe we entered orcl - this is because in the class we have Oracle Enterprise edition installed and usually it creates database with SID "orcl", however Express edition creates database with SID "xe".

Good luck with learning SQL!!!

Install and Configure Oracle XE. Configure schema HR.

Download Oracle XE.

Go to http://www.oracle.com find "Downloads" link and find the place from which you can download Oracle XE database, you'll need to select the Operating System for that. At the time of writing of this post the URL for downloading is the following : http://www.oracle.com/technology/software/products/database/xe/htdocs/102xewinsoft.html

From this page download Oracle Database 10g Express Edition. Before you start downloading you will be asked to enter your name and password for Oracle site. If you don't have them - go ahead and register, the registration is free and according to my experience they don't sp@m you.

After you download OracleXE.exe, start it.

Install and Configure Oracle XE. Configure schema HR.

• Run OracleXE.exe.
• After a while (and a couple of minutes of some unpacking operations) you are presented with a welcome screen. Click "Next" on it.
• Accept license terms on the next screen.
• On the next screen leave C:\oraclexe as the destination location and click Next
• Enter password twice on the next screen. I usually use the letter "a" - for simplicity you can use this password too. This password is for schemas SYS and SYSTEM. These schemas are used for administrative purposes. You should remember this password in order to access the database as an administrator.
• Press Install on the next screen
• Oracle will start installation. It will take several minutes.
• Finally you see the message that Setup has finished installing Oracle Database... On this screen check the checkbox "Launch the Database homepage" and press Finish (or you can later go to "Programs->Oracle Database...->Go to Oracle Database Home page"
• Now you are asked about username and password. These are username and password of admin account (the one that we entered during installation process). Enter "sys" for username and "a" for password (or the password that you entered yourself)
• One the next page click on the triangle on the "Administration" icon and select "Database Users->Manage Users":

• On the manage page you should see user HR - when you move mouse over it it says "Expired and Locked".
  
• Click on this user and on the next page enter the new password for this user "HR" and change the account status from "Locked" to "Unlocked". Leave all other default values and press "Alter User" button:

Congratulations!!! Your Oracle database and schema HR are ready!!!

Next post will be about downloading, running and connecting SQL Developer with this HR schema.


This is an example of PL/SQL code:

declare
  wealth VARCHAR2(20);
begin
  for emp in (select * from employees) loop
    if emp.salary < 5000 then
      wealth := 'poor';
    elsif emp.salary >=5000 and emp.salary < 15000 then
      wealth := 'middle class';
    else
      wealth := 'rich';
    end if;
    dbms_output.
      put_line(emp.first_name||' '||emp.last_name
        ||'-'||emp.salary||'-'||wealth);
  end loop;
end;