Oracle PL/SQL Objects

This post gives a high level overview of how one could use Oracle PL/SQL Objects. Much of this post was inspired by Steven Feuerstein’s Chapter 26 of Oracle PL/SQL Programming, 5th Edition. Buy it. It is a good book for a PL/SQL developer.

Let’s jump into it and create some silly Objects to play with.

[sourcecode language=”sql”]
CREATE OR REPLACE TYPE animalType AS OBJECT (
name VARCHAR2(30),
MEMBER FUNCTION makeNoise RETURN VARCHAR2,
MEMBER PROCEDURE printNoise
) NOT INSTANTIABLE NOT FINAL;

CREATE OR REPLACE TYPE BODY animalType
IS
MEMBER FUNCTION makeNoise RETURN VARCHAR2 IS
BEGIN
RETURN ‘‘;
END;

MEMBER PROCEDURE printNoise AS
BEGIN
dbms_output.put_line(name || ‘ says ‘ || self.makeNoise );
END;

END;
[/sourcecode]

Our base type, that can’t be instantiated but that can be extended by other sub types.

Now here are the sub-types that can be instantiated.

Now let’s try the object out. Please note the polymorphic behavior at work.

You should see something like the following in our dbms_output buffer.

sheep says baaahh
lion says roar

The geek in me says “Neat so far!” The skeptic in you says how do we save the object data? “Easy!” says I. Let’s create DAO, Data Access Object, in the form of a package. This example shows the data being stored in an object column.

Brilliant! Let’s save some beasties to the database:

Even better let’s select that data from its table.

Object columns are neat, but Object tables are even better. Let’s re-write the DAO to use an object table instead.

Let’s give the new DAO a spin:

Of course, Oracle gives us all kinds of ways to query the meta data about the objects.

OK. What if you need to pull your objects from an existing legacy table?

Insert some new data into our “legacy” table.

How do the views work. Try these queries to see:

References:

One Response to “Oracle PL/SQL Objects”

  1. ALEJANDRO Says:

    < blockquote > GENERIC PHARMACY : < b >-==== < a href=”http://pillsspot.comsyhost.com/order_thyroid_en-us.html” >Thyroid Pills < /a > ====-< /b >< /blockquote >…

    Purchase Cheap Generic Drugs Now!…