More About Oracle's History
The origin of the DUAL table, a CASE-sensitive question, on becoming
an Oracle Magazine Peer, and loading data from external tables.
The History of Dual
In the November/December issue of Oracle Magazine you recount the history of the company, so I'm wondering if you can tell me how the DUAL table got its name. People I've asked, including seasoned Oracle gurus, have not been able to answer this.
I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one.
Chuck Weiss, Oracle
Making Your Case
It has always mystified me the Oracle does not actively market its computer aided systems engineering (CASE) tools. This was brought home clearly in your 25th anniversary issue (November/December, 2001). Nowhere was it ever mentioned that Oracle has always been a pioneer in the development of tools to support the development of systems, not just the operation of them. It's a shame that Oracle doesn't seem to recognize this. Based on Oracle's publicity, you wouldn't know that Designer existed.
How do you pick the people highlighted in the Peer-to-Peer section of Oracle Magazine?
We select our Peers from a variety of sources: from the pool of potential authors who've submitted white papers and sample articles for publication consideration, from the pool of DBAs who submit code tips to Oracle Magazine online, and from the pool of DBAs and developers we come in contact with during marketing activities.
Regarding the article on data loading from external tables that appeared in the September/October 2001 issue, we put together a simple test case for external tables, using the following INSERT to load our test data:
INSERT INTO dest
SELECT * FROM ext_file;
If there is violation of a check constraint, such as a NOT NULL constraint, the above statement fails, and none of the good data in the file is loaded. With SQL*Loader, I could skip invalid records and continue with the load. Is there a way to do the same when using external tables?
By definition, SQL statements aren't allowed to partially succeed, and that rule currently applies when using SQL to read from an external table. Thus, a record that causes any type of database error will cause the entire load to fail. One workaround is to use a WHEN clause to exclude NULLs when creating your external table. For example:
CREATE EXTERNAL TABLE ext_file
ACCESS PARAMETERS (
RECORDS FIXED 20
LOAD WHEN (16:19) NOTEQUAL ' '
Another way would be to write a table function to exclude records with NULLs. A table function would be the best approach for the scenario in my earlier article.
SendMail to the Editor:
Send your rants, raves, and requestsabout what you read in Oracle Magazine and on Oracle Publishing Onlineto email@example.com. Or click on the Write the Editors button on our Web site, www.oracle.com/oramag/. Letters may be edited for length and clarity and may be published in any medium. We regret that due to the volume
of correspondence we cannot reply to every
letter. We consider any communications we receive publishable.