Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

1.4 Creating and Loading the Sample Tables

Many of the examples in this book, particularly the
reporting examples, have been developed against an example database
for an employee time-tracking system. It's a fairly
simplistic database, containing only three tables, but
it's enough to illustrate everything I talk about in
this book. You may or may not wish to create this database for
yourself. Creating the database will allow you to try all the
examples in this book exactly as they are shown. If you choose not to
create and load the sample database, at least familiarize yourself
with the data model. Glance at the sample data, which are reproduced
later in this section. If you have looked at the model and at the
data, you shouldn't have any trouble following and
understanding the examples in this book.

Every numbered example in this book is provided in a set of example scripts that you can download from the catalog page for this book at Example 1-1 is in the file named ex1-1.sql, Example 1-2 is in the file named ex1-2.sql, and so forth.

1.4.1 The Data Model

Figure 1-3 shows an
Entity Relationship Diagram (ERD) for
the sample database.

As you can see from the ERD, there are only three entities:
EMPLOYEE, PROJECT, and
PROJECT_HOURS. Table 1-2 gives a brief description of each entity.

The number of employees and projects is fairly small. However, a
large amount of data in the PROJECT_HOURS table
allow for the generation of multiple-page reports, which are needed
to demonstrate pagination, page headings, page footings, and
summarization.

1.4.2 The Tables

This section shows the column
descriptions, including column datatypes and lengths, for each of the
three example tables. This is the same information you would get
using SQL*Plus's DESCRIBE command.

1.4.2.1 EMPLOYEE
table

Name Null? Type
------------------------------- -------- -------------
EMPLOYEE_ID NOT NULL NUMBER
EMPLOYEE_NAME VARCHAR2(40)
EMPLOYEE_HIRE_DATE DATE
EMPLOYEE_TERMINATION_DATE DATE
EMPLOYEE_BILLING_RATE NUMBER(5,2)

1.4.2.2 PROJECT table

Name Null? Type
------------------------------- -------- -------------
PROJECT_ID NOT NULL NUMBER(4)
PROJECT_NAME VARCHAR2(40)
PROJECT_BUDGET NUMBER(9,2)

1.4.2.3 PROJECT_HOURS table

Name Null? Type
------------------------------- -------- -----------
PROJECT_ID NOT NULL NUMBER(4)
EMPLOYEE_ID NOT NULL NUMBER
TIME_LOG_DATE NOT NULL DATE
HOURS_LOGGED NUMBER(5,2)
DOLLARS_CHARGED NUMBER(8,2)

1.4.3 The Data

This section shows the data contained in the three example
tables.

1.4.3.1 EMPLOYEE table

ID Name Hire Date Term Date Billing Rate
------ -------------------- ----------- ----------- ------------
101 Marusia Churai 15-Nov-1961 169.00
102 Mykhailo Hrushevsky 16-Sep-1964 05-May-2004 135.00
104 Pavlo Virsky 29-Dec-1987 01-Apr-2004 99.00
105 Mykola Leontovych 15-Jun-2004 121.00
107 Lesia Ukrainka 02-Jan-2004 45.00
108 Pavlo Chubynsky 01-Mar-1994 15-Nov-2004 220.00
110 Ivan Mazepa 04-Apr-2004 30-Sep-2004 84.00
111 Taras Shevchenko 23-Aug-1976 100.00
112 Igor Sikorsky 15-Nov-1961 04-Apr-2004 70.00
113 Mykhailo Verbytsky 03-Mar-2004 31-Oct-2004 300.00

1.4.3.2 PROJECT table

ID Project Name Budget
------ ----------------------------------- -------------
1001 Corporate Web Site 1,912,000.00
1002 Enterprise Resource Planning System 9,999,999.00
1003 Accounting System Implementation 897,000.00
1004 Data Warehouse Maintenance 294,000.00
1005 VPN Implementation 415,000.00

1.4.3.3 PROJECT_HOURS table

The PROJECT_HOURS table
contains the following information, repeated for each employee:

