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.
1.4.1 The Data Model
Figure 1-3 shows an Entity Relationship Diagram (ERD) for the sample database.
Figure 1-3. 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.
Table 1-2. Entity descriptions
Contains one record for each employee. This record contains the employee's name, hire date, termination date, and billing rate. The primary key is an arbitrary employee ID number. The termination date for current employees is set to NULL.
Contains one record for each project that an employee may work on. Contains the project name and budget. The primary key is an arbitrary project ID number.
Each time an employee logs time to a project, a record is generated in this table. The record contains the number of hours charged against the project as well as the total dollar amount charged. The dollar amount charged is calculated at the time the record is created because an employee's billing rate may fluctuate over time. The primary key is a combination key made up of an employee ID, a project ID, and the date.
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.
18.104.22.168 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)
22.214.171.124 PROJECT table
Name Null? Type ------------------------------- -------- ------------- PROJECT_ID NOT NULL NUMBER(4) PROJECT_NAME VARCHAR2(40) PROJECT_BUDGET NUMBER(9,2)
126.96.36.199 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.
188.8.131.52 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
184.108.40.206 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
220.127.116.11 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:
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:
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.
18.104.22.168 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:
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.
Running bld_db sql from iSQL*Plus
If you're running i SQL*Plus, or a recent release of SQL*Plus, you're in for a real treat. You may not need to download any scripts at all to create the example data. From any release of i SQL*Plus ( assuming that you have Internet access), you can execute scripts directly from a web site, or from an FTP site, simply by giving a URL rather than a filename. Use the following command to invoke the example data script over the Internet:
Once you have invoked the script, you can follow the remaining instructions in "Step 4: Run the bld_db.sql script file" for responding to the prompts.
In addition to i SQL*Plus, you can invoke scripts directly over the Internet from all versions of SQL*Plus in Oracle Database 10 g and Oracle9 i Database Release 2, and from the Windows version of SQL*Plus in Oracle9 i Database Release 1. This is a much handier feature than you might initially think.
22.214.171.124 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 SettingsJonathanGennick> cd c:sqlplusExampleData C:sqlplusExampleData>
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.
126.96.36.199 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 !"
188.8.131.52 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:
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:
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 .
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!
Introduction to SQL*Plus
A Lightning SQL Tutorial
Generating Reports with SQL*Plus
Creating HTML Reports
Writing SQL*Plus Scripts
Extracting and Loading Data
Exploring Your Database
Tuning and Timing
The Product User Profile
Customizing Your SQL*Plus Environment
Appendix A. SQL*Plus Command Reference
Appendix B. SQL*Plus Format Elements
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
Authors: Jonathan Gennick
Similar book on Amazon
Secure Programming Cookbook for C and C++: Recipes for Cryptography, Authentication, Input Validation & More