Previous | Table of Contents | Next

developer.com - Reference Library

Page 601

APPENDIX D

Practice Database
Installation
Instructions

The examples used in this book require an Oracle7 database and the Developer 2000 tool products. If you do not have access to this database or the tools, a trial version may be obtained from Oracle. This trial version contains a copy of Personal Oracle7 and the complete Developer 2000 tool kit. At the time of this writing, Oracle allows interested parties to download the products from their Web site for a 60-day evaluation at no cost. For $15, Oracle will send a CD to you. I received a trial copy and installed it on my PC with no trouble. I would like to caution that this offer may be withdrawn at any time by Oracle and may not be available to you. Oracle's Web site address is http//www.oracle.com/. I strongly urge you to visit the site and check the availability of trial products.

If you do not have access to Personal Oracle7 and the Developer 2000 tools on your PC, you may be able to access the products at your place of employment. Oracle7 is normally used in the client/server environment. Your place of employment is probably using Oracle7 and the Developer 2000 tool set on a server. Contact your database administrator (DBA) to see if a user id and tablespace can be set up for the practice database. The database can be easily installed on the server by your DBA.

Page 602

The database used for practice throughout this book consists of seven tables. The tables are
identified in Table D.1.n

Table D.1 Practice Database Tables

Table Name Description Department Contains employee department attributes. Employee Contains employee attributes. Glasses Contains records on employee eyeglass purchases. Sectab Used for security examples. This table is normally empty. Tools Used for employee tool purchase records. Wge_maint A wage maintenance table. It is normally empty.

The database can be installed in your Oracle7 database by using either one of two methods. The first is to import the tables by using Oracle's import/export facilities. This is the quickest and easiest method. The second is to create the tables and load them by using SQL*LOADER. The CD has the files and data for both options. The following describes each of the methods.

Importing the Database

Personal Oracle7 comes with import and export facilities. The import facility is used to put the tables into the database. This icon is located in the Personal Oracle7 group. Figure D.1 displays the Personal Oracle program group icons and among them is the Import tool icon.

FIG. D.1
The Personal Oracle7 icon group and the Import facility.

Double-clicking this icon opens the Import dialog box. A modal dialog box requesting Database Logon information appears. Enter the values of a user id with DBA privileges. The default Personal Oracle user id and password is "system/manager." Click OK when done. The Database Importer dialog box appears, as shown in Figure D.2.

The dialog box has three settings. The first is the name of the Import file. It is called EMPEXP.DMP and is on the CD. The Users radio button should be clicked within the Import mode. The User to Import window should contain the name of the user id that will receive the

Page 603

tables. In Figure D.2 the user id is test_user. The default user_id in Personal Oracle7 is "scott." If a different user id is used, it must be created. The window should contain the name of the id that is sending the data. This id is "scott." The Specify button can be used to call a dialog box that will allow you to input the proper user ids.

FIG. D.2
The Database Importer dialog box.

Click the Import button to import the tables into the user id. The server will process the Import request, then, if it is successful, a dialog box will appear stating that the Import procedure was successful. A dialog box will appear stating that the import procedure was successful. If the process is not successful, the server sends an exception (error) message to the user interface.

NOTE
If you are using Personal Oracle7, be sure to start the database before employing either of the two methods. The Personal Oracle7 icon group has a Database Manager tool that can be used to start the database. The default password is oracle. The password does not include parentheses.n

If you are using Personal Oracle7 for Win95, Oracle7 version 7.2, or Oracle8, the tool icons or folders have changed. The procedure to import the data has remained fundamentally the same.

Creating and Loading Your Tables

The second method is to create and load the tables. If you are using Oracle7 on a server, have a DBA establish user id and tablespace for you. You must have the ability to create tables and to add, update, and delete records. The DBA must also grant you these privileges. If you are using Personal Oracle7, you can use the "scott/tiger" user account id. This is the default user id and has all of the needed privileges.

Page 604

Log on to SQL*PLUS. Chapter 3, "Acquiring Data by Using the Select Statement," describes this process. Run the PL/SQL scripts listed in Table D.2, which are located on the CD.

Table D.2 Table Create Programs

File Name Description DEPART.DDL Creates the Department table. EMPLOYEE.DDL Creates the Employee table. GLASSES.DDL Creates the Glasses table. SECTAB.DDL Creates the Security table. TOOLS.DDL Creates the Tools table. WGEMNT.DDL Creates the Wage Maintenance Table.

NOTE
The tables should be created in the order in which they are listed in Table D.2. The files are executed by using the following syntax: run drive\filepath\filename.

The following command executes the first create file in Table D.2. It assumes the file is located on the CD drive or d drive: run d:\depart.ddl. Review Chapter 3, "Acquiring Data by Using the Select Statement," for further help in executing files.n

After the tables have been created, the data may be loaded into them by using the SQL*LOADER product. If you are using Personal Oracle7, SQL*Loader is launched by clicking the icon located in the Personal Oracle7 group (see Figure D.1).

If the database is located on the server, SQL*Loader is launched by executing the sqlldr71.exe, sqlldr72.exe, sqlldr73.exe, or sqlldr80.exe files. The last two digits of the file name refer to the version of the Oracle database. The file is normally located in the \orawin\bin directory. If you cannot find the tools, contact your DBA.

Table D.3 contains the names of the load programs.

Table D.3Load Programs for the Practice Database

File Name Description LOADDEPT.CTL Loads the Department table EMP1.DAT Loads the Employee table GLA1.DAT Loads the Glasses table TOOL1.DAT Loads the Tools table

Page 605

NOTE
The tables should be loaded in the order in which they are listed in Table D.3. Review Chapter 10, "Using SQL*LOADER," to determine how to execute the files.n

If you are unable to locate the SQL*LOADER icon, you may load the table by using line commands. To do so, perform the following:

  1. Locate the SQL*LOADER executive program. This can be done by performing a hard drive search on the following character set: "sqlldr*.exe".
  2. After locating the sqlldr72.exe, sqlldr73.exe, or sqlldr80.exe file, open the DOS Prompt window.
  3. Change to the directory that contains SQL*LOADER.
  4. Use the following line command to start SQL*LOADER: sqlldr72 scott. (This assumes the name of the SQL*LOADER program and the name of the Oracle user account.)
  5. You will be prompted for the name of the control file. Use the name from table D3 (i.e., d:\loaddept.ctl, d:\emp1.dat, d:\gla1.dat, or d:\tool1.dat).
  6. You will be prompted for the user account password. Enter the password. The default password for the scott user id is tiger.

Page 606

Previous | Table of Contents | Next