In this blog posting I'll write about a more exotic feature of the Oracle database. You can
let the kernel rewrite SQL queries for you. This is based on the materialized view
technology: When you have a materialized view for a specific (complex) SQL query and the
optimizer "knows" about this, it will rewrite your query to use the materialized view instead
of the original tables. In practice, it's not that easy - there are some requirements
which the materialized views must meet. But that is another topic - I'll pick a particular
feature here: the DBMS_ADVANCED_REWRITE
(Documentation)
package to create your own rewrites - even
without materialized views.

With this package you can instruct the optimizer to rewrite one SQL query to another. By default
no database user can execute this package - and even the public synonym is missing. So in order
to play with it you first need to grant the EXECUTE privilege to your database user (here: SCOTT).
In addition to this, SCOTT needs the CREATE MATERIALIZED VIEW privilege.

The parameters SOURCE_STMT and DESTINATION_STMT determine which SQL query
is being rewritten to which other SQL statement. This is a declaration to the Oracle optimizer
that those two SQL queries are equivalent and that the DESTINATION_STMT is more favorable.
If the VALIDATE argument is set to TRUE the database will check whether the
two SQL queries really return the same results. If set to false, the database
will not perform that check. The last argument REWRITE_MODE determines when to use
the query equivalence. The example here (TEXT_MATCH) requires the query text to be
identical to the declaration. The equivalences declared with DBMS_ADVANCED_REWRITE
have high priority - the optimizer will use them even if it considers the original query
as more effective. Let's test the declaration.

That seems not to work - we have forgotten to set the session parameter QUERY_REWRITE_INTEGRITY,
which is set to ENFORCED by default. That means the database uses query rewrite only if
the optimizer is sure that the same data is being returned. The parameter must be
set to TRUSTED or STALE_TOLERATED
in order to use the declared equivalences. STALE_TOLERATED means that query rewrite even
may return stale or incorrect data.

You see that the query has been rewritten internally. The table has not been changed;
there is also no view involved. It's obvious that this is a very powerful feature which
can also lead to a lot of confusion - so use it with care. For the above example,
Virtual Private Database or a View would, of course, have been the better choice.

But DBMS_ADVANCED_REWRITE might be an approach to tackle performance problems. Think
about a complex SQL query (Joins, Computations) which you like to have precomputed, but
without having all the setup required for materialized views. The following example
does a query rewrite from a query with a join to a simple select on a single table.

If the involved tables have uncommitted changes in your session, the query rewrite will not work.

When you declare a Query Rewrite Equivalence with a join, use the "old" join syntax - the ANSI syntax
will not work. But you can use the ANSI syntax while querying - the previously declared equivalende with
the old syntax will be used. Of course, you need to set REWRITE_MODE to another value then
TEXT_MATCH - GENERAL would be an alternative.

These restrictions make it obvious that the feature has been designed for Data Warehouse usage. The
usage scenarios are limited - but in particular cases it might be a solution to tackle SQL performance
problems.

Do you frequently create object types in your database schema. You will do so if you use pipelined table functions since you need the types to describe the table returned by the function. Oracle 11.2 brings a tiny, but useful enhancement when it's about changing the type definitions. In previous versions you just can't change an object type's definition - you have to drop and recreate it. And when there are dependent types you have to drop all those types first. The new CREATE OR REPLACE TYPE ... FORCE syntax in 11.2 allows to change an object types' definition without looking at the dependent types. Changes are therefore much more easy to implement.

The database JVM is also one of my "hobbies" - so I very much liked this approach and thought how to extend this to also support unpacking existing zip archives. The result was a document in the german APEX-Community.

Shortly after publishing this I got feedback from a german customer who had umlauts and other non ASCII characters as part of filenames within the ZIP archive. This lead to an java error and the whole approach did not work anymore. The reason for this is a very old bug in the java system library. Java expects filenames in the zip archive to be encoded in Unicode - but the popular Winzip utility uses a local codepage - "codepage 850" in germany. So there is a bit more work to be done ...

Download the open source implementation of the java.util.zip system package: "Jazzlib". This is provided as source code - so it is easy to extend the classes to suppert different character sets. You can load the file directly into your database schema (SCOTT) using the loadjava utility.

Now the (extended) ZIP implementation Jazzlib has been loaded into the database schema. But the API which has been designed to match the java.util.zip API cannot be mapped to a corresponding PL/SQL package, since the java types have no SQL pendants. So we need more java code: the following java class just creates java wrapper methods which can be easily mapped to PL/SQL.

