New Orleans - a PL/SQL
Kind of Town
Who would of thought it? ODTUG
Now
was held June 18-22 in New Orleans this year. ODTUG is the
Oracle Developer Tools User Group, and its annual conference has long
been considered just about the best gathering for those interested in
Oracle development issues and challenges. I arrived in New Orleans
Sunday night, with my wife, Veva, and son, Eli, in tow. They don't
generally join me on technology travel jaunts, but: Veva enjoyed New
Orleans an awful lot the last time we'd visited; soft shell crabs were
in season; and Eli is an excellent jazz guitarist (18 years old and
just completed his first year at DePaul University).
It was a great visit on
multiple levels. We heard some great Dixieland jazz and blues piano. We
enjoyed some wonderful food, including beignets at Cafe du Monde and
delightful seafood at Andrew Jaegers. But perhaps best of all, and to
my great surprise, I was the recipient of two ODTUG awards:
Best
Presentation: Gee
Whiz: New PL/SQL Toys in Oracle 10g
Editor’s
Choice Award: Programming
Humility: Dealing with the Reality of Errors
(both presentations
available for
download at http://oracleplsqlprogramming.com/resources.html).
Congratulations also to Toon
Koopelaars, Scott
Spendolini and Jerry Ireland on receiving their
own awards from ODTUG!
I must say, it is always
an honor to have one's efforts recognized, but I am especially pleased
by ODTUG recognition. This is a conference by, for, and of
developers. Which also should remind us of another important fact:
PL/SQL remains an incredibly important technology to many companies and
programmers. This fact can also be seen, by the way, on the Oracle
Technology Network, where my Best Practice PL/SQL column is among the
top five pages on the entire OTN website!
OK, enough self-pats on
the back. Time for an Oracle PL/SQL Programming newsletter!
Hey, don't forget!
Join
me me on November 2-3, 2005 for a two-day PL/SQL conference: Oracle PL/SQL
Programming 2005
Help influence the future of the PL/SQL language
by visiting
the 21st
Century PL/SQL Idea Collector.
Check out QCGU,
www.qcgu.net,
which you can use to
quickly generate a PL/SQL code library customized to your own database
objects. Use QCGU to generate,
reuse and test code. It will totally transform the why you write PL/SQL
applications!
Tip of the Month: Table Functions
One of the coolest, new capabilities in Oracle9i
Database is
the ability to define functions that can be treated as tables in a
query. They are called table
functions and they come in very handy when you need to...
-
Pass lots of data back to a host environment
(say, Java or
.Net), but that data cannot be specified solely through an SQL query.
You must perform complex transformations on the data and then serve it
up. You can hide all that complexity inside a PL/SQL function that
returns a collection. The
collection can then be treated as a relational table in the FROM clause
of the query and out the data goes, in just the form needed.
-
Perform multiple transformations of data,
moving that data
from one table to another. With table functions, you can stream the data without having to
populate and manipulate PL/SQL program structures. Everything can be
done within a single SQL statement (example below).
-
Execute a PL/SQL function as part of a
parallel query
(critical for many data warehouse applications). One variant of a table
function is the pipelined
function, in which data is returned by the function asynchronous
to termination of the function. In other words, while the function is
still executing, it is sending information back to the calling program
or query. This feature is also handy for serving up data to a webpage
before all the work of the function is complete.
Below is an example of a streaming function that
clearly
demonstrates the elegance of table functions. I need to take data in
the stocktable and transfer it to the tickertable. The stockpivot
function performs all of the complex transforamtions needed.
BEGIN
INSERT INTO tickertable
SELECT *
FROM TABLE (stockpivot (CURSOR (SELECT *
FROM stocktable)));
END;
/
Allow me to explain some of the steps that occur
in the above
SQL statement:
-
Pass the contents of the stocktable into the
stockpivot
function as an argument. I do
this by taking advantage of the CURSOR operator, which in essence
transforms a SQL query into a cursor variable.
-
The stockpivot function performs its magic
(link to code
below) and returns a nested table of objects.
-
The TABLE operator transforms that nested
table into a
structure that emulates a relational table.
-
The SELECT statement retrieves all the rows
from the
nested table.
-
The INSERT statement moves that data into the
new
tickertable.
I think that is very cool stuff. Of course, that
one little
example doesn't reveal too many of the "secrets" of the table function.
May I suggest you pay a visit to my Resources
page, download 21st Century PL/SQL and the demonstration zip file? You
can then read more about table functions and be directed to specific
files, such as tabfunc_streaming.sql, that will help you figure out how
to use table functions in your code.
You can also check out(warning: commercial plug coming)
QCGU, whose
code repository offers many templates to generate code for table functions,
and
Oracle PL/SQL
Programming, the book, for a more extensive explanation.
Useful Code of the Month: Dynamic SQL Method
4
The intab procedure in this
download provides a "SELECT * FROM
<table>" capability from within PL/SQL. That is, you provide the
name of the table, an optional where clause and column name filter, and
the "in table" procedure displays the contents of the table matching
your criteria. The primary intention of this program unit to provide an
example and template for writing code that implements Method 4 Dynamic
SQL with the DBMS_SQL package. Method 4 means that at the time you are
writing your code, you don't know either (a) how many columns you are
querying or (b) how many bind variables are in the dynamic string. This
level of uncertainty makes the code you write much more challengingly
generic. For this download, since the table name is a variable, we
don't know the number (and types) of columns that will be queried at
runtime.
The intab_dbms_sql.sp file shows how to implement
a dynamic
"SELECT *
FROM <table>" using DBMS_SQL. While this supplied package
is no
longer the first choice for most dynamic SQL requirements, it is the
only mechanism available in PL/SQL to implement method 4 dynamic SQL.
In fact, the very aspect of DBMS_SQL that makes it unattractive (the
low-level API requires us to manually code each step of the process)
fits perfectly with the degree of flexibility needed for method 4.
Use the intab.tst script to compile this program,
create a
test table,
and display its contents using intab with a variety of argument values
to exercise intab functionality.
Note: the intab procedure has a number of
restrictions,
documented in
the program header, which you will want to address if you intend to use
this code in a production environment or increase its flexibility.
Recent PL/SQL Writings
of Note
You might find some of these
interesting...
-
My latest
article published in the Oracle Professional
newsletter: "A
very common
requirement among developers is to be able to use a dynamic set of
values in the IN clause. That is, at the time you write your program,
you don't know if you'll have one, five, or 75 values to be placed
inside the list for IN. In this article, Steven Feuerstein explains
various options for implementing a dynamic IN clause."
-
"Nulls:
Nothing to Worry About" - An Oracle Magazine article that explores
the challenges and complexities of NULLs in SQL (we all run into
similar issues in PL/SQL as well). Written by two outstanding Oracle
technologists, Jonathan Gennick and Lex de Haan.
Upcoming public trainings
Focus shifts to...Europe, where I will be spending
the week of
September 5th in Scandinavia (trainings sponsored by Quest) and the Netherlands,
followed by Oracle
Open World the week of September 19 (at which the fourth edition of
Oracle PL/SQL Programming will be released), and finally the week of
October 4 in the United Kingdom (trainings sponsored by Quest).
|
