Oracle 7.3.2 SQL ----

This document highlights some of the differences between the SQL2 standard and the SQL dialect of Oracle 7.3.2. Please share with us any additional differences that you may find.

---- Basic SQL Features

Oracle does not support AS in FROM clauses, but you can still specify tuple variables without AS:

    from Relation1 u, Relation2 v

On the other hand, Oracle does support AS in SELECT clauses, although the use of AS is completely optional.

The set-difference operator in Oracle is called MINUS rather than EXCEPT. There is no bag-difference operator corresponding to EXCEPT ALL. The bag-intersection operator INTERSECT ALL is not implemented either. However, the bag-union operator UNION ALL is supported.

In Oracle, you must always prefix an attribute reference with the table name whenever this attribute name appears in more than one table in the FROM clause. For example, suppose that we have tables R(A,B) and S(B,C). The following query does not work in Oracle, even though B is unambiguous because R.B is equated to S.B in the WHERE clause:

    select B from R, S where R.B = S.B;    /* ILLEGAL! */

Instead, you should use:

    select R.B from R, S where R.B = S.B;

In Oracle, the negation logical operator (NOT) should go in front of the boolean expression, not in front of the comparison operator. For example, "NOT A = ANY (<subquery>)" is a valid WHERE condition, but "A NOT = ANY (<subquery>)" is not. (Note that "A <> ANY (<subquery>)" is also a valid condition, but means something different.) There is one exception to this rule: You may use either "NOT A IN (<subquery>)" or "A NOT IN (<subquery>)".

Oracle does not support JOIN or OUTER JOIN expressions, either stand-alone or in FROM clauses. Instead, outerjoins are supported by a special operator "(+)" in WHERE clauses. For example, suppose that we have relations R(A, B) and S(C, D). Then,

    R full outer join S on R.B = S.C;
    R left outer join S on R.B = S.C;
    R right outer join S on R.B = S.C;

can be rewritten as:

    select * from R, S where R.B(+) = S.C(+);
    select * from R, S where R.B = S.C(+);
    select * from R, S where R.B(+) = S.C;

respectively. Basically, "(+)" next to a column means that this column can be padded with NULL's in the outerjoin result. You cannot outerjoin the same table to more than one other table in a single SELECT statement.

Oracle does support subqueries in FROM clauses as specified in the SQL2 standard.

Oracle ALTER TABLE statement does not allow you to drop columns, although you can add columns.

---- Data Types

BIT type is not supported. There is a BOOLEAN type in PL/SQL (see Using Oracle PL/SQL for details), but it cannot be used for a database column.

Domains (i.e., type aliases) are not supported.

Dates and times are supported differently in Oracle. For details, please refer to Oracle Dates and Times, available from the class web page.

---- Indexes

To create an index in Oracle, use the standard SQL2 syntax:

    create [unique] index <index_name> on <table_name>(<list_of_attrs>);

In general, <list_of_attrs> could contain more than one attribute. Such an index allows efficient retrieval of tuples with given values for <list_of_attrs>. The optional keyword UNIQUE, if specified, declares <list_of_attrs> to be duplicate-free, which in effect makes <list_of_attrs> a key of <table_name>.

To get rid of an index, again use the standard SQL2 syntax:

    drop index <index_name>;

Oracle automatically creates an index for each UNIQUE or PRIMARY KEY declaration. For example, if you create a table foo as follows:

    create table foo (a int primary key,
                      b varchar(20) unique);

Oracle will automatically create one index on foo.a and another on foo.b. Note that you cannot drop indexes for UNIQUE and PRIMARY KEY attributes. These indexes are dropped automatically when you drop the table or the key constraints (see the section on Constraints).

To find out what indexes you have, use

    select index_name from user_indexes;

USER_INDEXES is another system table just like USER_TABLES. This can become especially helpful if you forget the names of your indexes and therefore cannot drop them. You might also see weird names of the indexes created by Oracle for UNIQUE and PRIMARY KEY attributes, but you will not be able to drop these indexes.

On the Stanford Oracle installation, there are two "tablespaces", one for data, the other for indexes. Every time you create an index (either explicitly with CREATE INDEX or implicitly with a UNIQUE or PRIMARY KEY declaration), you should (on the Stanford Oracle) follow the declaration by TABLESPACE CSINDX. In addition, if you are implicitly creating the index, you need the phrase USING INDEX before TABLESPACE CSINDX. For example:

create index RAindex on R(A) tablespace csindx;

create table foo (a int primary key using index tablespace csindx,
                  b varchar(20) unique using index tablespace csindx);

---- Views

Oracle supports views as specified in SQL2. To find out what views you have created, use:

    select view_name from user_views;

---- Constraints

To find out what constraints are defined in your database, use:

    select constraint_name from user_constraints;

Oracle supports key constraints as specified in SQL2. For each table, there can be only one PRIMARY KEY declaration, but many UNIQUE declarations. Each PRIMARY KEY (or UNIQUE) declaration can have multiple attributes, which means that these attributes together form a primary key (or a key, respectively) of the table.

Oracle supports foreign key constraints, and allows an optional ON DELETE CASCADE after a REFERENCES clause in a table declaration. However, it does not allow ON UPDATE or SET NULL options.

Oracle supports attribute- and tuple-based constraints, but does not allow CHECK conditions to use subqueries. Thus, there is no way for an attribute- or tuple-based constraint to reference anything else besides the attribute or tuple that is being inserted or updated.

Domain constraints are not supported since domains are not supported.

As for general constraints, ASSERTION is not supported. However, a TRIGGER close to the SQL3 trigger is supported. See Using Oracle PL/SQL for details.

In the ALTER TABLE statement, Oracle supports ADDing columns and table constraints, MODIFYing column properties and column constraints, and DROPping constraints. However, you cannot MODIFY an attribute-based CHECK constraint. Here are some examples:

create table bar (x int, y int, constraint XYcheck check (x > y));
alter table bar add (z int, w int);
alter table bar add primary key (x);
alter table bar add constraint YZunique unique (y, z);
alter table bar modify (w varchar(2) default 'AM'
                                     constraint Wnotnull not null);
alter table bar add check (w in ('AM', 'PM'));
alter table bar drop constraint YZunique;
alter table bar drop constraint XYcheck;
alter table bar drop constraint Wnotnull;
alter table bar drop primary key cascade;

Dropping constraints generally requires knowing their names (only in the special case of primary or unique key constraints can you drop them without specifying their names). Thus, it is always a good idea to name all your constraints.

---- Transactions

Oracle supports transactions as defined by the SQL2 standard. A transaction is a sequence of SQL statements that Oracle treats as a single unit of work. As soon as you connect to the database with sqlplus, a transaction begins. Once the transaction begins, every SQL DML (Data Manipulation Language) statement you issue subsequently becomes a part of this transaction. A transaction ends when you disconnect from the database, or when you issue a COMMIT or ROLLBACK command.

COMMIT makes permanent any database changes you made during the current transaction. Until you commit your changes, other users cannot see them. ROLLBACK ends the current transaction and undoes any changes made since the transaction began.

After the current transaction has ended with a COMMIT or ROLLBACK, the first executable SQL statement that you subsequently issue will automatically begin another transaction.

For example, the following SQL commands have the final effect of inserting into table R the tuple (3, 4), but not (1, 2):

insert into R values (1, 2);
rollback;
insert into R values (3, 4);
commit;

Oracle also supports the SAVEPOINT command. The command SAVEPOINT <sp_name> establishes a savepoint named <sp_name> which marks the current point in the processing of a transaction. This savepoint can be used in conjunction with the command ROLLBACK TO <sp_name> to undo parts of a transaction.

For example, the following commands have the final effect of inserting into table R tuples (5, 6) and (11, 12), but not (7, 8) or (9, 10):

insert into R values (5, 6);
savepoint my_sp_1;
insert into R values (7, 8);
savepoint my_sp_2;
insert into R values (9, 10);
rollback to my_sp_1;
insert into R values (11, 12);
commit;

Oracle automatically issues an implicit COMMIT before and after any SQL DDL (Data Definition Language) statement (even if this DDL statement fails) .

---- Timing SQL Commands

Oracle provides a TIMING command for measuring the running time of SQL commands. To activate this feature, type

set timing on;

Then, Oracle will automatically display the elapsed wall-clock time for each SQL command you run subsequently. Note that timing data may be affected by external factors such as system load, etc. To turn off timing, type

set timing off;

You can also create and control multiple timers; type HELP TIMING in sqlplus for details.

This document is written originally for Prof. Jeff Ullman's CS145 class in Autumn, 1997; revised by Jun Yang for Prof. Jennifer Widom's CS145 class in Spring, 1998.

Yurttas/PL/DBL/oracle/F/R/or-sql.html

Contents