Pythian Group

Thanks to a patch from Tomas Pokorny, you can now select Oracle user-defined types directly into a Perl object.

User-defined types include Object, Varray, and Table. These have been around in Oracle since 8i but have never really gained any sort of popularity for any number or reasons. The most common reason I have heard from DBAs and modelers is that they break relational integrity. As the objects in Oracle are “Embedded,” I do not see how that is possible. The more practical reason is that the SQL to retrieve and query this sort of object tends to be rather involved, and is sometimes slower than a simple table join; also, this sort of object can be hidden or unavailable to many kinds of modeling software.

With the new functionality of DBD::Oracle, selects of these objects are simple to work with in Perl. Lets take the example below for a web site that has a number of different “user” types.

First, we have the base Object for a web_user (think of this as an abstract or base class):

CREATE OR REPLACE TYPE web_user AS OBJECT (
    first_name    VARCHAR2(20),
    last_name      VARCHAR2(100),
    user_id		varchar2(15)
) NOT FINAL;

Then we can add some new classes to this object:

CREATE TYPE employee UNDER web_user (
    employee_type  number,
    priv_level     number
);

CREATE TYPE customer UNDER web_user (
    customer_id    number
);

CREATE TYPE tester UNDER web_user (
    priv_level     number,
    test_date      date
);

And finally we just add a new table of these objects:

CREATE TABLE web_users (id INTEGER, obj web_user);

Next, we will have to add some real data to the table:

INSERT INTO web_user VALUES (1, employee('Bob','Barker','barker2',5,6));
INSERT INTO web_user VALUES (2, customer('Sara Jane','Smith','smithsj', 44));
INSERT INTO web_user VALUES (3, tester('Bill','Bloggings','blogging',5,'09/08/2008'));

This little model is a good example of the use of an embedded object, because the data is largely static—the model is not going to change; and I like to get all of a user’s profile in one simple select rather than in a more complex join or even in a second select.

To use Tomas’s new object patch, we first have to set the ora_objects property on the data handle, like this:

$dbh{'ora_objects'} =>1;

and then, as before, a simple select:

$sth = $dbh->prepare("select * from web_user order by id");
$sth->execute();

And now, when the data is fetched, it goes directly into an instance of DBD::Oracle::Object:

my ($id1, $web_user1) = $sth->fetchrow();
my ($id2, $web_user2) = $sth->fetchrow();
my ($id2, $web_user3) = $sth->fetchrow();

So now we can get the type of the object, like this:

print $obj1->type_name;     # 'WEB_USERS.EMPLOYEE' is printed
print $obj2->type_name;     # 'WEB_USERS.CUSTOMER' is printed
print $obj3->type_name;     # 'WEB_USERS.TESTER' is printed

You can also get the individual attributes, like this:

print $obj1->attr('FIRST_NAME');   # 'Bob is printed
print $obj2->attr('USER_ID');      # 'smiths' is printed
print $obj2->attr('CUSTOMER_ID');  # '44' is printed
print $obj3->attr('TEST_DATE');    # '09/08/2008' is printed

or you can get all the attributes as hash reference:

my $h1 = $obj1->attr;        # returns {FIRST_NAME => 'Bob', LAST_NAME=>'Barker',USER_ID=>'barker2', EMPLOYEE_TYPE=>5,PRIV_LEVEL=>6}
my $h2 = $obj2->attr;        # returns {FIRST_NAME => 'Sara Jane', LAST_NAME=>'Smith',USER_ID=>'smithsj', CUSTOMER_ID=>44}

Or even all the attributes both names and values as an array:

my $h1 = $obj1->attr;        # returns {'FIRST_NAME','Bill','LAST_NAME','Bloggings','USER_ID','blogging','PRIV_LEVEL',6,'TEST_DATE','09/08/2008'}

The object takes away the need to create a whole bunch of small single-use variable like this:

my ($id, $first_name,$second_name,$user_id,$employee_type,$priv_level) = $sth->fetchrow();

if the model was made up of non-embedded objects.

Now that I have all of the user’s profile in one shot, I can give a quick practical example of how it may be used in a Web app to redirect the user to the proper log-in page, once her profile is loaded.

# First tell DBD::Oracle I want to have the embedded object
# returned as an instance rather than an array. 

$dbh{'ora_objects'} =>1;  

# Next prepare, execute and fetch data from the the query.

$sth = $dbh->prepare("select * from web_user where id=?");
$sth->execute($passed_in_id);  # passed in from request
my ($id1, $web_profile) = $sth->fetchrow();

# finally test the type_name of the $web_profile
# and redirect as needed

if ($web_profile->type_name() eq 'WEB_USERS.EMPLOYEE') {
    # employee is logging in
    print redirect( -location =>'employee.html');
}
elsif ($web_profile->type_name() eq 'WEB_USERS.CUSTOMER') {
    # customer is logging in
    print redirect( -location =>'customer.html');
}
elsif ($web_profile->type_name() eq 'WEB_USERS.TESTER') {
    # tester is logging in
    print redirect( -location =>'tester.html');
}
else {
    # another type of user
    print redirect( -location =>'new_user.html');
}

Quite simple and quite powerful.