Object Types of Little Use in PL/SQL (long)

From: Mark Wagoner <mwagoner_at_iac.net>
Date: Thu, 8 Aug 2002 19:50:35 -0400
Message-ID: <aiv04b$1sgt$1@genma.iac.net>

I am working with 9i (9.0.1) in a Java servlet environment and thought I would explore the use of object types as a way to move data between my stored procedures and Java code (using JPublisher). After 3 days of false starts, I would have to say there are two options: go all-out and store the data as objects (do not use packages) or stick with result sets/ref cursors.

Also, as a warning, I appear to have found a bug so don't try this at home. I will explain this at the end.

I am trying to store menu configuration data. The menus operate in a manner similar to the Start menu in Windows. Each menu option is either a URL to a page to be displayed or it contains another sub-menu to be drawn. The data is being stored relationally, my reasoning being that it should be easier to learn how to work with the objects PL/SQL first before trying to store the objects and learning all of the object-related SQL stuff. It appears this assumption is wrong.

First, I tried to create an object hierarchy by defining a supertype to hold the text to be displayed on the menu and one subclass to define a URL option and another subclass to define an option that contains a submenu. The menu itself is simply a collection of menu options.

CREATE TYPE popup_option_typ AS OBJECT (   text VARCHAR2(20)
) NOT FINAL; CREATE TYPE popup_menu_typ AS TABLE OF popup_option_typ;

CREATE TYPE popup_option_url_typ UNDER popup_option_typ (   url VARCHAR2(100)
);

CREATE TYPE popup_option_menu_typ UNDER popup_option_typ (   sub_menu popup_menu_typ
);

This works fine; I can create the objects in PL/SQL and populate them with data from the relational tables. The problem I run into is when I go to get the menu options back out of the collection. Because the collection is defined as being of the supertype, I cannot access any of the subclass' attributes. As far as I can tell, then is no way to cast the object to the proper type or even to determine what type the object truly is. I found an IS OF operator in the documentation, but this appears to be a SQL operator. Trying to use it in PL/SQL causes an error.

Giving up on that approach, I thought I would create one generic object that can be used either way (kind of goes against the purpose, but that's not the worst of it). After much head-scratching and documentation searching I found that you have to use "forward reference declaration" when you have a circular reference, e.g., the menu contains menu options which, in turn, contain a menu. I also found that, when you do the forward reference, the object is marked as "incomplete" and you cannot use it as part of another object. Instead you have to use a reference to the object. I eventually recreated the objects as follows:

CREATE TYPE popup_option_typ;

CREATE TYPE popup_menu_typ AS TABLE OF REF popup_option_typ;

CREATE OR REPLACE TYPE popup_option_typ AS object (

  text        VARCHAR2(20),
  url          VARCHAR2(100),

  sub_popup popup_menu_typ
);

This created fine, but it appears you cannot manipulate object references in PL/SQL so I have no way of creating the collection.

Now for the bug. When trying to clean up (by dropping the types) it appears Oracle won't let you drop a type that is referenced by another type. The problem is popup_option_typ references popup_menu_typ which contains a reference to popup_option_typ. Therefore, I CANNOT DROP EITHER OF THE TYPES!!! Hopefully I am just an idiot. I keep telling myself that it can't be this difficult or the whole O/R thing wouldn't be much of a "feature". However, after days of working on this same problem (which I could have coded in an evening using ref cursors) and having a bigger mess than when I started (database objects I can't get rid of), I feel the need to warn others about this mess.

Considering Oracle added object types in version 8, I would have thought they would be more useful by now. Received on Thu Aug 08 2002 - 18:50:35 CDT