While out scanning the internet, I ran across an Oracle website with a variety of interesting features. If you have some free time, this might be just what you were looking for. more »

Extending the functionality of SQL-NVL. This is pretty old, and most likely replaced by 10g functionality. However for those on older database versions these may be very useful. more »

Nice extension of CLOBs that you can try to leverage in your design. outputs the content of an CLOB to the DBMS_OUTPUT converts a table of VARCHAR to CLOB converts a LONG to a table of VARCHAR converts a LONG to CLOB converts a CLOB to a table of VARCHAR converts a CLOB to LONG more »

Everyone knows the standard aggregate functions (sum, avg, and so on).But they always don't meet your needs. For instance, what about a product aggregate function?This tip will show you how to add your own aggregate function. This is quite simple and can be done with a function and a type.Note that's available only in 9i+.I'll take the example (which is meaningless) of a square product; that is the square root of the product of all elements by group of rows. more »

LONG RAW columns are not normally searchable being that they contain binary data and can also be quite large only to be compounded by the fact that Oracle doesn't provide very many useful ways to look at large LONG RAW data fields.Converting a LONG RAW to a BLOB makes searching possible. Using the below script you can scan for a particular search string among a group of docs or just one select document id. more »

The Microsoft SYLK (Symbolic Link) format is used mainly for the interchange of spreadsheet data between applications such as Microsoft Multiplan and Excel. Files in this format might also be imported directly by business graphics applications. SYLK files are written entirely in ASCII and, like Lotus DIF and SDI, are application-independent. SYLK, however, incorporates several features not found in other spreadsheet data interchange formats. more »

A somewhat 'popular' download here is the Mail Tools package. This package supports sending emails with and without attachments (binary/ascii). One of things I have been meaning to do is leverage our Oracle Applications environment and build concurrent programs to run these mail routines. In a sense a wrapper call.To note, in the source code there are some references to some custom functions which you can either change to your own code or send me an email and request additional information on the specific call or code you are interested in.To test or use, just build an appropriate PLSQL code block or setup a concurrent executable and concurrent program in the application. If the latter, integrate into your request sets.There are four (4) different modules for emailing : more »

A virtual toolchest of coding utilities. I have been building this collection since 2000 and do to this day. I have collected these utilities from various locations, and built others on my own. I share them with others at no charge other than that if they improve or add to them, I would be very interested in your improvements and subsequent integration into my toolchest. more »

Okay, I would never profess to be an expert at XML. Far from it. However, I have been dabbling with Oracle's XML calls. I have put together a set of open api's that permit me to receive an XML transaction, extract and parse the header, and then extract and parse the records contained within the xml transaction file.And you wonder why this is a big deal to me... good point. Well my previous efforts resulted in memory issues when working with large XML files (in excess of 40 mb). I didn't have the option of adding a java parser to my environment as was often suggested. What I ended up doing was pulling smaller bits of the transaction into memory from a transaction stored into a transaction log table. By doing this I was able to process the data more efficiently without burdening the system. more »

Periodically you may run into a situation where it is beneficial to create a view that permits parameters to be passed to it. Views by design do not provide such functionality. However, by using the client_info portion of the DBMS_APPLICATION_INFO you can accomplish this. more »

O'Reilly has published the 4th edition copy of their Oracle PL/SQL Programming book authored by Steven Feuerstein and Bill Pribyl. Over 1000 pages of good material for an Oracle Developer.It is also my 1/15th seconds of fame. Through a collective effort of research on ways to build a FTP process from pure PL/SQL code, yet never finding one specific way that satisfied my need, I finally came up with a final product that gave me everything I was looking for : Put/Get/Rename/Delete + more. All of which was performed within a PL/SQL package. more »

Multi-threading can be accomplished at many levels- Separate stages that run parallel to each other, in Concurrent Manager request sets- Using DBMS_JOB.SUBMIT at the database level- Compiled Pro*C files (Oracle does this with PYUGEN for example)- Unix background processes- Scripting language such as Perl- Java- JSP- etcHowever, in the wise words of someone else I know, sometimes the simpliest answer achieves the desired goal. Shell Scripts, when you get down to it, are pretty simple. No real complex logic in most cases. more »