Thursday, November 24, 2005


You would like to communicate with a 3rd-party application from your PL/SQL program, for example you want to run a UNIX command. Or perhaps you'd like to communicate directly with another Oracle session. Whatever your specific communication needs are, DBMS_PIPE is your solution.

What is DBMS_PIPE?

DBMS_PIPE is a package provided by Oracle that allows two or more sessions in the same instance to communicate.

If you know anything about UNIX pipes, its a similar concept in Oracle. It is called a pipe because it connects two (or more) sessions, and messages are queued up inside, just like a pipe. Each session can take the next received item out of a pipe, or insert the next item to send. Anybody (with access) can insert or remove something from the pipe, in any order. Messages can only be removed and read once - two people can't remove the same message from a pipe.

In more real terms, these pipes are buffers in the system global area (the SGA). Messages are prepared for loading using PACK_MESSAGE, loaded into a pipe using SEND_MESSAGE, and read similarly (RECEIVE_MESSAGE then UNPACK_MESSAGE).

There are basically 3 different types of pipes. Briefly:
Implicit Public Pipe: Automatically created when first accessed, disappears when it is emptym, available to entire schema
Explicit Public Pipe: Created with CREATE_PIPE, freed with REMOVE_PIPE, available to entire schema
Explicit Private Pipe: Created with CREATE_PIPE and private=true, freed with REMOVE_PIPE, available only to that userid privileges, or SYSDBA.

Check chapter 45 of the PL/SQL Supplied Packages and Types Reference.


For this test, open up two sessions as the SAME user on the SAME instance. Make sure your user has access to the DBMS_PIPE package.

We are going to have the first instance create the pipe, send in an SQL command, and have the second instance retrieve that message and execute it. That should accomplish two things: show the basic usage of DBMS_PIPE, and give Pete Finnigan a heart attack.

I have largely left out error-checking and handling, for brevity's sake. At many points throughout the "ORA-23322" error is possible, which is for insufficient privileges to access that pipe.

Session #1:

l_status NUMBER(2);

-- Create the pipe. 0 is success.
-- Possible error: name in use.
l_status := DBMS_PIPE.CREATE_PIPE (pipename => 'TEST_PIPE',
maxpipesize => 8192, private => TRUE);

-- Let's pack an instruction for another session.
-- Possible error: buffer overflow (4093 bytes of data)

-- Let's stuff it into the pipe
-- We'll use defaults for maxpipesize and timeout
-- Returns 0 on success, 1 for timeout, 3 for interrupt
l_status := DBMS_PIPE.SEND_MESSAGE(pipename => 'TEST_PIPE');

-- Ok we're done, we should get 0 for success
l_status := DBMS_PIPE.REMOVE_PIPE (pipename => 'TEST_PIPE');


Session #2:

l_received_message VARCHAR2(128);
l_message_type NUMBER(2);
l_status NUMBER(2);

-- Receive the message, use default timeout
-- If the pipe doesn't exist, Oracle will create it,
-- and wait for a message.
-- Returns 0 on success, 1 on a timeout, 3 for an interrupt
l_status := DBMS_PIPE.RECEIVE_MESSAGE(pipename => 'TEST_PIPE');

-- Ok, so what type are we extracting?
-- 0 nothing, 6 number, 9 varchar2, 11 ROWID, 12 DATE, 23 RAW
l_message_type := DBMS_PIPE.NEXT_ITEM_TYPE;

-- Open up the message, we can get ORA-06556 or ORA-06559
-- if its the wrong time, or nothing is left.
IF (l_message_type = 9) THEN
DBMS_PIPE.UNPACK_MESSAGE(item => l_received_message);
EXECUTE IMMEDIATE(l_received_message);


By the way, I realise that I have removed the pipe in the first session before accessing that message in the second session. But because there is still a message in there, the pipe will stick around. To destroy it immediately I would have to purge it first.

More Examples:

The aforementioned guide has an example on how to use DBMS_PIPE for debugging. There is also a great example on communication with the shell to execute UNIX commands (like listing the contents of a directory). These are complete, excellent examples.

I'll close with some other little notes about DBMS_PIPES:
- There is a constant called maxwait which determines how long the pipe will wait for a message to be picked up.
- The two major errors with DBMS_PIPE are ORA-23321 (bad pipename) or ORA-23322 (insufficient privileges).
- You can use PURGE to empty out a pipe, and RESET_BUFFER for the local packing buffer
- You can use UNIQUE_SESSION_NAME to help distinguish between sessions if there are several accessing the pipe.

Monday, November 21, 2005

RAW Datatype

Earlier I mentioned the idea of using a RAW datatype for your table's primary key. I stumbled upon a minor inconvenience with using the RAW datatype today, so I thought I would take a step back and talk a little bit more about the RAW datatype.

RAW(size) is used for raw binary data or byte strings of length "size" bytes (must be specified). The maximum is 2000 bytes, but in PL/SQL it is 32767, which can lead to interesting situations.

Think of RAW very much like VARCHAR2, but unlike VARCHAR, RAW data will not be converted depending on the character set (by import/export, for example). More generally, Oracle will treat this as binary data and will not interpret it as a number, character, or anything else (generally done when moving data from one system/session to another).

RAW data can still be viewed as characters (2 characters per byte). Something like SQL*Plus does this for you automatically when you query. But RAW data is a little harder to use in pure SQL, because you often have to use HEXTORAW or RAWTOHEX to do your work (example shortly). However in PL/SQL you can just use the UTL_RAW package.

Now back to the story.

In the aforementioned article I mentioned using it as a primary key, using SYS_GUID. The advantages are you get a (generally) unique and randomly-dispersed key that you should never have to change. Please note: I do not want to get dragged into a debate on whether or not it is a good idea, I'm just saying its possible, and has some advantages. And yes, RAW data can be indexed just fine.

Now let me give you an example of the type of minor annoyance you have to deal with when using RAW datatypes.

Associative arrays, also referred to as:
1. Hash arrays: in other languages, like Perl, that is what they are called
2. "Index-by tables": because they are declared just like nested tables except with "index by" added to it.
Can not use RAW datatypes as their keys.

For this example I will use "NUMBER" instead of the %ROWTYPE you would have if using RAW(16) as your primary key.

test_ass ASS_TYPE;
test_ass(SYS_GUID()) := 1;
test_ass(SYS_GUID()) := 2;
test_ass(SYS_GUID()) := 3;

PLS-00315: Implementation restriction: unsupported table index type

So you have to convert it to VARCHAR2 in order to use it, using RAWTOHEX. You can use HEXTORAW to turn it back. Here is a working example:

test_ass ASS_TYPE;
test_ass(RAWTOHEX(SYS_GUID())) := 1;
test_ass(RAWTOHEX(SYS_GUID())) := 2;
test_ass(RAWTOHEX(SYS_GUID())) := 3;


Related Info:

There is some additional information the Oracle SQL Reference and Oracle Application Developer's Guide - Fundamentals.

Other binary data types, depending on your version, include BLOB, Bfile, LONG RAW, and others.

Here is a link to an AskTom discussion on RAW

Friday, November 11, 2005

More Oracle Essays

I recently posted an assorted list of Oracle essays from some of my favourite Oracle authors:

There are so many other great Oracle writers out there pleased to provide free insights into Oracle. So I thought I'd share a few more with Troy and everyone else. Remember that each of these authors have written several papers, let me know if you have trouble locating more work from your favourites.

Improving SQL Efficiency Using CASE by Doug Burns

Exploiting and Protecting Oracle by Pete Finnigan for PenTest Limited

Write Better SQL Using Regular Expressions by Alice Rischert for Oracle

What, Where, When, Why - Selecting Oracle Development Tools by Ken Atkins from ODTUG 2001

Getting Fast Results From STATSPACK by Bjorn Engsig for Miracle A/S

SQL Tuning With Statistics by Wolfgang Breitling for Centrex

The VARRAY Data Type by Howard Rogers of Dizwell

SQL In, XML Out by Jonathan Gennick for Oracle Magazine May/June 2003

Planning Extents by Steve Adams for Ixora

Oracle Collections: A Definition in Plain English by Lewis R. Cunningham

Sorry, no Oracle papers from your favourite blogger! :)

Thursday, November 10, 2005

DUAL Table

What is the DUAL table?

The DUAL Dummy table (as it is sometimes called) is an automatically-generated table assigned to SYS, but accessible to all users. It has a single column "DUMMY" of type VARCHAR2(1) which has a single row with a value of 'X'.

scott@Robert> SELECT * FROM DUAL;


scott@Robert> DESC DUAL;
Name Null?
----------------------------------------------------------------------- --------

Warning: Do not modify the DUAL table! Why would you want to anyway? It is part of the data dictionary, which you shouldn't modify (that's a topic for another day).

What is it used for?

It is useful because it always exists, and has a single row, which is handy for select statements with constant expressions. You could just as easily do this with any other table with a single row, but using DUAL makes it portable among all Oracle installations.



The other reason to use the DUAL table isn't just portability, but optimization. According to Tom Kyte, the Oracle Optimizer knows it is a special one row, one column table. Eddie Awad tested a related assertion recently:

Why is it called "DUAL"?

It was named "DUAL" because the primary intention of this table was to allow users to create 2 rows for every row in a table by joining it to this system table. Thus "DUAL". Chuck explained it in the January/February 2002 issue of Oracle Magazine:

Of course, you can quite easily use DUAL to create as many rows as you want, using LEVEL (or CUBE). Just Ask Tom how this is done:

Why is it sometimes referred to as a "magic" table?

For the answer to this and any remaining questions about DUAL, just Ask Tom:


Earlier I made mention of the DUAL table, including an example:

Eddie Awad recently tested Jared Still's assertion of an interesting feature in Oracle 10g. Even if DUAL has more than one row, apparently you can trust it to always return just a single row. Read more:

Selecting from the DUAL table is mentioned in Chapter 8 of the Oracle SQL Reference Guide:

Wednesday, November 09, 2005


This marks my 60th post since I started in May, and, according to my stat counter I have had 10,000 hits.

I was expecting 100, and even if 100 was the current total, it would still be worthwhile for me to share my thoughts. Knowing that my ideas have been read 10,000 times makes me feel more excited about sharing them.

So whoever all of you are, you have my thanks, and I will definitely continue to write about Oracle for as long as people are willing to read it!


Friday, November 04, 2005

Oracle Essays

Recently I recommended "Oracle Insights," a book containing a series of Oracle-related essays by some of the top authors in our community. For those who are interested in reading some more essays, consider some of the following samples from a variety of my favourite authors on a variety of topics. The best part is that these are free!

Re-building Indexes: When, How, Why by Jonathan Lewis

Programming Humility: Dealing with the Reality of Errors by Steven Feuerstein of Quest Software

Locking and Concurrency by Tom Kyte

Practical Guide to Oracle Data Warehousing by David Aldridge

Query Tuning Using DBMS_STATS by Dave Ensor

String Aggregation Techniques by Dr. Tim Hall

Bind Variables Explained by Mark Rittman

The following two articles are also worth mentioning again.

Why a 99%+ Database Buffer Cache Hit Ratio is Not Ok, by Cary Millsap of Hotsos

Why I Invented YAPP by Anjo Kolk (Free registration for download)

While recommending some reading, I'm also including my favourite Oracle community debates of 2005:

The great Burleson/Kyte debate on "Predictive Reorganization"

The great Dizwell Forum debate: "Natural vs Surrogate keys"

The great Burleson/Lewis debate on "Supersizing your PGA"

Wednesday, November 02, 2005

Oracle Insights: Tales of the Oak Table

A recent book review of "Oracle Insights: Tales of the Oak Table" by Doug Burns, combined with the introduction of Mogens Norgaard's new blog motivated me to finally finish my own review of his book, which has sat incomplete since I first read it 6 months ago.

Doug Burns' Book Review of "Oracle Insights:"

Mogens Norgaard's new blog:

Oracle Insights is a series of essays from a variety of relatively like-minded authors that together comprise the Oak Table Network. This network includes some of my favourite authors, a group of individuals who are very excited about their experiences with Oracle and are eager to both share their insights, and gain even more.

For more about the Oak Table Network, visit their web site:

Oracle Insights is a wonderful collection of essays from a very talented and enthusiastic bunch of Oracle experts. It starts with a great history of Oracle, and then is followed by 10 chapters, basically split evenly between the topics of performance/tuning and collections of war stories of Oracle projects gone bad.

Being a collection of essays, each chapter is stand-alone, linked together only by Mogens Norgaard's prefaces. In fact, I really enjoyed the prefaces because they were very casual and personal. Normally I skip prefaces because they're carefully scripted, bland advertisements for a book you're already reading, but Mogens were short, and served to put a human face to each of the authors.

Because of the nature of this book, I will briefly review each of the essays before going into my overall impression of the book. First, you may want to visit Jonathan Lewis' site, where he has a synopsis on each chapter as well as some anonymous reviews (with his responses):

Chapter 1: A Brief History of Oracle, by Dave Ensor

Without question, this is the highlight of the book. It is much longer than the other essays (maybe he should drop "Brief" from the title), and he does tend to go on from time to time, but this is still the hardest chapter to put down. It is admittedly not a complete history, but it touches on the evolution of Oracle from Ted Codd's original paper of Relational Databases to what Oracle is today. It organises Oracle's responses to Codd's 12 Rules, and then some of the other related hot topics over the years that shaped Oracle into what it is today: e.g. Transactional Integrity, Isolation Levels, Privileges, Rule Based Optimizers. It then covers Oracle version-by-version discussing the new features, their motivations, and their consequences, right up to and including version 10.

I've heard it said that this chapter alone is worth the price of the book, and its hard to disagree.

Chapter 2: You Probably Don't Tune Right, by Mogens Norgaard

There are at least 5 chapters that focus on performance and tuning, this being the first and probably the weakest. Let me qualify that. First of all, I mean that more out of praise to the other essays than an indictment of this one. Indeed, this essay is (at least in part) a summary (or high-level view) of the others.

Secondly, this chapter's strength is not in "tuning" but rather on the topic of Instrumentation. I think that would have made a better essay, and that's what makes it worth reading.

One word on the style. This chapter is more like a narrative, or a conversation that the author is having with you. I'll be honest, that's not really my style (although its great for prefaces). For example, sometimes it gets repetitive. ("the Oracle database is by far the best database around, technically speaking", next page: "Oracle is technically the best database around.")

Chapter 3: Waste Not, Want Not, by Connor McDonald

Connor McDonald is perhaps the most talented writer in the crowd, and that's saying a lot. His essay includes examples of inefficient (or "wasteful") applications, SQL statements and PL/SQL blocks. Even in this short essay, he describes how he found these wasteful cases, why they were wasteful, and how to correct them. Those that learn from example will particularly enjoy his lessons.

Chapter 4: Why I Invented YAPP, by Anjo Kolk

The shortest chapter, and now the third on performance. This chapter is only of interest to those that have already read (and enjoyed) Anjo Kolk's famous (and awesome) paper on Yet Another Performance Profiling Method. Here is the link to that paper (after a free registration):

Chapter 5: Extended SQL Trace Data by Cary Millsap

This one, you can judge for yourself, because this chapter is available on-line right here:

If you want to hear a little bit more about this chapter, I found that it was consistent with Cary Millsap's other work, about which you can find a review of mine here:

Chapter 6: Direct Memory Access, by Kyle Hailey

Because of the developer in me, this was one of my favourite chapters. Kyle Hailey relays a story about having met and worked with an Oracle master named Roger Sanders and his "m2" application, which could access the memory Oracle was using directly. Having opened our minds to the possibilities, his story leads into a discussion on Oracle's SGA (Shared memory) and DMA, leaving you thirsting for more. A very memorable chapter for fans of the nuts and bolts, and yet is not that heavy a read.

Chapter 7: Compulsive Tuning Disorder, by Gaja Krishna Vaidyanatha

It is time not only for yet another chapter on performance and tuning, but also for a comedy break. Don't get me wrong, there is just as much substance as the other chapters, especially for fans of the Oracle Wait Interface.

The most valuable contribution of this chapter is the so-called "Cure for CTD". It is a 2-pronged methodology that focuses both on the OWI and the OS. It is very clearly summarized on page 235: photocopy it and pass it around to your team. After explaining the system in very clear and specific terms, the chapter closes with 2 appropriate examples. Even your junior DBAs can now look like pros.

If they had decided there were far too many chapters on performance and tuning (which there are), and decided to only keep one, this chapter would have my vote.

Chapter 8: New Releases and Big Projects, by James Morle

This essay deals with one particular Oracle project gone bad. It starts where the author joined in, describing the mess his predecessors had gotten themselves into, and then all the problems they had to contend with along the way. A fun read.

Chapter 9: Testing and Risk Management, by David Ruthven

"Poor engineering practises remain the root cause of application failures and there is usually very little in the way of contingency planning to avoid and minimise the impact of such failures." - David Ruthven.

In this chapter, David Ruthven breaks a database application project down, dividing it into types and levels, and identifies where they often go wrong. He includes something for everyone, for example I enjoyed his summary on the ingredients to an effective development environment. He even helps quench the thirst for more on Instrumentation that was first whetted in Chapter 2.

The second half of his chapter focuses on testing. He touches on functionality tests, white box and black box tests, regression tests, automated tests, scalability tests - everything you wish your manager had read about.

Chapter 10: Design Disasters, by Jonathan Lewis

More war stories, for fans of Chapter 8! "Now prepare yourself to read all about 'The World's Worst Oracle Project.'" - Jonathan Lewis.

This chapter describes some of the most common mistakes in development Oracle database applications. You'll certainly recognise some of them, because so many people stubbornly cling to certain beliefs. I know I like to bring up several of his points when I get into common arguments like these:
1. We want our application to be "Database Independent."
2. We will check data integrity at the application level instead of taking advantage of Oracle's constraint checking abilities.
3. We want to use sequences for our primary keys.

I won't give away too much of this chapter, but its definitely food for thought for anyone who has ever been (or plans to be) involved in a database application development project.

Chapter 11: Bad CaRMa, by Tim Gorman

The book closes with another war story. We get a look at Vision, a custom-built order-entry and customer-service application (a CRM). "This story is about such an IT project, the most spectacular failure I have ever experienced." - Time Gorman. (Why do we enjoy the chapters about failures so much?)

Appendix: Join the BAARF Party, by James Morle and Mogens Norgaard

This is a rant against Raid-5. Check out their web site for more:

Overall Impression:
This book has been fairly widely praised throughout the Oracle community. It received 4.5 out of 5 stars on and even PSOUG's Dan Morgan gave it a very high recommendation:

Personally I would love to see many more books like these. I certainly hope there is a sequel coming up with even more essays from the most gifted and enthusiastic authors in the Oracle community. I would even love to see more of these essays expanded into books. This book is a very fun read, and I guarantee every Oracle specialist will find several essays within that they will enjoy. High recommendation.

This page is powered by Blogger. Isn't yours?