Now the PL/SQL part will be done. First we need data structures for dealing with ZIP archies. The following SQL script creates a type ZIP_ENTRY_T (which represents a file within a ZIP archive) and a collection ZIP_ENTRY_CT which is for all the files in a ZIP archive.

The LIST table function always returns SQL NULL for the CONTENT attribute. Populating it with
the actual unpacked file would lead to too much open temporary lobs. If you want to unpack the
archive and insert into unpacked files into another table (say: MY_UNPACKED_FILES) this would be done
with the following Code ...

The function to create a ZIP archive is pretty much the same as in Joels original posting. It has been
overloaded. The query can be either SQL text in a VARCHAR2 variable or a REF CURSOR argument. In both
cases the query must return two columns: the first one must be the filename, the second one must
be the file content as a BLOB. The function will return the ZIP archive as a BLOB.

DATE and TIMESTAMP and how to deal with these was already the topic of some blog postings. But today I'm talking again about this - and I have a particular issue: Adding or substracting months from a given date. The most elegant syntax seems to be the INTERVAL function ...

What is that? Now, Oracle substracts a month from "October" and keeps the day (31st). The result is 31st of September - which does not exist, of course. This behaviour is odd - but it is as it is. So we have to figure how to deal with it. And there are two options: The first one would be to substract the month from the beginning of the next month. So we first "navigate" to November 1st, substract a month and a day. This is a bit of playing with ROUND and TRUNC functions for dates. But it is a quite cumbersome approach.

So when it is about adding or substracting months from a given date, the ADD_MONTHS function is the better approach since it also handles the described issue. And this applies particluarly when the given date is the end of a month, say: all days starting with the 28th. Yes, also the 29th and the 30th might be a problem, when your "target month" after adding or substracting is February.

I'll talk about DBMS_APPLICATION_INFO. With this package you can today - while developing your application - help the database administrator when it is about future debugging and tracing. This is one of the most important PL/SQL packages for application developers - for all developers regardless the programming language they use.

DBMS_APPLICATION_INFO allows you to set "markers" in the database session. Oracle provides three markers: CLIENT_INFO, MODULE and ACTION. You are allowed to set whatever value you want. The values you set are being recorded in all dynamic performance views the DBA uses (V$SQL, V$SQLAREA, V$SESSION and others. A PL/SQL example illustrates this.

The DBA can directly see what's going on in this very database session. This is particularly important when you have a three-tier-architecture with middleware involved. In those cases you have a connection pool where multiple database sessions are being opened with the same database user. Without DBMS_APPLICATION_USER the DBA cannot see which database session is doing which operation. DBMS_APPLICATION_INFO makes it transparent - and Java developers can (and should) also use it.

The same applies to all other programming languages. All Oracle tools use the Information provided with DBMS_APPLICATION_INFO. The following is a screenshot of Oracle's Enterprise Manager - the tool which many DBAs use the maintain the database. If they search for particular SQL statements Enterprise Manager allows to filter for ACTION or MODULE or both.

The interesting bit is that this kind of query could also be used as a replacement for table functions - the recursion allows to create new, "table-independent" rows in a query result. And here is an example: I've done the table function for the "mortgage plan" as a recursive query. This query looks like this ...

As an advantage you don't need to create object types (which you need for pipelined table functions) - so this query could be executed with just the CREATE SESSION privilege. But (currently) this query is significantly slower than the pipelined function. And the result of all this ...? Using the new recursive WITH clause you can do much more than just querying parent-child relationships.

In Oracle 11.2 there is a new feature for the "classic" import utility - and this is IMHO a very helpful one: DATA_ONLY. When specified as DATA_ONLY=Y the import utility processes only the data of an import file and skips all the metadata. That means, no tables, types, PL/SQL objects or other database objects are being created. When a table for the data is not present, import raises an error message. Calling import with help=yes shows the new parameter.

In the past we worked with IGNORE=Y when the tables were already present. But import then recreated all PL/SQL objects then. So DATA_ONLY is much better suited in cases where all the metadata is already present and we just want to import table rows. In generel I'd recommand to use Data Pump instead of the "classic" export and import utilities - with Data Pump you can adjust the export and import process in a much more fine granular manner. But sometimes we just have an "old" dumpfile and in those cases this new option might be helpful ...

Java in the database clould be a solution (again). This is because
in a java stored procedure one can open a plain JDBC connection to the remote database, execute a SQL query selecting the LOB column and retrieve the LOB bytes with java streaming. Those bytes could then returned to the SQL layer as a LOB object.

Now we have the package REMOTE_LOB which allows you to read
BLOB oder CLOB objects from remote databases. The only disadvantage is that you need to pass
username, password and the connect string to the java stored procedures. The information contained
in an existing database link cannot be used since the remote users' password cannot be read from
the dictionary tables. Before testing you need appropriate java privileges - without the JDBC connection will fail with the error message below.

ERROR in line 1:
ORA-29532: "Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.net.SocketPermission
sccloud033 resolve) has not been granted to SCOTT. The PL/SQL to grant this is
dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission',
'sccloud033', 'resolve' )
ORA-06512: in "SCOTT.REMOTE_LOB", line 2
ORA-06512: in line 2

