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.

* Updatable External Tables
* Pie Charts in SQL*Plus
* Bar Graphs in SQL*Plus
* Hierarchical queries
* SQL Developer (formerly known as Raptor) by Oracle
* PL/PDF (Doc to PDF for PL/SQL environment)
* XML Publisher
* How ROWNUM can help tune a query
* PL/SQL Source Control Code in the Database
* Using PL/SQL Collection return a REF CURSOR
* Debugging PL/SQL with DBMS_DEBUG
* PLDOC, documenting your PL/SQL directly from the database
* Publishing PL/SQL Based Web Services
* Consuming Web Services from PL/SQL - Part II: A pure PL/SQL solution using UTL_HTTP (Oracle 9i+)

Enjoy !

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.

Pages: 1 · 2

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

Click to download code

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.

Pages: 1 · 2 · 3 · 4

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.

Click to download script

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.

Click to download code

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.

Pages: 1 · 2 · 3 · 4

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.

Click here to download code

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.

I am wrapping up a few pieces of it and then I will post it here for interested parties to review and comment on. Please do your best not to laugh at my efforts. LOL. However, I am trying, that much can be said. Until then… if you have any thoughts on processing XML or sample code, please feel free to post and share with others.

Pages: 1 · 2 · 3

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.

Pages: 1 · 2

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.

Pages: 1 · 2

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
- etc

However, 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.

Pages: 1 · 2