Database Management Systems

by Raghu Ramakrishnan and Johannes Gehrke

[Database Management Systems (3rd Edition)] [WWW Resources] [Material for the Third Edition] [Material for the First Edition] [Material for the Second Edition]

Oracle Guide

This Guide will introduce you to using Oracle for several basic tasks, as well as how to configure the Oracle environment and use special features of its user interface. This guide is divided into the following sections:

Getting Connected
Tables
Data
Constraints
Using SQL*Plus

When you are done, you may go back to the Welcome Page. You can also register for free and access the online Oracle documentation at technet.oracle.com

Getting Connected

In order to interact with Oracle you should use the SQL*Plus Application. This application is available on both the windows and Unix platforms.

On Windows go to the Start Menu, then select Start >Programs > Oracle - DBBOOK_HOME > Application Development > SQL * Plus. Use your installation name in place of DBBOOK_HOME.
On Unix you can type sqlplus from a Unix Shell. Your instructor should have provided information on how to configure your account and login to Unix.

You will be prompted for your username, connect string, and password. If you are using Oracle on windows with the CD accompanying the text, then you may leave the connect string blank. On Unix the connect string will follow your username and the @ symbol in the format username@connectstring.

Note: If you have installed Oracle on Windows, you may use it to connect to your Unix based server. In order to do this copy the entries from the tnsnames.ora in the $ORACLE_HOME/network/admin directory on the Unix system to the tnsnames.ora file on the Windows machine. Then, use the connect string as you would in Unix. There is also a version of SQL*Plus on windows that works in command line mode, which is identical to the Unix version.

Sample Session

student|computer-one|~|[1]% sqlplus

SQL*Plus: Release 9.0.1.0.1 - Production on Sat Jun 8 15:28:53 2002

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Enter user-name: student@oracle.cs.someplace.edu
Enter password:

Connected to:
Personal Oracle9i Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL>

Tables

Creating Tables

Oracle data is stored in tables which represent your fundamental schema design. You create them with the create table command:

create table tablename ( columnname type, columnname type ...);

where tablename is the name of your table, columnname is the name of your column and type is the type as specified in the table below. There are additional types available, however these are the most common. Give some thought as to the order of columns as it can effect the default order they are displayed and data is entered.

Type	Description
varchar2(n)	variable length string up to n with maximum 2000 characters. Specified as 'characters' where characters are the characters in the string.
date	holds a date. By default they are specified as day-month-year as in '12-DEC-1990'
number	integer or real value up to 40 decimal digits
number(n)	up to n digits
number(n,d)	up to n digits with d after the decimal point. The use can help provide formatting information to applications

Observing Table Information

The easiest way to get information about a table is with the DESCRIBE command:

DESCRIBE tablename

Altering Tables

To alter a table use the alter table command:

alter table tablename add(  columnname datatype, ...);
alter table tablename modify ( column newdatatype);
alter table drop column columnname;

Deleting Tables

To delete a table use the following:

drop table tablename;

To Drop all your tables you can use the following in a script:

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET MARKUP HTML OFF
SET ESCAPE \
SPOOL DELETEME.SQL
select 'drop table ', table_name, 'cascade constraints \;' from user_tables;
SPOOL OFF
@DELETEME

Sample Session

SQL> create table sign_sales(Color varchar2(30),date_sold date,
  2  price_each number);


Table created.

SQL> alter table students add( sign_shape number);


Table altered.


SQL> alter table students modify (sign_shape varchar2(10));


Table altered.


SQL> alter table students drop column sign_shape ;


Table altered.

SQL> DESCRIBE sign_shape

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
COLOR                                              VARCHAR2(30)
DATE_SOLD                                          DATE
PRICE_EACH                                         NUMBER

SQL> drop table sign_sale;


Table dropped.

Data

Inserting Data

Working with data is fundamental to database systems. It is possible to load data with SQL. It can also be done using the Oracle bulk loader. To load values in SQL*Plus use the following syntax:

Insert into tablename values (somevalue, somevalue, ...);

where somevalue is a value to insert and tablename is the name of the table. Values are inserted in the order of the columns of the table. The first value is inserted into the first column, etc.

Querying Data

Querying data in SQL is one of a database systems major tasks, and can not be fully described here. In its most basic form a query consists of the SELECT statement:

select columnname, columnname... from tablename;

Where columnname is the name of a column or * for all columns and tablename is the name of the table.

Altering Data

You can alter data with the update expression. The optional where clause limits the rows effected. The set clause describes the change as follows:

update tablename set columnname=expression where whereclause;

Deleting Data

You can delete data with the delete expression. The optional where clause limits the rows effected.

delete from tablename where whereclause;

Sample Session

SQL> insert into signs values(19.95,'White','Rectangle','Park Somewhere Else');


1 row created.


SQL>
SQL> select * from signs;


PRICE_EACH COLOR     SHAPE	  DESCRIPTION
---------- --------- ------------ -------------------
19.95      White     Rectangle    Park Somewhere Else


SQL>
SQL> update signs set price_each = 4.00 where price_each < 20;


1 row updated.


SQL>
SQL> delete from signs ;


1 row deleted.


SQL>

Constraints

Adding Constraints

In order to finish realizing a schema in Oracle, it is necessary to add the required constraints. Oracle supports several types of constraints, to enforce complex rules may require the use of active database features such as triggers. In this section the examples are limited to key constraints.

Constraints can be added to new tables or after table creation. To add primary key constraints to a new table specify the primary key after the columns similar to the following:

