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

You are writing PL/SQL code to provide secure, high performance access to your data and implement business rules. [reference: Why Use PL/SQL? ] Right? Good. And you use collections (associative arrays, nested tables, arrays) because they offer all sorts of great functionality. [reference: Collections in PL/SQL YouTube playlist ] Right? Good. So here's a quick reminder about COUNT, one of many methods available for collections (others include DELETE, FIRST, LAST, NEXT, PRIOR, TRIM, EXTEND): It works pretty much like COUNT in SQL. If the collection is empty, COUNT returns 0, not NULL. If you try to "read" an element at an undefined index value, Oracle Database raises NO_DATA_FOUND. Just like a SELECT INTO that identifies no rows. If you check to see if a collection is empty with a call to COUNT, it doesn't raise NO_DATA_FOUND. To verify what I've said, and to have a bit of fun while doing it, you can take a quiz on this topic at the Oracle Dev Gy...