Oracle Tips by Burleson
For the remainder of the book we will create
examples that use the PUBS database schema.
This database was designed as a teaching tool and is available in the
pubs_db.sql (see code depot)
The file is a list of Oracle commands that creates
and populates the database schema. The commands are discussed in
detail in the companion SQL
book, Easy Oracle SQL.
Installing the PUBS
database creates a user called pubs and grants the Role DBA to
that user. As such, this script should only be used in a
To install the PUBS database you
need a running oracle database and we assume that you have already
successfully installed the Oracle database. If not, you need to go to
otn.oracle.com and download the latest version of the Oracle
database. We recommend downloading the new Oracle XE database, which
is free and contains the latest version of the PL/SQL engine. All
the examples in this book were tested on Oracle9i,
Oracle10g and Oracle XE Beta.
When the database was created you assigned it a
System ID (called a “SID”) and OracleXE uses the SID named “XE”.
Create a practice directory and copy the
pubs_db.sql file to this
Start the database.
On Windows go to services, right click and start
the service OracleService<SID>. Then start the service
On Linux/Unix you must set the environmental
variables Oracle Home and Oracle SID before starting the database.
Substitute the correct information in the example below.
$ export ORACLE_HOME=/opt/oracle
$ export ORACLE_SID=mydb
$ sqlplus “/ as sysdba”
$ lsnrctl start
In both cases we
started two programs, the database and the listener. The listener
listens on a port (normally port number 1521 but you can change the
port) for a connection. When you connect to the database, the
listener takes your connection request and creates a server process to
perform the actual database work.
Open a terminal window and change into
the practice directory.
On Windows select Start – Run – type cmd
and press enter.
On Linux right click on the desktop and
Change to the practice directory
$ cd practice
All the examples in this book will use SQL*Plus. This
is Oracle’s command line tool for interacting with the database. We
will go into SQL*Plus
in much more detail later. Start
SQL*Plus as the super user.
$ sqlplus “/
After starting, SQL*Plus will leave
you at the SQL> prompt. To load the PUBS database,
you will need to run the pubs_db.sql
This will create the pubs user with a password of
pubs, create the tables and load the data. When you are through you
will be left as the user pubs. You can verify this with the command
Use exit to get back to the operating system
The code depot also contains a PowerPoint slide (PUBS_DB.ppt)
with the diagram of the PUBS database. The PUBS
database details information about publishing books. There is an
which list authors. Notice that
it contains a unique key called the auth_key that uniquely
identifies each author. The book table
lists the books. The store table list stores that sell the
books and the sales table list books sold, by order number, in each
store. A publisher also has and employee table for the
employees who print the books.
You can see the links between each table. Notice
the book_author table. This is a
weak entity needed to eliminate the many-to-many link between the
author table and the book table, representing
the fact that an author can write many books and a book can have more
than one author. The book_author table eliminates that
many-to-many link between author and book. The primary key in the
book_author table is both the auth_key and the book_key
(a multicolumn key).
The above book excerpt is from:
Easy Oracle PL/SQL Programming
Fast with Working PL/SQL Code Examples