create table tablename ( columnname type, columnname type ..., primary key(keycolumn,keycolumn,...);

where keycolumn is the name of a column that is part of the key.

Foreign keys should refer to unique tuples. To create a new table with foreign keys in addition use:

create table tablename ( columnname type, columnname type ..., primary key(keycolumn,keycolumn,...),
foreign key(foreignkeycolumn,foreignkeycolumn,...) references foreigntable,
foreign key(foreignkeycolumn,foreignkeycolumn,...) references foreigntable,...);

where foreignkeycolumn is the column of the foreign key in table foreigntable. It must match a column in the current table. You can also add constraints with the alter table command where tablename is the name of the table and tableconstraint is the constraint definition:

Alter table tablename add tableconstraint;

Observing Constraints

Often you may wish to see what constraints exist on a table. The view USER_CONSTRAINTS is maintained by Oracle and has information about constraints. Some interesting columns include, the CONSTRAINT_NAME, CONSTRAINT_TYPE where P refers to primary key, and R is a foreign key constraints, the TABLE_NAME on which it is defined, and the R_CONSTRAINT_NAME for foreign key constraints. Type DESCRIBE USER_CONSTRAINTS for more informaion.

Once you know the CONSTRAINT_NAME, you can use the USER_CONS_COLUMNS view to find the COLUMN_NAME and POSITION for that CONSTRAINT_NAME. You can query these views as you would any other table:

select column_name, position, constraint_name from User_cons_columns;

Using Constraints

At times you may need to create constraints that are interdependent, such as two tables that refer to each others primary key in a foreign key constraint. One way to deal with this problem is to defer constraints. They constraint must be made deferrable and the constraints mode set to deferred. You can substitute the names of specific constraints or use the all keyword. They will be checked at the end of the transaction instead of after each insert. To enable them make the mode immediate:

create table tablename ( columnname type, columnname type ...,
foreign key(foreignkeycolumn,foreignkeycolumn,...) references foreigntable deferrable);

set constraints all deferred;

set constraints all immediate;

Deleting Constraints

Constraints can be deleted with the drop constraint command. where tablename is the table name and someconstraint is the constraint name:

alter table tablename drop constraint someconstraint;

Sample Session

SQL> create table bids( bid_id varchar2(10), bidder_id varchar2(10), item_id varchar2(10),
  2  bid_amount number, primary key(bid_id), foreign key ( item_id ) references
  3  auction_items, foreign key (bidder_id) references members(member_id) deferrable);


Table created.


SQL>
SQL> select constraint_name, constraint_type from user_constraints where
  2  table_name='BIDS';


CONSTRAINT_NAME                C
------------------------------ -
SYS_C001400                    P
SYS_C001401                    R
SYS_C001401                    R


SQL>
SQL> alter table students drop constraint SYS_C001400;


Table altered.


SQL> alter table students add primary key( bid_id );


Table altered.


SQL> set constraints all deferred;


Constraint set.

Using SQL*Plus

SQL*Plus provides an interactive command line interface to Oracle. In it you type various commands to manipulate your data. It has many built in features.

Ending Your Session

To end your session type:

quit

In the windows version you can select Exit from the file menu.

Changing Your Password

Type passw to change your password. Try to avoid certain characters like @ and / in your password, as these will confuse oracle.

passw

Editing Commands

Commands in Oracle are stored in a buffer until ended with a semicolon ; and are also accessible immediately after execution. They may use several lines. If you make a mistake it is easier to edit your command then type it all over. You can use the edit command to edit a file, or select Edit >Editor > Invoke Editor in Windows.

edit

This will edit the command in the buffer. You may also specify a file as a parameter. By default the editors are vi in Unix and notepad on Windows. You may change the editor where editorname is you editor by using the Define Editor menu in Windows or:

define _editor = editorname

You may execute the command in the buffer by either the command RUN or a single slash (/). You can use GET and SAV to retrieve or save the buffer to a file. You can also change a single line with C to replace the old text with the new.

C/old/new

Committing Data

Whenever you have finished a series of changes and you want to insure they are part of the database you should commit the transaction. This will make the changes visible to other database sessions. type:

commit;

If you make a mistake you can undo the change with:

rollback;

It will restore data to the last commit. Some commands automatically result in commit, such as creating a table.

Logging Sessions

SQL*Plus can record your commands and output to a file. This is useful for class assignments that ask for a log of your session, or in automated self generating scripts. To do this type:

spool filename

where filename is the name of the file to save the output. In windows you can use the File > Spool >Spool File Command. To stop recording to a file select File >Spool> Spool Off or type

spool off

Using Special Characters

Sometimes you may need to use a special character such as & or ; in a query that is used by SQL*Plus. You can define an escape character. You may then use it in queries before a special character. It is often useful in automated scripting. To escape the quote character use multiple quotes

set escape \

select 'It''s mine\; I like it' from dual;

You may also turn escape on and off.

set escape off

Loading Commands From A File

If you prepared commands in advance, for example by developing them at home to load onto another system type:

@filename.sql

where filename.sql contains the commands to run. The File > Open menu can also be used.

Running Commands At Login

There is a login file that you may create in the directory from which you call sqlplus. Put the commands in the file called login.sql This is especially useful for common tasks such as setting the editor.

Changing Date Formatting

Oracle provides several options in how both input and output are formatted. One consistent formatting parameter that you may need to change is the date formatting. The ALTER SESSION command allows you to change the date format. use the SET NLS_DATE_FORMAT command followed by the date format string. The default format is DD-MON-YY. Some common Code include:

String	Meaning
YYYY	Year
MM	Month
DD	Day
HH	Hour
HH24	Hour (24 hour clock)
MI	Minuite
SS	Second

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI'

Changing Output Formatting

You can also change the output formatting in SQL *Plus. This can disable any of the headers normally included with output or change there size and location. This is also helpful when exporting output. For example, the code below disables all heading informaion.

 SET NEWPAGE 0

 SET SPACE 0

 SET LINESIZE 80

 SET PAGESIZE 0

 SET ECHO OFF

 SET FEEDBACK OFF

 SET HEADING OFF

Getting additional help

SQL*Plus has an on line help system. To use it type:

help command

where command is the command you need help with. To obtain a list of commands with help available type:

help index

Sample Session

SQL> spool answer.txt
SQL> DEFINE _EDITOR= pico
SQL> spool off
SQL> commit;


Commit complete.


SQL> select username from junk;
select username from junk
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> c /junk/user_users
  1* select username from user_users
SQL> /


USERNAME
------------------------------
STUDENTNAME


SQL> quit
Disconnected from Personal Oracle9i Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
student|computer-one|~|[4]%