DEVELOPING SUCCESSFUL ORACLE APPLICATIONS in Font

Creator Data Matrix ECC200 in Font DEVELOPING SUCCESSFUL ORACLE APPLICATIONS

etc.). It covers a SQL MM (multimedia) type, object-relational types, and so on. No vendor is certifying databases to be SQL99 Core or Enhanced compliant and, in fact, I know of no vendor who is even claiming that their product is fully compliant with either level of conformance. In addition to SQL syntactic differences, implementation differences, and differences in performance of the same query in different databases, there are the issues of concurrency controls, isolation levels, query consistency, and so on. We ll cover these items in some detail in 7 and see how their differences may affect you. SQL92/SQL99 attempts to give a straightforward definition of how a transaction should work and how isolation levels are to be implemented, but in the end, you ll get different results from different databases. It is all due to the implementation. In one database, an application will deadlock and block all over the place. In another database, the same exact application will not do any of these things it will run smoothly. In one database, the fact that you did block (physically serialize) was used to your advantage, and when you go to deploy on another database, and it does not block, you get the wrong answer. Picking an application up and dropping it on another database takes a lot of hard work and effort, even if you followed the standard 100 percent. The bottom line is that 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 we can always find a way to perform the 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. I call this defensive programming.

The same defensive programming techniques that I advocate for building truly portable database applications are, in essence the same as those employed by people writing OS-portable applications. The goal is to fully utilize 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. However, on Windows it runs in the Windows way: using threads and other Windows-specific facilities. On UNIX, Oracle runs as a multiprocess server, using individual processes to do what threads did on Windows that is the UNIX 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. Informix has a SERIAL datatype. 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, for 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.

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 would have to do to develop a totally database-independent process is to create a table such as ops$tkyte@ORA10G> create table id_table 2 ( id_name varchar2(30) primary key, 3 id_value number ); Table created. ops$tkyte@ORA10G> insert into id_table values ( 'MY_KEY', 0 ); 1 row created. ops$tkyte@ORA10G> commit; Commit complete. Then, in order to get a new key, you would have to execute the following code: ops$tkyte@ORA10G> update id_table 2 set id_value = id_value+1 3 where id_name = 'MY_KEY'; 1 row updated. ops$tkyte@ORA10G> select id_value 2 from id_table 3 where id_name = 'MY_KEY'; ID_VALUE ---------1 Looks simple enough, but the outcomes (notice plural) are as follows: Only one user at a time may process a transaction row. You need to update that row to increment a counter, and this will cause your program to serialize on that operation. At best, one person at a time will generate a new value for this key. In Oracle (and the behavior might be different in other databases), all but the first user to attempt to concurrently perform this operation would receive the error ORA-08177: can t serialize access for this transaction in the SERIALIZABLE isolation level. For example, using a serializable transaction (which is more common in the J2EE environment, where many tools automatically use this as the default mode of isolation, often unbeknownst to the developers), you would observe the following behavior. Notice that the SQL prompt (using the SET SQLPROMPT SQL*Plus command) contains information about which session is active in this example: