monline

I strongly discourage everyone from running Java in the JVM of Oracle 9i databases. I had two serious issues with it in a production environment (btw. fully patched to the latest patchset) and would never suggest to use Java in an Oracle PL/SQL session ever again.

Under some circumstances a call to dbms_utility.format_call_stack() might return an empty string (NULL).

The documentation of Oracle 9iR2 says that the dbms_session.free_unused_user_memory() procedure can be used to free up memory that is not in use. They even have an example for index-by tables (in 9i they are called associative arrays) and it works just as expected. However the memory is only released if the table being used was indexed by a numeric datatype! For tables indexed by VARCHAR2 it will not free the memory. Try it yourself if you've doubts about it. Unfortunately I'm using VARCHAR-indexed PL/SQL tables quite heavily in my application (for caching table data) and this bug hit me pretty seriously. I've to rewrite a lot of code now so my app does not abort due to ORA-04030 (out of process memory) exceptions.

If you use the wpg_docload.download_file() procedure to download a BLOB to the browser, then be sure to close the BLOB prior the function call. Otherwise the browser will never get the contents of the BLOB (actually it'll never get an answer at all). I don't know whether this is a bug or not, but it occured to me on Oracle 9iR2 (Windows).

The built-in functions SQLCODE and SQLERRM provide information inside an exception handler about the exception itself that is being dealt with. The question is when does the life of an exception end ... or put it the other way around: when does SQLCODE "forget" the error code of the exception and return "ORA-00000" ("normal, successful completion")?

I've found something interesting about the Oracle optimizer and its use of PL/SQL functions in query conditions (in the WHERE clause). It seems that if you refer to a column of a view in a function call, then Oracle tends to "merge" this call into the view and runs it on a lot bigger set of values, then you would think based on the views results. A NO_MERGE hint is of no use in this case.

SearchOracle.com has a podcast with Peter Finnigan on his recently published whitepaper discussing the weaknesses of the PL/SQL wrapper built into most currently used Oracle RDBMSes.

Let's say you have a variable with a character in it and you want to know whether it is a letter or not. First of all choose a proper value for your language for use in the NLS_SORT parameter ('XHUNGARIAN' in my example).

In my experience the overhead of PLSQL procedure invocations is so small that you'll most probably never have to think about it. In 99.99% of all cases the "real" overhead comes from the way you do things inside the procedure(s) and not from the procedure invocation itself. So putting as much code into a single procedure as you can won't help.

I was quite thrilled, when I first read about the "DETERMINISTIC" compiler hint introduced in 8i. Unfortunately it works not as one would hope/expect.