Obsessed with Oracle PL/SQL

There are special member methods -  map  or  order  methods - that we use to tell Oracle Database how to compare two objects of the same datatype. This capability is critical when we want to perform an equality test in PL/SQL or when sorting objects in SQL. There is no default way to do this. In other words, if I create a simple object type, add it as a column to a table, and try to compare or sort, all I get are errors. Let's take a look. First I will create a table that has an object type as a column and add a couple of rows. CREATE TYPE food_ot AS OBJECT ( name VARCHAR2 (100), food_group VARCHAR2 (50), grown_in VARCHAR2 (100) ) NOT FINAL / CREATE TABLE meals ( served_on DATE, main_course food_ot ); / BEGIN INSERT INTO meals (served_on, main_course) VALUES (SYSDATE, food_ot ('Shrimp cocktail', 'PROTEIN', 'Ocean')); INSERT INTO meals (served_on, main_course) VALUES (SYSDATE + 1, food_ot ('House Salad...

Packages have subprograms (procedures and functions). Object types have methods. Object type methods are, still, procedures and functions. But there are also different types and characteristics of methods that only make sense in an object type, which supports inheritance and dynamic polymorphism. In this post, 3rd in my series on object types, I explore Static methods  Member methods  Non-instantiable methods  Invoking methods of super types All the code you see below can be run in Oracle LiveSQL through this script . Member Methods Member methods are methods applied to an instance of the type. Almost all the methods you ever write for an object type will be a member method. Assuming you are already familiar with writing PL/SQL functions and procedures, the most important thing to come up to speed on is the SELF value. Member methods have a built-in (implicit) parameter named SELF that denotes the object instance currently invoking the method. We'll...

In my first post on object-oriented programming in PL/SQL, I introduced the object type (our version of a class) and showed how you needed to instantiate an object type instance before you could work with it. In this post, I explore how to create a hierarchy of types. Type or class hierarchies are a key concept in object-oriented programming, because of the way that subtypes inherit attributes and methods from their parent types. All the code you see below can be run in Oracle LiveSQL through this script . Let's revisit the root type of my hierarchy: food. CREATE TYPE food_ot AS OBJECT ( name VARCHAR2(100), food_group VARCHAR2 (50), grown_in VARCHAR2 (100) ) NOT FINAL ; A very simple type: it contains just three attributes and no methods (procedures or functions). It also contains the NOT FINAL clause. This means, rather obviously, that I am "not done." What that means in the context of object types is that I might want to create a ...

PL/SQL is a procedural language - mostly. But it can also be used to implement object-oriented features in Oracle Database. In "from the ground up" object-oriented languages like Java, everything is defined in classes. In Oracle Database, we have object types . In this post, I introduce you to the basics of object types, and kick off a series  exploring many of the features of these programming elements. You do not  need to be an expert in object orientation to understand the basics of object types. I know this for a fact, because have no formal training in object orientation and was able to sort out at least the basics. Before proceeding, though, you might wonder when you would want to use object types. Use cases include: You'd like to apply object-oriented principles in your PL/SQL-based development. This makes sense if you are an experienced O-O developer and new to PL/SQL. You want to use the many features of Oracle Database that rely on object types for th...