Database Reference
In-Depth Information
SQL99 defines only two levels of conformance: Core and Enhanced. It attempted to go far beyond traditional
SQL and introduced object relational constructs (arrays, collections, etc.). It covered a SQL MM (multimedia) type,
object-relational types, and so on. No vendors are certifying databases to be SQL99 Core or Enhanced “compliant”
and, in fact, I know of no vendor who is even claiming his product is fully compliant with either level
of conformance.
You should not be afraid to make use of vendor-specific features—after all, you are paying a lot of money for
them. Every database has its own bag of tricks, and you can always find a way to perform a given operation in each
database. Use what is best for your current database, and reimplement components as you go to other databases. Use
good programming techniques to isolate yourself from these changes. The same techniques are employed by people
writing OS-portable applications—such as the Oracle kernel developers.
Make Sure You Can Adapt
The goal is to fully use the facilities available to you, but ensure you can change the implementation on a case-by-case
basis. As an analogy, Oracle is a portable application. It runs on many operating systems. On Windows, however, it
runs the Windows way: using threads and other Windows-specific facilities. On UNIX/Linux, in contrast, Oracle runs
as a multiprocess server, using individual processes to do what threads do on Windows—that's the UNIX/Linux way.
The “core Oracle” functionality is available on both platforms but it is implemented in very different ways under the
covers. Your database applications that must function on multiple databases will be the same.
For example, a common function of many database applications is the generation of a unique key for each
row. When you insert the row, the system should automatically generate a key for you. Oracle has implemented
the database object called a
SEQUENCE
for this,
SYS_GUID()
is another function that provides for unique keys as well.
Informix has a
SERIAL
data type. Sybase and SQL Server have an
IDENTITY
type. Each database has a way to do this.
However, the methods are different, both in how you do it, and the possible outcomes. So, to the knowledgeable
developer, there are two paths that can be pursued:
•
Develop a totally database-independent method of generating a unique key.
•
Accommodate the different implementations and use different techniques when
implementing keys in each database.
■
oracle now also has an
IDENTITY
type, as of oracle 12
c
. under the covers it creates a sequence and defaults
your column to that value—making it work very much like the SQl Server
IDENTITY
type.
Note
The theoretical advantage of the first approach is that to move from database to database you need not change
anything. I call it a “theoretical” advantage because the downside of this implementation is so huge that it makes this
solution totally infeasible. What you'd have to do to develop a totally database-independent process is to create a table
such as this:
EODA@ORA12CR1> create table id_table
2 ( id_name varchar2(30) primary key,
3 id_value number );
Table created.
EODA@ORA12CR1> insert into id_table values ( 'MY_KEY', 0 );
1 row created.
EODA@ORA12CR1> commit;
Commit complete.
