Obsessed with Oracle PL/SQL

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

A long, long time ago in a blog long ago lost to the shrouds and clouds of memory, I published a Ten Commandments of PL/SQL Development. A Twitter follower recently asked me for them, and lo and behold actually found them lurking in a Word doc on my drive. So I have decided to share them with you, largely unchanged. In some places I have struck through entirely irrelevant or outdated text, and also offered updates for 2019. 1. Thou shalt encapsulate your SQL statements behind procedure and function calls. Sure, it's really, really easy to write SQL in your PL/SQL programs -- in fact, it's way too easy. SQL is the "Achilles' heel" of your application code. You code and you code and you code, and before you know it, you have dozens of variations of the same SQL statement making their negative performance impact known in your application. You can't analyze the impact of data structure changes and you find enhancements to be very expensive. Solution? Put all ...

I was asked the following question via a Twitter DM today: I stumbled upon a mention of " SQL Guard " and I was wondering what has become of it. Google confused me. Has it been published under some other name, or is it part of a bigger product? Great question! I sort of vaguely remembered creating something like that. So I searched around in my folders and found....SQL Guard! NOTE: SQL Guard is not a product I offer anywhere on the Internet. There are things out there called SQL Guard. I am not challenging their copyright or trademark or anything else. What you find will find below is the document I wrote in 2007 explaining my ideas and code. I am making it available AS IS through this site and a set of LiveSQL scripts, links below. I make no warranty about usability or usefulness or even basic validity of this code. If it helps you great. If it destroys your database I WARNED YOU. LiveSQL: create tables LiveSQL: create package specification LiveSQL: create packa...

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