Joe Lennon

In this tutorial, I will be looking at the basics of Oracle databases, namely, the creation of database tables and inserting of data rows. I will be showing you two ways of interacting with the database: 1.) via Oracle’s Application Express Web-Based Interface; and 2.)Via Oracle’s Command-Line Application, SQL*Plus

For the purpose of this tutorial, I will assume that you have followed my previous tutorial, which guides you through the process of installing Oracle Database 10g Express Edition (Oracle XE) on a Windows XP computer. The schemas, usernames and passwords I refer to will only work if you have set your system up as described in that lesson.

Figure 2a - Oracle Database Express Edition Login Screen

First, I am going to log into Oracle Application Express, which will allow me to manage the database via a neat web-based interface. From your Start Menu, go to Programs -> Oracle Database 10g Express Edition -> Go To Database Home Page. This will take you to a screen that should look similar to figure 2a above. On this screen enter the username somando and the password somando1. These login details will only work if you followed my previous tutorial and set up the user as described. If you used a different username and password when you created the user, be sure to use those credentials instead or you will not be able to access Application Express.

Figure 2b - Oracle Database Express Edition Main Menu

Once you have successfully logged in, you should see a screen similar to that in Figure 2b above. I am now going to create a database table. To do this, click on the arrow to the right of the Object Browser image icon. From the Create submenu, choose Table.

Figure 2c - Create Table Form

This screen will allow you to define a table, with fields for the table’s name, and for 8 columns. To add more than 10 columns, you can click the Add Column button. I am going to create a table called category with three columns, id, name, and parent_id. Don’t worry about the purpose of these fields for now, I’ll explain it in more detail at a later stage. Fill out the form exactly as you see in Figure 2c, and click the Next > button.

Figure 2d - Define Primary Key Form

Next you will need to set up the primary key for the table. I am going to set up the id field as our primary key, populated automatically from a sequence. To do this, select Populate from a new sequence from the list of options for Primary Key, and accept the default values for the Primary Key Constraint Name and Sequence Name fields. Now, select ID(NUMBER) from the drop-down list for the Primary Key field and press Next >.

Figure 2e - Define Foreign Keys Form

I am not going to set up any foreign keys at this time, so on the Foreign Keys screen (see Figure 2e), you can simply click the Next > button and move on. The next screen allows you to set up unique keys and check constraints. Again, I will not be using either of these right now, so you can simply press the Finish button.

Figure 2f - Confirm Create Table Screen

Finally, you will be asked to confirm your request to create the category table on the somando schema. If you press the SQL link, you will be shown the SQL statements that will be used to create the table. The code should be as follows:

CREATE table "CATEGORY" (
    "ID"         NUMBER(11,0) NOT NULL,
    "NAME"       VARCHAR2(255) NOT NULL,
    "PARENT_ID"  NUMBER(11,0) NOT NULL,
    constraint "CATEGORY_PK" primary key ("ID")
)
/

CREATE sequence "CATEGORY_SEQ"
/

CREATE trigger "BI_CATEGORY"
    before insert on "CATEGORY"
    for each row
begin
    select "CATEGORY_SEQ".nextval into :NEW.ID from dual;
end;
/

If all looks in order, go ahead and click the Create button, which will issue the above SQL code to the database, which will in turn create the table for you. If the create operation has completed successfully, you should see a screen similar to that displayed in Figure 2g below:

Figure 2g - Category Table Properties Display

Congratulations, you have created your first Oracle database table! That wasn’t so difficult now, was it? I will now take a break from using Application Express and use the SQL*Plus client that comes with Oracle Database 10g Express Edition to interact with the new table I have just created. To start SQL*Plus, go to Start -> Programs -> Oracle Database 10g Express Edition -> Run SQL Command Line. This will open up a DOS-style window with an SQL prompt ready and waiting for you, which should look like Figure 2h below:

Figure 2h - Run SQL Command Line

Now that we have started SQL*Plus, we can connect to our database schema. To do this, type CONNECT and hit enter. You will now be asked for a username followed by a password, which, if you followed my previous tutorial, will be somando and somando1, respectively. If you have supplied the correct login information, you will see a message informing you that you are connected. To make sure you are connected to the correct database, type DESC category and hit enter. If you have followed this tutorial correctly, you should see output similar to the following:

Figure 2i - Result of DESC category SQL statement

Our table isn’t much good to us if we’re not going to store any data in it, so now we will use SQL to insert a few rows into the table. Teaching you the SQL language is out of the scope of this tutorial, so if you are finding it difficult to grasp some of the statements we use, it might be a good idea to read the Oracle SQL tutorial available at http://www.db.cs.ucdavis.edu/teaching/sqltutorial/ which should bring you up to speed.

To follow the code examples in this tutorial in SQL*Plus, enter each line of code, line by line, pressing enter at the end of each line. The SQL interpreter will not process your code until you issue a semicolon or forward slash, telling it that you have finished typing your statement. Don’t be afraid to make mistakes, the best way to learn is often to figure out how to correct your errors. So now, let’s get started, type up the following code listing in your SQL Command Line window:

INSERT INTO category(name, parent_id) VALUES('Oracle', 0);
INSERT INTO category(name, parent_id) VALUES('SQL Server', 0);

You will notice from this code that you are issuing two statements, each of which will create a new row in the category table. After you have entered each line, you should see the message 1 row created which indicates that your statement was executed successfully. This data will now persist for the remainder of your SQL session, but because of Oracle’s architecture, you will need to “commit” the changes in order for them to be stored permanently. This process is very simple, all you need to do is type COMMIT and press enter. A message Commit complete will tell you that the command worked.

You may have noticed that the INSERT statements we used to populate the table only featured two of the three fields in our category table. This is because we have a sequence and trigger (as set up earlier using Application Express) which automatically handle the creation of a unique number for the id field every time we perform an insert on the table.

Before we select the data back from the database, I am going to issue two commands which will help to format our data a little nicer. Do not concern yourself with these commands for now, as they are not of any particular importance. You may be wondering why I don’t issue a semi-colon or forward slash after these commands. The reason for this is that they are SQL*Plus commands rather than SQL statements. Again, don’t worry too much about the difference for now.

SET LINESIZE 300
SET HEAD OFF

Now that I’ve changed these settings, we are ready to select back our data from the database. I am going to issue two statements, one which returns the number of rows in the table, and a second which returns the ID and Name columns of all rows in the table.

SELECT COUNT(*) FROM category;
SELECT id, name FROM category;

The first command should result in the value 2 being output to the screen. The second statement will then display two rows, the first showing 1 Oracle and the second outputting 2 SQL Server, as can be seen in the following figure:

Figure 2j - Results of SELECT statements

Before we finish, I’m going to delete one of the rows we have created using a DELETE statement. This code also introduces the concept of the WHERE clause, which allows you to restrict the rows affected by an SQL statement by defining conditions which the data must meet in order for the specified action to be performed on that row.

DELETE FROM category
WHERE id = 2;
COMMIT;

In this lesson, I have covered the basics of creating a table using Application Express, and working with that table using the SQL*Plus command line tool. Both of these applications are very powerful and can be used to create full-blown databases and software. In future tutorials I will be looking at creating web applications using the Oracle PL/SQL language and HTML/CSS/JavaScript, and I’ll also be showing you how to use other useful tools such as Oracle’s SQL Developer.

Click here to view a printer friendly version of this tutorial. To download this tutorial to disk, right click here and choose “Save Target As” or “Save Link As”. You will need Adobe Reader to open this file.

Oracle Database 10g Express Edition (I will refer to this as Oracle XE from here on) is a basic version of the Oracle Database 10g that is freely available for development, deployment and distribution. It is an excellent entry-level database for learning Oracle and PL/SQL and will run on most modern PCs and laptops. I will be using Oracle XE in the majority of the Oracle tutorials and sample applications on this website, so it is highly recommended that you install it so you can follow my examples closely and precisely.

In order to install Oracle XE on your computer, you will need to download it from Oracle’s website. It is available for Microsoft Windows and x86 Linux distributions. For the purpose of this guide I will be showing you how to install Oracle XE on Microsoft Windows XP, but the procedure should be similar for other Windows operating systems.

Oracle has an extensive Installation Guide for its Oracle XE product. Windows users can view this guide at: http://download.oracle.com/docs/cd/B25329_01/doc/install.102/b25143/toc.htm.

Linux users, check out Oracle’s Installation Guide at: http://www.oracle.com/technology/software/products/database/xe/files/install.102/b25144/toc.htm.

This guide will have many similarities to Oracle’s Installation Guide above, but if you follow any naming conventiones I use during installation it will make it much easier for you to follow any future Oracle guides I make available.

The first step in installing Oracle XE is to download the installation package from Oracle’s website. The direct URL to the Windows binary download page is http://www.oracle.com/technology/software/products/database/xe/htdocs/102xewinsoft.html. On this page, you must first accept the License Agreement by clicking on the radio button towards the top of the page. Once you have clicked on this, you can click on the relevant download links to download the installer. It is worth mentioning at this stage that you must be logged in to the Oracle Technology Network (OTN) to download Oracle XE. Registration to OTN is free and if you are not logged in you will be prompted to login or register before you will be allowed to proceed with the download.

Figure 1a - Oracle Database 10g Express Edition Download Page

