MBA 669 E-Business Technology --

Home    Site Map    Search    EhrlichOrg.com

Syllabus    Classes    References    Student Info    Guest Info

Oracle Reference

>> under construction Oracle 8i Server 8.1.5 Documentation is available at http://hojohn.photo.net/ora81/DOC/ 

Data Modeling

See the Data Modeling presentation, example, Greenspun page 313, and http://photo.net/sql/data-modeling.html ??

SQL Names

Names of tables and columns in SQL are subject to the following rules:

We will use lower case table and column names.

SQL Data Types

Note, we are only using a subset of Oracle datatypes.  This section explains what we are using and gives a summary of other types.

Character Strings:

There are other character string types including:

String functions:

Dates:

Mask format elements for to_char and to_date include: 'D' day of week from 1-7, 'DAY' name of day, 'DD' day of month, 'DDD' day of year, 'DY' abbreviated day name, 'HH' hours, 'HH24' hours in military time, 'MI' minutes, 'MM' month number, 'MON' abbreviated month, 'MONTH' full month, 'YYYY' year, etc.

Round(date, format) will round dates according to the specified format; Trunc(date, format) will truncate date values.  Format values include: cc century, q quarter, yyyy or year year, month , ww the nearest Monday, etc.

For more information:

Numeric Types:

Oracle stores all numeric datatypes (Decimal, Integer, Float, Double Precision, and Real) are as Number.  Monetary amounts in dollars and cents would be number(15,2).

In many cases Oracle will convert between numbers and strings automatically.  When you want to control conversion and format use:

Mask format elements include '$' currency symbol, '.' decimal symbol, ',' comma, '9' numeric value or space, '0' numeric value or zero, 'S' sign character.

SQL Round can be used to control the length of calculated values.  Use round(value, precision) in your Select statement.  Precision may be 0 to round to an integer, positive to specify the number of places to the right of the decimal, or negative to round to a number of places to the left of the decimal.  For example, to get a value in dollars and cents specify

select round(average(sales),2) as avg_sales from foo

State Values

State values can be represented as character strings that are constrained to have one of a defined set of values.

>> fix up example

number_type varchar(15) check (number_type in ('work','home','cell','beeper'))

Sequence

A Sequence is a way of creating unique identifier values that are increasing but not necessarily sequential values.  See Greenspun pg 467, http://photo.net/wtr/thebook/case-studies.html, and http://www.photo.net/sql/transactions.html (at end of section).

Sequences are different from tables but are declared and named in a similar way.  Define a sequence in SQL Plus with a create statement.  For example:

Create sequence seq_name;

Create sequence seq_name start with 1000;

Sequence operators include:

Sequences are used within SQL statements.  For example:

insert into bday_reminders (reminder_id, email, event_description, event_date, remind_week_before_p, remind_day_before_p, remind_day_of_p) values (reminder_id_sequence.nextval, 'philip@greenspun.com', 'remember to finish PhD', '1993-06-01', 't', 't', 't');

The following TCL code sets seq_id to the next value of the sequence usf_seq (using the dummy table named dual):

set seq_id [database_to_tcl_string $db "select usf_seq.nextval from dual"]

The following code sets curr_id to the current value of the sequence usf_seq (using the dummy table named dual):

set curr_id [database_to_tcl_string $db "select usf_seq.currval from dual"]

The sequence usf_seq has been predefined for class use and does not require a prefix when referenced from within TCL code.  You may create your own sequence as follows:

1. Create one or more sequences in SQL Plus: Create sequence seq_name;
2. For each sequence do a grant to user AD like for a table: Grant all on seq_name to ad;
3. Prefix the sequence with your account name in references on .adp pages.

Dual

Oracle provides a dummy table named dual that can be used in queries.  For example:

set player_id [database_to_tcl_string $db "select player_id_sequence.nextval from dual"]

Predicate

Since Oracle does not have Boolean data type, we will use our own Predicate type to represent true/false values.  Note that we specify a default value so that we do not have to be concerned with Null values for predicates.

foo_p char(1) default 'f' (check foo_p in ('t', 'f'))

Null

Null is not really a data type, it is the value returned for attributes that have not had any value specified.  In other words, Null is the value of an undefined attribute.  Numeric calculations including a Null value will return Null, that is no data.

For numeric columns, use the Oracle NVL function to substitute a default value, like 0, for Null.  For example:

select budget_code, nvl(sum(amount),0) as spent, from actual;
 

When testing for Null you must use 'is null' like this:

select user_id from users where home_phone is null

The use of =null will not work:

select user_id from users where home_phone = null

Database Design

SQL Views

create view spent as select budget_code, sum(amount) as total, count(amount) as n from actual group by budget_code;
 

Rownum

