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 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 ALLis 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>)".
In Oracle, an aliased relation is invisible to a subquery's FROM clause. For example,
is rejected because Oracle does not find S in the subquery, but
is accepted.
In Oracle, a query that includes
  1. a subquery in the FROM clause, using GROUP BY; and
  2. a subquery in the WHERE clause, using GROUP BY
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.


In Oracle, comments may be introduced in two ways:
  1. With /*...*/, as in C.
  2. With a line that begins with two dashes --.
-- This is a comment
SELECT * /* and so is this */

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.

CHAR(n) can be of length up to 2000. VARCHAR(n) 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 <attr_list>. The optional keyword UNIQUE, if specified, declares <attr_list> to be duplicate-free, which in effect makes <attr_list> a key of <table_name>.

To get rid of an index, use:

    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 INDX. In addition, if you are implicitly creating the index, you need the phrase USING 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 together 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 ON 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 in parentheses:

  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 is 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 constraints.


Triggers in Oracle differ in several ways from the SQL standard. Details are in a separate section Constraints and Triggers.


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 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);
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
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: Pro*C, JDBC.

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);

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.


Here are a few of the most common distinctions between Oracle's PL/SQL and the SQL standard PSM (persistent, stored modules):

In nested if-statements, PL/SQL uses ELSIF, while PSM calls for ELSEIF. Both are used where we would find ELSE 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 = B.

Object-Relational Features

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 CREATE TYPE ... AS OBJECT, while the word ``OBJECT'' is not used in the standard.

When accessing an attribute a of a relation R 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 or SQL/PSM.

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.