The version I am using in my guides is the Oracle Database 10g Release 2 (10.2.0.1) Express Edition for Microsoft Windows (Western European). The download is highlighted in blue on Figure 1a above. It weighs in at a heavy 157mb, so it’s best to download this over a high-speed Internet connection if possible.

Once you have downloaded the installation package, run the OracleXE.exe file to start the installer.

Figure 1b - Oracle Database 10g Express Edition InstallShield Wizard

On this welcome screen (see Figure 1b), press Next (Alt+N) to continue to the next screen.

Figure 1c - Oracle Database 10g Express Edition License Agreement

On the License Agreement screen (Figure 1c), select “I accept the terms in the license agreement” (Alt+A) and then press the Next button (Alt+N) to move on.

Figure 1d - Oracle Database 10g Express Edition Destination Location

On the “Choose Destination Location” screen (Figure 1d) leave the default values as they are in Figure 1d and press Next (Alt+N) to continue.

Figure 1e - Oracle Database 10g Express Edition Database Passwords

On this screen (“Specify Database Passwords” – Figure 1e), you can enter a password that will be used for the SYS and SYSTEM database accounts. Throughout the course of this guide I will always use the password somando1 and I suggest you do the same to make it easier to follow my guides. Obviously these passwords should only be used on development machines, with much stronger and secure passwords being used on production servers. Once you have entered and confirmed the password, hit Next (Alt+N) to continue.

Figure 1f - Oracle Database 10g Express Edition Installation Summary

You will now be presented with a summary of the installation options (make a note of the port numbers, they should be as in Figure 1f). If all looks in order, click Install (Alt+I).

Figure 1g - Oracle Database 10g Express Edition Setup Status

The installation should now begin, and you may see a screen similar to Figure 1g. The installer will automatically copy the relevant files to your computer and will configure the database. This process may take a considerable amount of time depending on your computer’s specification. On my machine (an Intel Core 2 Duo T7300 2.00GHz with 2GB of RAM) the process took less than five minutes.

Figure 1h - Oracle Database 10g Express Edition Installation Complete

Once the installation has completed you will see a screen like in Figure 1h above. Leave the checkbox for “Launch the Database homepage” checked and click the Finish button. You have now successfully installed Oracle XE on your computer.

Figure 1i - Oracle Database Express Edition Login Screen

If you left the checkbox in Figure 1h checked, your default web browser will open and you will see a page similar to that shown in Figure 1i. Enter SYS in the field for Username and somando1 in the field for Password and click the Login button.

Figure 1j - Oracle Database Express Edition Homepage

If you were able to login successfully, you should see a page similar to that in Figure 1j, which is basically a web-based control panel called Application Express. This utility is in fact a powerful web application which provides you complete control over your database. At this point I highly recommend that you click on the “Getting Started” link at the top right hand section of this screen, which will bring you to a local version of Oracle’s Database Express Edition Getting Started Guide. This guide can also be accessed online at http://download.oracle.com/docs/cd/B25329_01/doc/admin.102/b25610/toc.htm.

The Getting Started Guide will walk you through the process of unlocking a sample user, logging in under the sample user’s account, and creating and running a simple application with Application Express. It will take you 20 minutes to get through, and will give you an idea of how simple it is to create powerful applications using Application Express.

Finally, I will create a user/schema that I will be using in my guides. All the tables that I create in future guides will be created in this schema. To create a user, open the Database Home Page (Start -> Programs -> Oracle Database 10g Express Edition -> Go To Database Home Page) and login with the username SYSTEM and password somando1. Click on the Administration image, then click on Database Users, and then the “Create >” button.

Figure 1k - Create Database User

On the Create Database User screen (as in Figure 1k above), enter the following details:

• Username: somando
• Password: somando1
• Confirm Password: somando1
• Expire Password: Leave this option unchecked
• Account Status: Unlocked
• Roles: Connect and Resource should be checked, DBA should be unchecked.
• Click on the Check All link at the bottom right of the User Privileges box to give this user all Direct Grant System Privileges.

Once you have filled out the form accordingly, click on the Create button at the top right hand corner of the Create Database User box. Once you click this button, the user/schema somando should be created. In order to verify that the user has been set up correctly, click on the Logout link at the right hand top corner of the page, and log back in, except this time log in with the username somando and the password somando1. If you can successfully log in, the user/schema has been created successfully.

That concludes this tutorial. In the next guide, I will be looking at creating tables and inserting data using Application Express. I will also be introducing the SQL Command Line (SQL*Plus) and some basic SQL statements.

Click here to view a printer friendly version of this tutorial. To download this tutorial to disk, right click here and choose “Save Target As” or “Save Link As”. You will need Adobe Reader to open this file.