Rownum is a psuedo column provided by Oracle that gives you the row number of a record returned by a query.  You can:

Note: values of Rownum are assigned BEFORE rows are arranged by any ORDER BY or GROUP BY statement.  In order to get row numbers in sequence, create a View and then use Rownum when selecting from the view.  For example to get the first 10 names:

create or replace view alpha_names as select first, last, id from names order by upper(last), upper(first)

select first, last, id from alpha_names where rownum<=10

Constraints

Primary Key

Index

A SQL Index speeds up retrieval of rows in certain conditions and forces values to be unique.  In some conditions it may not be possible to change the value stored in a column that is used as an index.  In general, we don't need to be concerned with making index columns.  An example of index creation is:

create index bday_reminders_idx on bday_reminders(email);

Interactive SQL

In order to use Interactive SQL you have to log onto the server using Telnet.  There are two ways to login to SQLPlus, normally you will log in automatically (sqlplus /).  In some cases you must login by name.

It is important that you type exit to log out of SQL when you are done or your database changes may not be saved.

Login Automatically

To use SQL Plus with the same user-name as you logged into Linux with, type 

sqlplus /

You will not have to enter user-name or password.

Login by name

Follow these instructions to login with a specified user name that may be different from your Linux account name.

Type sqlplus 

Enter user-name:  enter your user name as supplied.  Names are case sensitive.

Enter password:  enter your password exactly as given to you.  Note: your password will not be displayed and passwords are case sensitive.

Enter user-name: chuck
Enter password: 

Connected to:
Oracle8i Release 8.1.5.0.1 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production

SQL> 

Type exit to log off.

Interactive SQL Commands

Create Table

Drop Table

Deletes a table and all associated data.

Alter Table

Use Alter Table to add or drop columns

SQL> alter table s add (q varchar(10));

Table altered.

SQL> describe s;
Name        Null? Type
-------- -------- ----------------------------
SID               NUMBER
SNAME             VARCHAR2(10)
Q                 VARCHAR2(10)

Dropping a column.

SQL> alter table s drop (q);

Table altered.

SQL> describe s;
Name        Null? Type
-------- -------- ----------------------------
SID               NUMBER
SNAME             VARCHAR2(10)

Insert

Select

Use a where clause to specify values for a select and join (Greenspun 339, http://photo.net/sql/queries.html )

Search for key words contained in a column using Like and % to match zero or more characters.  For example this select with find Elvis Presley, Elvis Costello, and Dancing Elvises:
Select title, artist from cd_table where artist like '%Elvis%'

Delete 

Update

Column Formatting 

You can display only 10 characters of a varchar column by typing the following command for each column:

SQL> column s format A10;

This only effects the display and will not change your data.  

Longer values may be wrapped and displayed on multiple lines.  If you don't want wrapping type:

SQL> set wrap off;

Commit

The Commit command makes database changes permanent and closes the current transaction.  Up until the point at which you commit, you can use Rollback to undo your changes.

SQL> commit;

Commit complete.

Rollback

You can undo all the changes you have made since the last Commit command or the start of your SQL Plus session with the Rollback command.

SQL> rollback;

Rollback complete.

Count select count(*) from users;

Check ??

Query Hints (Greenspun p 336)

Import Data

Export Data

Upload (Greenspun p 512 does not work)

Show what tables, views, synonyms and sequences you may access:

select * from cat; 

Show 'your' tables and columns:

select table_name, column_name from cols;

select table_name from user_tables order by table_name;

describe some_table;

Multiple Users and Grant

Each Oracle developer has their own set of data definitions, called a schema. This avoids problems with two users creating tables with the same name.

Tables defined in one schema are not visible to other developers until they are explicitly shared using the Grant command.  Shared tables are referenced as schema.table, or user.table.  For example, TABLE chuck.person.

You will want to share tables with your team mates and with user AD, the AOL server.  

A separate Grant statement is required for each table.

You may Grant ALL Privileges or only specific privileges such as SELECT, DELETE, INSERT, UPDATE, or REFERENCES.

Sharing privileges may be Granted to specific users (by name) or to all users (PUBLIC).  Privileges may be Granted with Grant Option which allows the user receiving the privileges to Grant them to others.

The Revoke command can be used to reverse the effect of a Grant.

Examples:

GRANT ALL PRIVILEGES ON TABLE person TO AD;

GRANT SELECT, INSERT, UPDATE ON person TO PUBLIC;

GRANT DELETE ON person TO chuck, geoff WITH GRANT OPTION;

REVOKE DELETE ON TABLE person FROM AD;

Database Programming

Begin/End Transaction

Additional Oracle Resources

For more information see:

© 2000 by Chuck Ehrlich, all rights reserved.  Comments to webmaster.  Updated on April 27, 2000.