CLOBs also work. The functions GET_BLOB_SIMPLE and GET_CLOB_SIMPLE are
"convenience functions" which do the three steps (prepare, get, close) at once. The basic
principle of these functions could also be used to retrieve LOB objects from non-Oracle databases -
a 3rd-party JDBC driver can easily be loaded into the database JVM. More about this later ... for
now have fun with this package ...

In Oracle Enterprise Manager you can view and manage server-generated alerts - specific events, (you can define additional event and their metric yourself) trigger alerts. After logging into Database Control you can view see the current alerts on the homepage. Note: To use this tip you need to license the Oracle Enterprise Manager Diagnostic Pack sind the view we're using here is "protected" by that license.

But you need to login into Enterprise Manager in order to see the alerts. You can register an email address within Enterprise Manager in order to be notified by mail - but for message exchange there is another very popular format: RSS. This posting shows how to provide an RSS feed containing the current Enterprise Manager alerts.

The alerts are being managed in the EM Repository, which resides in the SYSMAN schema of (Database Control) the Oracle Database or (Grid Control) the repository database. This posting is based on Database Control - it should work for Grid Control also but you might need apply changes to the posted code. Furthermore I've used an 11g database here since I need the PL/SQL Embedded Gateway as the RSS feed's webserver. In 10g you would need to use the Apache Webserver with mod_plsql (as with APEX) - which is not explained here.

Using the database it is easy to build an RSS feed containing the alerts. Since RSS is an XML format it could be generated using the XML functions of the Oracle database. There was a blog posting about those in the past. After some research I found the database view containing the alert information: MGMT$ALERT_CURRENT in the schema SYSMAN. It is (as you can see) documented but cannot be selected by a "plain" database user.

So I need to say something about the securiry implications: An RSS feed is visible to everyone who knows its URL - Enterprise Manager alerts are only visible to DBA's. That's the reason why there is no public database view. I'd like to show how to provide the RSS feed anyway: You can (as we'll see) implement it in a secure manner and some of you might find it useful.

Here is the PL/SQL procedure which generates the RSS feed XML containing the contents of SYSMAN.MGMT$ALET_CURRENT. It's based on the PL/SQL Web Toolkit (OWA_UTIL, HTP, HTF etc.). For security reasons I'd recommend to use an own database schema for that procedure (ALERTRSS). Adjust the the content of the PL/SQL variable v_em_url to your EM installation before running the script.

To call this procedure from the browser we need a webserver. There
are basically two methods for this. In Oracle11g we have the Embedded PL/SQL Gateway, in Oracle10g we
need to use Apache Webserver with mod_plsql - as described above. The Embedded Gateway uses the
Oracle Listener as the HTTP server; I'll now describe how to configure it.

Firstly you need to enable the HTTP protocol server (if not done already). There is a SQL script for that: catxdbdbca.sql in $ORACLE_HOME/rdbms/admin. You can choose the TCP/IP port number. Use lsnrctl status to check whether the listener has opened the HTTP port. After that you
can configure a new Database Access Descriptor (DAD) in order to access the procedure. The following
script does this. Finally unlock the database user ANONYMOUS which makes the procedure accessible without
any login - that's the reason why the user ALERTRSS does not need the CREATE SESSION privilege. This setup makes only the procedure ALERTRSS.EM_ALERT_RSS accessible - and only for browser requests.

In Firefox it should look as follows - clicking on a link
branches directly to the alert's related EM database control page. Since RSS is a standardized
format there are many newsreader applications available. The Oracle database alerts could then
be displayed among other news (or alerts from other databases) - the DBA has a consistent view.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation or Oracle Germany. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.