March 2008 - Simultaneous Program Execution in PL/SQL

Simultaneous Program Execution in PL/SQL
by Steven Feuerstein

The Toad World PIPELINE newsletter recently posted this puzzler by yours truly:

"Which of the following does not help you execute multiple PL/SQL programs simultaneously?"

Oracle Advanced Queuing
DBMS_JOB
DBMS_SQL
Pipelined Functions

From the responses we got to this puzzler, it became apparent that this puzzler stumped lots of people. So I thought I would provide the answer with some additional explanation.

The answer, by the way, is "DBMS_SQL."

First of all, what do I mean by "simultaneous"? PL/SQL does not support true multi-threaded execution. You cannot natively "spawn" execution of PL/SQL programs from within another PL/SQL program.

Yet, there are things you can do that come close to or emulate multi-threading. In most cases, you accomplish this by communicating your desire to run a PL/SQL program to another session. This session detects and responds to your request as quickly as possible.

Beyond that, you can use pipelined functions (a specialized form of table functions) to allow parallel execution of PL/SQL functions from with the FROM clause of a query.

But there is no way at all to use DBMS_SQL (Oracle's long-suffering – those who use it, anyway – package-based mechanism to execute dynamic SQL statements) to emulate simultaneous execution. It will only help you execute a SQL statement in the current session.

So...to explain in a bit more detail:

Use Oracle Streams Advanced Queuing. This is Oracle's "message-oriented middleware" technology for setting up and working through queues of data and commands. With AQ, you put a message onto a queue, and then another session with the appropriate privileges can pull that message off the queue and execute the specified action. Any sort of interesting example of AQ is out of the scope of this blog entry (and, to be honest, I am very rusty when it comes to AQ). You can get lots more information from Oracle Documentation (AQ is now offered as part of Oracle Streams).
Use DBMS_PIPE. This package has been around a long time, and is a relatively simple, quick and fragile (the database goes down and all data in the SGA-based pipes is wiped out) mechanism for communicating between Oracle sessions. My demo.zip file contains an example of using DBMS_PIPEs to share PGA-cached data across sessions. Check out the syscache.pkg file for more details. My Oracle Built-in Packages book, while dated, contains an excellent chapter on DBMS_PIPE, written by my friend and co-author, John Beresniewicz.

And then there are pipelined functions. A pipelined function is a special kind of table function that can be used with the Parallel Query option of the Oracle RDBMS to enable parallelized execution of that function.

You may be wondering "What is a table function?" That is a function which can be called in the FROM clause of a query and treated as if it were a relational table! It is a very cool feature of PL/SQL, fully realized in Oracle9i Release 2, and can be used to hide complex transformations of data behind a simple function interface. Combine table functions with cursor variables, and you can very easily hand off to a Java or .Net developer a result set (rows and columns) that they can fetch with ease into their own local objects.

Here is a very simple example of a table function: generate N names on the fly, within a SQL query.

CREATE OR REPLACE TYPE names_nt IS TABLE OF VARCHAR2 ( 1000 );
/

CREATE OR REPLACE FUNCTION lotsa_names (
   base_name_in   IN   VARCHAR2
 , count_in       IN   INTEGER
)
   RETURN names_nt
IS
   retval names_nt := names_nt ( );
BEGIN
   retval.EXTEND ( count_in );

   FOR indx IN 1 .. count_in
   LOOP
      retval ( indx ) := base_name_in || ' ' || indx;
   END LOOP;

   RETURN retval;
END lotsa_names;
/

And now I call this function from within a query, using the TABLE operator:

SELECT COLUMN_VALUE
  FROM TABLE ( lotsa_names ( 'Steven ', 100 )) names

Of course, this example is a pretty long way from a pipelined table function that can avoid serialization when executed as part of a parallel query.

To learn more, read up on:

My "Best of PL/SQL" training materials – it will give you a high level overview of all this technology.
Chapter 16 of Oracle PL/SQL Programming, 4^th Edition.
Oracle Documentation coverage of pipelined functions.

Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language, having written ten books on PL/SQL (all published by O'Reilly Media, including Oracle PL/SQL Programming. Steven has been developing software since 1980, spent five years with Oracle (1987-1992) and has served as PL/SQL Evangelist for Quest Software since January 2001. He is also an Oracle ACE Director. He writes regularly for Oracle Magazine, which named him the PL/SQL Developer of the Year in both 2002 and 2006. Steven's online technical cyberhome is located at www.ToadWorld.com/SF; explore the non-PL/SQL side of his life (what little of that there is) at www.StevenFeuerstein.com.