CS145 Introduction

Non-SQL2 Features of Oracle 7.3.2

Here are some of the ways in which the class's Oracle implementation differs from the SQL2 standard described in the text. Please share with me and the class any differences, both additions and missing features, that you find.

Basic SQL Features
Indexes
Types
Constraints
Embedded SQL

Basic SQL Features

The set-difference operator in Oracle is MINUS rather than EXCEPT.
An ALTER TABLE statement does not allow you to drop columns, although you can add columns.
Oracle does not support JOIN or OUTER JOIN expressions, either stand-alone or in FROM clauses. However, Oracle does support a select-from-where query defining a relation within the FROM clause. A trivial example:
```
     SELECT price 
     FROM (SELECT bar, price
           FROM Sells
           WHERE beer = 'Bud'
          )
     WHERE bar = 'Joe''s Bar';
```
a bizarre way to get the price Joe charges for Bud.
Domains are not supported.
AS is not supported in FROM clauses. You have to say
```
     FROM Beers b1, Beers b2
```
However, AS may be used in SELECT clauses.

Indexes

In Oracle, you can get an index by the command:
```
     CREATE INDEX <IndexName> ON <RelName>(<Attribute List>);
```
If the attribute list contains more than one attribute, then the index requires values for all the listed attributes to find a tuple. That situation might be helpful if the attributes together form a key, for example. An illustration of the CREATE INDEX command is
```
     CREATE INDEX DrinkerInd ON Drinkers(name);
     CREATE INDEX BarInd ON Bars(name);
```
which creates two indexes, one on the attribute name of relation Drinkers and the other on the attribute name of relation Bars.

To get rid of an index, you can say
```
     DROP INDEX
```
followed by the name of the index. Notice that each index must have a name, even though we only refer to the name if we want to drop the index.

On the Stanford Oracle installation, there are two disks, one for data the other for indexes. Every time you create an index, either explicitly with CREATE INDEX or inplicitly with a PRIMARY KEY or UNIQUE declaration, you should (on the Stanford system) 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 DrinkerInd ON Drinkers(name)
         TABLESPACE csindx;
```
or
```
     CREATE TABLE Foo (
         a INT PRIMARY KEY USING INDEX TABLESPACE csindx,
         b CHAR(20)
     );
```

Data Types

Bit type is not supported.
Oracle supports the DATE type for attributes. However, the format of dates is not according to the SQL2 standard. In fact, the stored format of dates is installation-dependent, and our system uses the format 'dd-mon-yy'. That is, two digits of day, a hyphen, a 3-character month abbreviation (e.g., nov or apr), another hyphen, and a two-digit year (yes, folks; we've got the ``year-2000 problem''). Character strings in the proper format can be stored in date-valued attributes or compared with dates. For example:
```
     CREATE TABLE Days(d DATE);

     INSERT INTO Days VALUES('06-may-97');

     SELECT d FROM Days WHERE d > '01-jan-90';
```
We can also convert other reasonable formats into dates with the Oracle/SQL TO_DATE function. This function takes a character string in any format and another string that ``decodes'' the first, using the same convention as for date masks described in the material on the loader (y or Y begins a year field, etc.). For example:
```
     INSERT INTO Days VALUES(TO_DATE('1997/05/06', 'yyyy/mm/dd'));
```
There is no analog of the TIME SQL2 type. However, it is possible to use a (nondefault) format for dates that includes hours, minutes, and seconds, so a date could play the role of a time. See Kristian Widjaja's guide to dates and times for more details.
Types in PL/SQL are tricky. First, you cannot parametrize certain SQL types like CHAR(50), although either CHAR or VARCHAR(50) are OK. However, comparisons between PL/SQL variables and data in your database may not go the way you expect. To be safe, declare the type of a PL/SQL variable that will be compared with data from your database with the %TYPE attribute as described in the PL/SQL Guide.

Constraints

Oracle allows ON DELETE CASCADE after a REFERENCES clause in a table declaration, but does not allow an ON UPDATE clause there, nor does it allow SET NULL.
CHECK conditions are not permitted to use subqueries. Thus, there is no way to have an attribute- or tuple-based check that involves anything except the attribute or tuple that is inserted or changed.
PRIMARY KEY and UNIQUE each result in the creation of an index on the attribute(s) so declared. As in SQL2, there can be only one primary key, but many unique attributes.
Assertions are not supported. However, a trigger close to the SQL3 standard is supported. There are several differences between Oracle and SQL triggers. Some of the important ones:

a)
INSTEAD OF is not supported.
b)
You cannot modify the same relation whose modification is the event triggering the trigger.
c)
You cannot even modify a relation connected to the triggering relation by another constraint such as a foreign-key constraint.
d)
The action of the trigger is a PL/SQL statement rather than a sequence of SQL statements.
Oracle allows ALTER TABLE t followed by either ADD (e.g., to add a column) or MODIFY followed by a column name (e.g., to add a default value a new type, or a constraint on the column. However, it does not appear that our implementation actually supports the addition of column constraints such as primary or foreign keys. On the other hand, it is possible to add primary key, foreign key, or check constraints as elements of the table declaration. The only surprise is that it is necessary to give the constraint a name. For example, I could make name the key for Beers by:
```
     ALTER TABLE Beers ADD CONSTRAINT c PRIMARY KEY(name);
```

Embedded SQL

The use of EXEC SQL BEGIN/END DECLARE SECTION is voluntary in Oracle's SQL embedded in C. However, if you embed SQL in C++, the declare-section markers are required.
Oracle doesn't allow FROM after FETCH in a cursor-fetch statement.

There are also a number of problems that come not from Oracle's version of embedded SQL per se, but that are a function of how their system structure relates to the way people have set up their own environments. Here are some of the most common problems we've seen: