Weblog for the AMIS Technology corner
Archive for June, 2006
Mastering XML DB – Unexpected side effect of updateXML
Jun 30th
When trying to create anonymous data, I encountered an unexpected side effect of the updateXML statement while updating XML documents in an Oracle 10gR2 (10.2.0.1.0) EE database.
I got a small test environment in which a table (HGO010_DETAM) resides that contains approximately 500Mb of XML documents. The real production environment is much bigger, think in 100th of Gigabytes per table and an average of 512 Kb (up to 6 Mb) per document. The web application that makes use of this data is document driven so these documents were stored in an XMLType column based on CLOB storage. The table also contains an extra ID column that was added for reference purposes.
The table was created as follows:
Read the rest of this entry »
Rounding numbers given in string representation while controlling their number of significant digits
Jun 30th
For a reporting application demanding numeric accuracy we needed a functionality for rounding (positive) numbers given in string representation while controlling their number of significant digits. I found the standard oracle functionality somewhat lacking in this respect since it doesn’t necessarily maintain the number of significant digits, at least when using the standard chain: round(to_number(..)).
The following PL/SQL is our own string rounding function Read the rest of this entry »
Oracle db feature matrix
Jun 30th
Interested in the features and options of the different editions (standard, express and enterprise) of the Oracle database? This document lists them all in a handy matrix format.
ODTUG 2006 Conference Handouts/Presentations available
Jun 30th
Last night, the ODTUG 2006 Conference Committee announced that all handouts and presentations from this year’s (well, in fact that is last week’s) conference are available for download from the ODTUG website at http://www.odtug.com. Until August 1st, all material can be downloaded by the general public. After that date, you have to be a member of ODTUG to access the conference handouts.
For downloading, go to the ODTUG website and use the search box in the upper left hand corner. You can search on "conference", which also returns handouts from previous conferences, or on author or title if you know what they are. At ODTUG 2006 Kaleidoscope you can find an overview of all presenters and presentations; you can use that information to search in order to download the material.
An Oracle10g PL/SQL implementation of the AWK split() built-in
Jun 29th
With the regular expression functionality in Orcale database 10g some handy build-ins of the UNIX pattern matching language AWK are easily implemented. I myself use AWK split() function quite often in AWK scripts. AWK split() splits a given string into chunks separated by substrings which are described by a regular expression parameter. The substrings are returned in hash table (an associative array like datatype). The function returns the number of chunks collected.
A typical split call could be something like t = split(‘oracle123@mysql’,tab,’^a-z +’) putting ‘oracle’ and ‘mysql’ in the hash table tab and returning t = 2. Read the rest of this entry »
Oracle Data Mining – from SQL and PL/SQL
Jun 29th
During one of the sessions on last week’s ODTUG 2006 conference, I saw a very interesting demo by Keith Laker. He discussed the problem of having several dozens of dimensions or descriptors for a particular fact and utter uselessness of a report with more than 40 or 50 qualifiers. Like an overview of salesrecords with per customer information such as name, birthdate, gender, zipcode, number of pets, political affiliation, job, health history, favorite author et. Each one of these pieces of information may be meaningful, but when all presented in a report, it becomes impossible to extract meaningful information. So he suggested an approach for determining which of all these qualifiers are the most useful ones and only including those in the report.
Read the rest of this entry »
Ohhh, thy mighty Oracle…
Jun 29th
Ohhh, thy mighty Oracle. I had completly forgotten about him and then we where re-united. We met years and years and years ago…and I coud always ask him the most difficult questions. His answers are wise and still to this day he puts some effort into those important questions.
Ask him yourself on [ http://cgi.cs.indiana.edu/~oracle/index.cgi ]or find some of his great wisdom on [ http://cgi.cs.indiana.edu/~oracle/bestofs.cgi ]
ADF security (ODTUG 2006)
Jun 28th
Being a major important technology for Oracle, many presentations on this year’s ODTUG conference covered ADF. The showcase, the demonstration of the forthcoming AJAX enabled ADF Faces, has been described in detail by Lucas (AMIS) and Wilfred. But just as worthwhile were the two presentations on ADF security.
The first one was by Peter Koletzky titled "JAZN – implementing the superstition in JDeveloper". He started off with a general introduction to security and mentioned some of the myths that are, unfortunately, stille quite common like "Users always do what you expect them to do" and "Only those who should access your data do access your data". Although, this raised some laughter, we clearly understood the message: pay attention to security and do it well.
Unittesting PL/SQL (ODTUG 2006)
Jun 27th
For me, one of the highlights of this year’s ODTUG conference was the presentation of Steven Feuerstein about PL/SQL unittesting, "Six simple steps to unit testing happiness".
While unittesting is (more or less) a standard practice in the java world, it is less so in the database world and even more surprising, in many cases the usefullness is still the subject of hot and fierce debates. Feuerstein seems to be the lone crusader in advocating and practicing pl/sql unittesting. This was again supported by the fact that within the crowd of more than 100 attendees, only a few of them used a formal, automatic, way of testing PL/SQL code. Read the rest of this entry »
Mogens Norgaard: No Cary, we’re still not tuning the right stuff. (ODTUG 2006 )
Jun 27th
Right after the Oracle Quiz Lucas and I presented last week on ODTUG, I went to Mogens Norgaard’s presentation. Here is a short report from that session, you may have read the announcement on
Dimitri Gielis’ Blog.
Besides talking about his firm, Miracle AS- they also brew beer, he gave an overview of "Tuning through the ages" and proposed a new way of tuning as well. Read the rest of this entry »
