Oracle 9i SQL versus Standard SQL
This document highlights some of the differences between the SQL standard
and the SQL dialect of Oracle 9i. 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
rather than EXCEPT. There is no bag-difference operator corresponding
to EXCEPT ALL. The bag-intersection operator
is not implemented either. However, the bag-union operator UNION ALLis
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
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>)".
In Oracle, an aliased relation is invisible to a subquery's FROM clause.
SELECT * FROM R S WHERE EXISTS (SELECT * FROM S)
is rejected because Oracle does not find S in the subquery, but
SELECT * FROM R S WHERE EXISTS (SELECT * FROM R WHERE R.a = S.a)
In Oracle, a query that includes
can cause the database connection to break with an error
(ORA-03113: end-of-file on communication channel),
even if the two GROUP BY clauses are unrelated.
- a subquery in the FROM clause, using GROUP BY; and
- a subquery in the WHERE clause, using GROUP BY
In Oracle, comments may be introduced in two ways:
With /*...*/, as in C.
With a line that begins with two dashes --.
-- This is a comment
SELECT * /* and so is this */
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
Dates and times are supported differently in
Oracle. For details, please refer to Oracle
Dates and Times, available from the class web page.
CHAR(n) can be of length up to 2000.
can be of length up to 4000. However, special treatment is required when
bulk-loading strings longer than 255 characters. See The
Oracle Bulk Loader for details.
To create an index in Oracle, use the syntax:
create [unique] index <index_name> on <table_name>(<attr_list>);
In general, <attr_list> could contain more than one attribute.
Such an index allows efficient retrieval of tuples with given values for
The optional keyword UNIQUE, if specified, declares <attr_list>
to be duplicate-free, which in effect makes <attr_list> a key
To get rid of an index, use:
drop index <index_name>;
Oracle automatically creates an index for each
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 INDX.
In addition, if you are implicitly creating the index, you need the phrase
INDEX before TABLESPACE INDX. For example:
create index RAindex on R(A) tablespace indx;
create table foo (a int primary key using index tablespace indx,
b varchar(20) unique using index tablespace indx);
Oracle supports views as specified in SQL. To
find out what views you have created, use:
select view_name from user_views;
To find out what constraints are defined in
your database, use:
select constraint_name from user_constraints;
Oracle supports key constraints as specified
in SQL. 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
form a primary key (or a key, respectively) of the table.
Oracle supports referential integrity (foreign
key) constraints, and allows an optional ON DELETE CASCADE or
DELETE SET NULL after a REFERENCES clause in a table declaration.
However, it does not allow ON UPDATE options.
Note that when declaring a foreign key constraint at the end of a table
declaration it is always necessary to put the list of referencing attributes
create table foo (...
foreign key (<attr_list>) references (<attr_list>));
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 SQL trigger
supported. See Constraints
and Triggers 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
Triggers in Oracle differ in several ways from the SQL standard. Details
are in a separate section Constraints
Oracle supports transactions as defined by the
SQL 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
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);
insert into R values (3, 4);
During interactive usage with sqlplus, Oracle also supports an AUTOCOMMIT
option. With this option set to ON each individual SQL statement is treated
as a transaction an will be automatically commited right after it is executed.
A user can change the AUTOCOMMIT option by typing
SET AUTOCOMMIT ON
SET AUTOCOMMIT OFF
whereas by typing
a user can see the current setting for the option (including other ones).
The same rules for designating the end of a transaction (COMMIT/ROLLBACK)
and the beginning of it (which is implied and starts just after the last
COMMIT/ROLLBACK) apply to programmers interacting with Oracle using Pro*C
or JDBC. Note though that Pro*C doesn't support the AUTOCOMMIT option whereas
JDBC does and it has a default AUTOCOMMMIT option set to ON. Thus a programmer
needs to execute COMMIT/ROLLBACK statements in Pro*C whereas in JDBC a
user can make use of the AUTOCOMMIT and never specify explicitly where
a transaction starts or ends. For more details, see the respective sections:
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
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,
insert into R values (5, 6);
insert into R values (7, 8);
insert into R values (9, 10);
rollback to my_sp_1;
insert into R values (11, 12);
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,
set timing off;
You can also create and control multiple timers; type HELP TIMING
in sqlplus for details.
Here are a few of the most common distinctions between Oracle's PL/SQL
and the SQL standard PSM (persistent, stored modules):
PL/SQL Vs. PSM
In nested if-statements, PL/SQL uses ELSIF,
while PSM calls for ELSEIF. Both are used where we would find
IF in C, for example.
To leave a loop, PL/SQL uses EXIT,
or EXIT WHEN(...) to exit conditionally. PSM uses LEAVE,
and puts the leave-statement in an if-statement to exit conditionally.
Assignments in PL/SQL are with the :=
operator, as A := B. The corresponding PSM syntax is SET A
There is a great deal of difference between the Oracle and SQL-standard
approaches to user-defined types. You should look at the on-line guide
Object Relational Features of Oracle
for details and examples of the Oracle approach. However, here are a few
small places where the approaches almost coincide but differ in small ways:
When defining a user-defined type, Oracle uses
TYPE ... AS OBJECT, while the word ``OBJECT'' is not used
in the standard.
When accessing an attribute a of a relation
that is defined to have a user-defined type, the ``dot'' notation works
in Oracle, as R.a. In the standard, a must be thought of
as a method of the same name, and the syntax is R.a().
To define (not declare) a method, Oracle has
you write the code for the method in a CREATE TYPE BODY statement
for the type to which the method belongs. The standard uses a CREATE
METHOD statement similar to the way functions are defined in PL/SQL
This document was 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; further revisions by
Jun Yang, Spring 1999; further revisions by Jennifer Widom, Spring 2000;
minor revisions by Nathan Folkert, Spring 2001; Henry Hsieh, Autumn 2001;
and Antonios Hondroulis, Spring 2002; further revisions by Wang Lam for
Prof. Jennifer Widom's CS145 class in Spring 2003.