Proj ID Emp ID Log Date Hours Charged Amt Charged
------- ------ ----------- ------------- -----------
1001 101 01-Jan-2004 1.00 169.00
1003 101 01-Jan-2004 3.00 507.00
1005 101 01-Jan-2004 5.00 845.00
1002 101 01-Feb-2004 7.00 1,183.00
1004 101 01-Feb-2004 1.00 169.00
1001 101 01-Mar-2004 3.00 507.00
1003 101 01-Mar-2004 5.00 845.00
1005 101 01-Mar-2004 7.00 1,183.00
1002 101 01-Apr-2004 1.00 169.00
1004 101 01-Apr-2004 3.00 507.00
1001 101 01-May-2004 5.00 845.00
1003 101 01-May-2004 7.00 1,183.00
1005 101 01-May-2004 1.00 169.00
1002 101 01-Jun-2004 3.00 507.00
1004 101 01-Jun-2004 5.00 845.00
1001 101 01-Jul-2004 7.00 1,183.00
1003 101 01-Jul-2004 1.00 169.00
1005 101 01-Jul-2004 3.00 507.00
1002 101 01-Aug-2004 5.00 845.00
1004 101 01-Aug-2004 7.00 1,183.00
1001 101 01-Sep-2004 1.00 169.00
1003 101 01-Sep-2004 3.00 507.00
1005 101 01-Sep-2004 5.00 845.00
1002 101 01-Oct-2004 7.00 1,183.00
1004 101 01-Oct-2004 1.00 169.00
1001 101 01-Nov-2004 3.00 507.00
1003 101 01-Nov-2004 5.00 845.00
1005 101 01-Nov-2004 7.00 1,183.00
1002 101 01-Dec-2004 1.00 169.00
1004 101 01-Dec-2004 3.00 507.00 The detail is the same for each employee. They all work the same
hours on all projects. There are enough
PROJECT_HOURS records to produce some reasonable
summary reports, as you will see in Chapters Chapter 5 through Chapter 7.

1.4.4 Loading the Sample Data

In order to
load the sample data you will need an Oracle username and password.
If you are accessing a remote database (often the case for people
using Windows), you will also need a net service name. You must have
the necessary privileges and quotas to create tables in the database
you are using. Specifically, you must have the following system
privileges: CREATE SESSION ALTER SESSION CREATE TABLE CREATE VIEW CREATE TRIGGER CREATE PROCEDURE CREATE SYNONYM CREATE SEQUENCE CREATE TYPE (Oracle8 and higher)
Your DBA can help you with any of these items. Once you have a
username and password and have been granted the necessary privileges,
you can create the sample tables and data by following these four
steps:

Download and unzip (or untar) the script files.

Start SQL*Plus.

Log into your Oracle database.

Run the bld_db.sql script file.

If you are new to SQL*Plus and are completely uncertain how to start
it in your particular environment, you should first read the section
Section 2.2
in Chapter 2. Once you know how to start
SQL*Plus, you can come back here and run the script to create the
sample tables and fill them with data.

1.4.4.1 Step 1: Download and unzip the script files

The SQL scripts to create the tables
and data used for the examples in this book can be downloaded from
O'Reilly's web
site: http://www.oreilly.com/catalog/orsqlplus2
Download either SQLPlusData.zip or
SQLPlusData.tar.gz, depending on whether you
prefer to work with zip files (Windows) or tar files (Linux/Unix).
Extract the contents of the file that you download into a directory
on your hard disk.

You can extract the script files into any directory you wish, but if you're a Windows user you may want to avoid using a directory with spaces in its name, or with spaces in any of the parent directory names. Some releases of SQL*Plus throw errors when confronted with path and filenames containing spaces.

1.4.4.2 Step 2: Start SQL*Plus

SQL*Plus has three variations: command-line, Windows GUI, and
i SQL*Plus. Unless you know a bit about SQL*Plus
already and know how to connect to i SQL*Plus in
your environment, you'll find it easiest to use
command-line SQL*Plus to load the example data. For Windows users,
this means opening a command-prompt window, which I know is something
not often done under Windows.

