Obsessed with Oracle PL/SQL

So far in my series on object-oriented development in Oracle Database, all manipulation of object type instances have taken place in PL/SQL. But as you may have guessed from the fact that you "CREATE OR REPLACE" object types, those types are also available for us in SQL. You can create relational tables of object types, called object tables. You can also define columns of relational tables whose datatypes are object types. In this post, I will explore both of these approaches. All the code you see below may be found in this  LiveSQL script , so you can get to know these features by playing around with them yourself. Object Tables It's easy to create object tables and work with the instances in those tables (both selecting and changing rows of data). Here's a simple example: CREATE TYPE food_ot AS OBJECT ( name VARCHAR2 (100), food_group VARCHAR2 (50), grown_in VARCHAR2 (100) ) NOT FINAL / CREATE TABLE food_table OF food_ot (CONSTRAINT food_ta...

Find below the list of my blog posts in a series on working with object types (a.k.a, classes) in Oracle Database and, specifically, PL/SQL. This series is not intended to offer an in-depth training on object-oriented development; instead, it introduces basic O-O concepts and shows how they are implemented in Oracle Database with PL/SQL. Introduction to Object Types, Part 1 Object Types and Inheritance, Part 2 Object Types Methods, Part 3 Using Object Types in Relational Tables, Part 4 Comparison Methods for Object Types, Part 5 Dynamic Polymorphism - Why, What, How, Part 6 Each post also contains a link to a LiveSQL script, so you can try out all the code yourself.

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...