I know what you are thinking: what happened to versions 3, 4, 5, 6 and 7? Well, Oracle Corp. got so excited about the great technology they'd developed that they would roll up a whole bunch of major enhancements into one fantastic release. Originally, Oracle had planned to implement the technology according to the schedule shown in Table 1.
| Release Number | Major New Feature | Release Year |
|---|---|---|
| PL/SQL 3.0 | External Procedures | 1998 |
| PL/SQL 4.0 | Object support | 1999 |
| PL/SQL 5.0 | Enhanced array support | 2000 |
| PL/SQL 6.0 | Enhanced LOB support | 2001 |
| PL/SQL 7.0 | Extended national language support | 2002 |
All right, so I'm just kidding. The simple fact is that Oracle decided to "normalize" all of its version numbers to match up with the RDBMS release, which as everyone knows is eight. (By the way, I suggest that you check out a book of the same name: The Eight by Katherine Neville). This book has nothing whatsoever to do with Oracle technology, but it is an excellent thriller by a woman with a female protagonist, still a fairly unusual scenario).
Oracle8 gives you objects, LOBs, and better performance and features for databases so large that, as my son used to say, "you just can't believe it." To ensure that you can take advantage of all this latest, greatest stuff from stored code objects, Oracle also enhanced the PL/SQL language. The list of features shown above just a bit facetiously is the set of major extensions to PL/SQL Release 8. In this article, I look briefly at each of these, giving you a flavor of what you are now can do from within the PL/SQL environment. Of course, you still have to make that fateful decision: Upgrade to Oracle 8. Will it be Oracle 8.0? Will you wait for Oracle 8.1? How many Oracle installations really need all (any) of these new features? These are the questions that cause many Oracle employees to chew down their nails to ugly stubs.
External Procedures
No doubt about it: Oracle's got a crowd pleaser here. With PL/SQL 8, you can finally execute programs written in other languages! You will, in other words, be able to perform "user exits" from within a PL/SQL program and call, say, a C program (in fact, PL/SQL 8 only supports external procedures that are written in C or are callable from C programs).
So if you already have programs that perform specialized processing, perhaps even libraries of programs stored in shared libraries like Windows DLLs, you do not have to recode those programs into PL/SQL in order to execute them from within a PL/SQL-based application. Of course, in any number of scenarios, such as compute-intensive engineering, scientific, and real-time processing scenarios, you would not even have tried to build the programs in PL/SQL. It would have been either impossible or highly impractical (read: very slow).
All of this means that PL/SQL is no longer a "closed language." You can now interact with the operating system, take advantage of existing programs, and, at least in some cases, have a choice about where you want to implement a particular requirement. In this article I can't provide full details on the syntax and code required to enable the calling of external procedures; there are, in fact, many nuances and it is by no means a transparent process. Instead, I'll summarize the main steps you need to take and then offer an example.
Steps to Call External Procedures
Step 1. Store your external program in a shared library, such as a DLL. You may be writing a new program or you may simply be incorporating existing code into a library.
Step 2. Ask your friendly neighborhood DBA to define an "alias library"
within Oracle. This is done with the CREATE LIBRARY command. It is called
an alias library because it is a representation of the actual code library
within Oracle. If you are granted the CREATE ANY LIBRARY privilege, then
you can create your own libraries. Following is an example of a statement
that creates a library containing financial computations (the "so"
extension stands for "shared objects"):
CREATE LIBRARY fincomp_lib AS `/libs/fincomp.so';
Step 3. The DBA then grants EXECUTE privileges on the library to you
and any other authorized user(s). Following is an example of a GRANT statement
for a library:
GRANT EXECUTE ON fincomp_lib TO PUBLIC;
Step 4. Register the external procedure, which means you tell the PL/SQL
runtime engine where to find the procedure, how to call it, and what to
pass to it. To do this, you build a PL/SQL program (either standalone or
in a package) that serves as a representative or proxy of that external
procedure within PL/SQL. This program should have an EXTERNAL clause rather
than an executable body of code. Following is an example of a PL/SQL program
that serves as a proxy for an external procedure that calculates the balance
remaining on a loan with a given interest rate and months paid:
CREATE OR REPLACE FUNCTION rembal (intrate IN NUMBER, mthspaid IN INTEGER) RETURN NUMBER AS EXTERNAL LIBRARY fincomp_lib NAME rembal LANGUAGE C;
Step 5. Place calls to the proxy PL/SQL procedure inside your PL/SQL code. Note that while the PL/SQL procedure accepts normal PL/SQL arguments, it must convert these into datatypes and values that are consistent with the external procedure. Most of the code you write when working with external procedures deals with the parameters you pass back and forth with that external procedure. In the everyday world of calling external procedures from within PL/SQL, functions such as rembal will be enhanced with a PARAMETER clause specifying INDICATOR clauses for NULL handling, and LENGTH and MAXLEN clauses for string parameters. It won't be pretty, but Oracle seems to have covered the issues, so you'll be able to do what you need to do.
Step 6. Run your application. When the PL/SQL runtime engine encounters a call to an external procedure, it loads the library containing that program dynamically, then calls the routine as if it were a PL/SQL function or procedure. To do this, PL/SQL alerts a listener process, which then starts up a process named "extproc" for each session. PL/SQL uses the listener to pass the required information about the external procedure to extproc. The extproc process will also pass values back to the PL/SQL environment through the listener. There are, of course, warnings and restrictions, including:
How well does all of this functionality work? I haven't tried it myself
yet, but there is no reason to think it is anything but a traditional version
1.0 of anything Oracle. It will work, but it will have its wrinkles and
"undocumented features." Use with care and avoid deployment it
in production environments for as long as you can.
Working with Objects
Maybe I should just start off with a confession: I am not really too much of a technology junkie. Sure, I am obsessed with PL/SQL, but that's just "My Thing." It's not "Technology." I have never felt the desire to hook into every new thing that comes along; instead, I try to stay focused on the here and now, particularly as it affects PL/SQL developers. This can lead to myopia with both positive and negative consequences.
When everybody started talking up the Internet, I was working on a book on PL/SQL programming. When Java hit the scene big-time, I was eyeball-deep in construction of PL/Vision, a library of packages for PL/SQL developers. And throughout that entire period, of course, Oracle was talking about Oracle8, which would be able to handle AEDBs (Astronomically Enormous Databases) and offer objects without making us give up outer joins and foreign keys. I have to admit: I just haven't paid it a whole lot of attention, and I have never even read a book on object-oriented design.
But now Oracle has decided to get real and release some software. So I find myself working with Bill Pribyl (former Select Magazine editor, now with Data Craft at www.datacraft.com) to update my book Oracle PL/SQL Programming for PL/SQL version 8. I am reading the Oracle8 documentation and trying to figure out where and how I will install it. (Watch out! Major additional disk space required!). My situation also makes it possible for me to examine all of the new gadgets with a somewhat skeptical eye and with this question before all others: How will objects help PL/SQL developers get their job done on a day-to-day basis? From this perspective, I must say that I am a bit worried. From the numerous training sessions and presentations I have sat through, my feeling is that most PL/SQL developers have not yet been able to take full advantage of what PL/SQL offers today. Their ability (and desire) to move into the world of objects should not be taken for granted.
With all that said, I'll move on from editorial commentator to technical analyst. With Oracle8 you can now create object types in the Oracle database and then create instances of these object types, known as objects. An object type is a completely new beast in the Oracle world because it lets you encapsulate a data structure (a table, in Oracle7 parlance) with programs you need to manipulate that data (PL/SQL code, also known as methods for the object). Prior to Oracle8, you could create data structures (tables) and you could write programs to manipulate the data (most optimally as a package built "around" the table), but there was no native way to link these two aspects together tightly. The object type provides that capability. Still, Oracle8 does not provide "pure" encapsulation, in that Oracle8 allows objects to be manipulated both by callings its methods and by executing SQL against those objects.
Of course, that's just the beginning of the fun. You can have nested
types, so Oracle's object types are "complex object types." You
can have object views, which let you preserve relational database structures
while also presenting that relational data in object terms. Following are
some examples of object types you might build for a person:
CREATE OR REPLACE TYPE name_type AS OBJECT ( first VARCHAR2(30), last VARCHAR2(100), middle VARCHAR2(30), title VARCHAR2 (30), MEMBER FUNCTION fullname RETURN VARCHAR2 ); CREATE OR REPLACE TYPE address_type AS OBJECT ( addrline1 VARCHAR2(100), addrline2 VARCHAR2(100), city VARCHAR2(100), postalcode VARCHAR2(100), county VARCHAR2(100), state VARCHAR2(100), country VARCHAR2(100) ); CREATE OR REPLACE TYPE human_type AS OBJECT ( id NUMBER, name name_type, ssn VARCHAR2(12), home_address address_type, MEMBER FUNCTION favorite_author RETURN VARCHAR2 );
Now, just as with packages, object types have specifications and bodies.
What you see previously are specifications. How do you know? Because object
type bodies start with this line of code:
CREATE TYPE BODY xxx AS OBJECT
You also know because I defined only the headers of two members of the
object types, but they have no bodies. Just as with packages, though, after
I define the specifications (by executing these commands as DDL - you cannot
create object types in PL/SQL code, you can only create them in the database
as stored objects), I can reference this code in a PL/SQL block. Following
are the bodies for two of the types defined above:
CREATE OR REPLACE TYPE BODY name_type
AS
MEMBER FUNCTION fullname RETURN VARCHAR2
IS
v_middle VARCHAR2(32);
BEGIN
IF middle IS NOT NULL
THEN
v_middle := ` ` || middle;
END IF;
RETURN first || v_middle || ` ` || last;
END;
END;
/
CREATE OR REPLACE TYPE BODY human_type
AS
MEMBER FUNCTION favorite_author RETURN VARCHAR2
IS
BEGIN
IF id = 1
THEN
RETURN `Machiavelli';
ELSIF id = 2
THEN
RETURN `Dr. Seuss';
END IF;
END;
END;
/
And the code in Listing 1 shows how to define objects based on object
types and also call methods in objects. Specifically, I declare an instance
(actual object) of a person object type and also use the constructor method
(a special method that comes with every object type and is used to instantiate
an object of that type):
Listing 1
DECLARE
mapa_addr address_type :=
address_type
('1020 W Belmont', 'Apt 15',
'Moca Rattan', '60606', 'Warm County', 'Florida', 'USA');
mom human_type :=
human_type (1, name_type ('Joan', 'Feuerstein', 'Lee', 'Mom'),
'456-789-1010', mapa_addr);
dad human_type :=
human_type (2, name_type ('Sheldon', 'Feuerstein', 'Pop', 'Esquire'),
'123-456-7890', mapa_addr);
BEGIN
IF dad.favorite_author != mom.favorite_author
THEN
DBMS_OUTPUT.PUT_LINE ('What will they talk about in Florida?');
END iF;
DBMS_OUTPUT.PUT_LINE ('Full name of Mom is ' || mom.name.fullname);
DBMS_OUTPUT.PUT_LINE ('Full name of Dad is ' || dad.name.fullname);
END;
/
Now, you can draw several conclusions from this very simple chunk of code. PL/SQL 8 code packed with objects and methods will take some getting used to. I can't be the only PL/SQL Release 2 programmer who looks at this code and thinks to him or herself: Wow! That is some difficult code to read, with the nested calls to constructors and all. It gets even more complicated when you get to collections of objects (which I talk about later in this article), as well as object identifiers, REF and DEFREF operators, and using objects inside SQL.
You are still writing PL/SQL code. In fact, for the foreseeable future, all methods in Oracle8 will be coded in PL/SQL. Oracle has said that it will eventually (Oracle8.1?) let you code methods in Java or J/SQL or something like that, but personally I am keeping my fingers crossed. Go PL/SQL! This means that you can use named notation (the "=>" symbols) to select only those attributes of interest. You can blend new object-based code right into your existing application code set.
Before I look at how Oracle has extended the concept of PL/SQL tables in PL/SQL 8, I want to talk briefly about object views. By the time Oracle8 is released and actually installed by more than a handful of masochistic technology hounds, just about everyone in the world will have converted to Oracle7.3 and will be working hard figuring out how to thrive in the relational world. Their very large databases (VLDBs) will be in tables stuck together with joins. They will need to be able to preserve that data, while also moving it into the object world for new application development. Enter the object view.
An object view is an extension of the Oracle relational view that lets you present data stored in relational tables and/or views as tables of objects. Suppose, for example, that the FBI converts to Oracle8. As you may well suspect, they have a file on anyone who ever participated actively in the democratic process in this country. They have, in other words, many person rows in their person table. In order to perform background checks on potential White House officials with daunting efficiency and accuracy, they will develop their system based on objects.
If the following DDL creates the FBI's person table:
CREATE TABLE person ( trackUdown NUMBER, firstname VARCHAR2(30), lastname VARCHAR2(100), middleinitial VARCHAR2(30), ssn VARCHAR2(12), title VARCHAR2 (30), addrline1 VARCHAR2(100), addrline2 VARCHAR2(100), city VARCHAR2(100), postalcode VARCHAR2(100), county VARCHAR2(100), state VARCHAR2(100), country VARCHAR2(100), /* Addresses, phone numbers, */ /* you name it. */ sexual_preferences CLOB /* Required by J. Edgar Hoover */ );
then I could move the FBI into the object world by creating the following
object view:
CREATE VIEW person_ov OF human_type
WITH OBJECT OID (id) AS
SELECT
trackUdown,
name_type (firstname, lastname, middleinitial, title),
ssn,
address_type (
addrline1,
addrline2,
city,
postalcode,
county,
state,
county)
FROM person;
Then, once you figure out how to manipulate objects from within SQL, you can reference this object view in SELECTs, INSERTs, and other statements.
Nested Tables and VARRAYs
In what has got to be one of the biggest surprises of the object-relational phase of the War of the Database Vendors, Oracle has decided to use the "a" word: array. While this is certainly a step forward, you should not jump to any rash conclusions, such as: PL/SQL now supports arrays!
In PL/SQL 8, Oracle has broadened its support for what is calls "collections," a very general term that covers two new constructs: nested tables and variable-size arrays. A nested table is an "ordered collection of elements, all of the same type." (I admit it: This is from the Oracle PL/SQL 8 manual) The nested table is modeled on the database table, in that it is unbounded (there is no upper limit on rows in the nested table) and sparse (memory is not pre-allocated as it is for a traditional array). Does this sound familiar? Nested tables are the latest incarnation of the old PL/SQL tables, which are now known as index-by tables and are a subset of the full functionality of nested tables.
Following is the definition of a nested table of human beings:
DECLARE TYPE bunch_of_humans IS TABLE OF human_type;
How do you know when you are working with a PL/SQL table, I mean an index-by
nested table? When the declaration syntax includes that cumbersome INDEX
BY statement:
DECLARE
TYPE bunch_of_humans IS TABLE OF
emp.ename%TYPE INDEX BY
BINARY_INTEGER;
Nested tables are much more flexible and interesting than the PL/SQL
tables of days past (in Oracle HQ time, anyway). In the following example,
I declare a nested table of humans and immediately add my mother and father
to that table using the constructor method for the nested table:
DECLARE
mapa_addr address_type :=
address_type
(`1020 W Belmont', `Apt 15',
`Moca Rattan', `60606',
`Warm County', `Florida', `USA');
TYPE bunch_of_humans IS TABLE OF human_type;
family bunch_of_humans;
BEGIN
family := bunch_of_humans
(human_type (1, name_type (`Joan', `Feuerstein', `Lee', `Mom'),
`456-789-1010', mapa_addr),
human_type (2, name_type (`Sheldon', `Feuerstein', `Pop', `Esquire'),
`123-456-7890', mapa_addr));
END;
/
One really nice feature of nested tables is (as you might expect given their modeling on database tables) that you can use them inside SQL statements. In fact, nested tables can be database objects or PL/SQL objects. In the following example, I define a nested table in the database, use that nested table as a column in a table, and then populate that table from within PL/SQL:
1. Create an object type for a Political Action Committee.
CREATE TYPE pac AS OBJECT ( name VARCHAR2(100), industry VARCHAR2(100));
2. Create a table type for the list of Political Action Committees.
CREATE TYPE paclist AS TABLE OF pac;
3. Create a database table for Senators to help track their PAC contributions:
CREATE TABLE beholden_to ( senator name_type, state VARCHAR2(30), party VARCHAR2(30), pacs paclist) NESTED TABLE pacs STORE AS paclisttab;
4. Insert rows into the BEHOLDEN_TO table (see Listing 2). And what about
VARRAYs? Well, it turns out that variable-size arrays are very much like
traditional, 3GL-based arrays in that VARRAYs have fixed lower and upper
bounds. You declare a maximum size when you define the VARRAY, as I do below
with an array holding a string for each month of the year:
DECLARE TYPE month_descriptors IS VARRAY (12) OF VARCHAR2(100);
Listing 2
DECLARE
v_party beholden_to.party%TYPE := 'Republicrat';
BEGIN
INSERT INTO beholden_to
VALUES (name_type ('Joe', 'Hamilshin', 'Swirl', NULL), 'New Colabama', v_party,
paclist (pac ('Smoking Makes Sense Association', 'TOBACCO'),
pac ('Big Bombers Mean Jobs Council', 'MILIND COMPLEX')));
END;
/
How are VARRAYs different from "regular" arrays? They can have only a single dimension and must be indexed by integers. You can, on the other hand, stored VARRAY types in the database. You can also pass them as arguments in procedures and functions and return them in function RETURN clauses. The rules for manipulating nested tables and VARRAYs are similar to one another and to index-by tables (PL/SQL tables). You can define both in the database and embed them inside table definitions. You can pass both by value or reference (the latter improving performance, but not available for index-by tables). So what's the difference between the two structures? Remember that nested tables have no preset size, boundary, or defined elements, while VARRAYs have a fixed size. This distinction has the following consequences:
Support for LOBs
You've been hanging on to those enormous blobby pieces of data for years, waiting for the moment when Oracle would let you work with them in a reasonable fashion. Well, pull `em out of the closet and stick `em in the database. Oracle LOBs (large objects, if you don't know) have finally arrived! With Oracle8, you get four different datatypes for storing blocks of unstructured data:
BLOBs, CLOBs, and NCLOBs participate in transactions-which means that
changes to these data structures can be committed or rolled back. From within
PL/SQL you can declare variables that point to LOBs of these various types
(either to a file or to a locator in the database). You manipulate these
objects with calls to the DBMS_LOB built-in package.
Extended National Language Support
To ensure that no one feels left out when working with an Oracle database,
Oracle8 and PL/SQL 8 extend the national language support (NLS) with two
new datatypes: NCHAR and NVARCHAR2. This means that PL/SQL now supports
two different character sets:
With NCHAR and NVARCHAR2, you can now manipulate and store character strings formed from the current national character set in variables declared with these types.
It's a Start
Oracle8 (and PL/SQL 8 along with it) offer impressive new levels of flexibility and power. I am not yet going to pretend to (a) grasp all the implications of object design in Oracle or (b) understand all the new features of this major upgrade to the Oracle world. Maybe I'll do that after they release the software. For now, I will join the thousands of other PL/SQL developers who will wrestle with the challenge of finding the best fit for new object technology with our relational brains and applications. Steven Feuerstein is the author of Oracle PL/SQL Programming and Advanced Oracle PL/SQL: Programming with Packages (O'Reilly and Associates, 1995 and 1996, respectively), and is the Director of the Oracle Practice for SSC, a systems management consulting firm based in Chicago (www.saraswati.com). Steven is a Senior Technology Officer for RevealNet and coauthor of the Reveal for PL/SQL knowledge server (www.revealnet.com). Finally, he is the developer of PL/Vision, the first third-party library of packages for PL/SQL, also available from RevealNet. You can email Steven at [email protected].