Once you have a command prompt, navigate to the directory into which
you unpacked the example scripts. Make that directory your current
working directory. For example, under Linux:

oracle@gennick02:~> cd sqlplus/ExampleData
oracle@gennick02:~/sqlplus/ExampleData>

or under Windows:

C:\Documents and Settings\JonathanGennick>cd c:\sqlplus\ExampleData
C:\sqlplus\ExampleData>

Next, invoke SQL*Plus using one of the following forms:

sqlplus username
sqlplus username@net_service_name Use the first form if you're running SQL*Plus on the
same computer as the Oracle instance. Use the second form if
you're accessing Oracle over a network connection.

Prior to the Oracle8 i Database release, the name of the SQL*Plus executable under Windows varied from one release to the next and followed the pattern plus80 (Oracle8), plus73 (Oracle7.3), etc. Thankfully, Oracle has recovered from this bit of insanity.

1.4.4.3 Step 3: Log into your Oracle database

After starting SQL*Plus, you'll be prompted for a
password. Enter the password corresponding to your username, and you
should be connected to your database:

oracle@gennick02:~/sqlplus/ExampleScripts> sqlplus sql_dude
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Apr 9 19:13:44 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

After you enter your password, you should see a message beginning
with "Connected to:", after which
you should land at the SQL> prompt. If you have
trouble logging in, ask your DBA for help. If you are the DBA, then
it's "Physician, heal
thyself!"

You may know that SQL*Plus allows you to enter your password on the command line. Avoid doing that. Many Linux and Unix systems make your command line visible to all users on the system. Don't give your password away by typing it on the command line. Let SQL*Plus prompt you for it instead.

1.4.4.4 Step 4: Run the bld_db.sql script file

The final step is to run the
bld_db.sql script
file, which is one of the files in the ZIP archive you downloaded in
step 1. To do that, simply use the @ command as shown below:

SQL> @bld_db Ideally, your current working directory will be the directory
containing the file. If that's not the case,
you'll need to specify the full directory path to
the script:

SQL> @c:\sqlplus\ExampleData\bld_db After you type one of these commands and press Enter,
you'll be prompted to confirm your intention to
create and populate the example tables:

SQL> @bld_db
This script creates the tables and sample data needed
to run the examples in the SQL*Plus book.
Do you wish to continue (Y/N)? Y Respond by entering Y or N and
pressing Enter again. In this example, I've
responded in the affirmative with Y.

If you make any mistakes in input while running the bld_db.sql script, the script will simply end. You'll get a message telling you to rerun the script and answer correctly. SQL*Plus is incapable of repeatedly asking you to retry bad input. A graceful exit is the most you can hope for.

Next, you'll be asked whether you wish to drop the
tables before creating them:

You have the option of dropping the sample
tables before creating them. This is useful
if you have previously created the sample
tables, and are recreating them in order to
reload the original data.
Do you wish to DROP the tables first (Y/N)? N This option to first drop the sample tables is convenient if you have
loaded them before and wish to reload them quickly. If this is your
first time running this script, you should answer this question with
N. If you have loaded the tables previously, and
you know that they exist now, then you should answer with
Y.

Now you can just sit back and watch while the script creates the
example tables and populates them with data. You'll
see progress messages such as these:

Creating employee table...
Creating project table...
Creating project_hours table...
Creating projects...
Creating Employees...
Creating employee time log entries for 2004...
Thank-you for loading the sample data!
Please press ENTER.

The entire load process should take less than a minute. When the load
is complete, you will be asked to press Enter one final time. Be sure
to do that! Then you can use the
EXIT
command to
leave SQL*Plus and return to your operating system command prompt:

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition
Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
oracle@gennick02:~/sqlplus/ExampleData>

Now that you have loaded the sample data, you can proceed with the
book and try out the examples as you go. Enjoy!