Obsessed with Oracle PL/SQL

The SYSDATE function is available in both SQL and PL/SQL. They both return the current date-time (down to nearest second) for the database, So it would be reasonable to assume that they "act" the same in both SQL statements and PL/SQL blocks. That would, however, be a bad assumption to make, because in reality: In SQL, SYSDATE is called just once for the entire statement. In PL/SQL, SYSDATE is called every time it is invoked. Wow. Mind blown.  Let's take a look. In the script below, I create a table and insert four rows. Then I create a package that keeps track of distinct dates added to a collection and show those dates. Finally, a function that uses the package. CREATE TABLE tab (id INT) / BEGIN INSERT INTO tab VALUES (1); INSERT INTO tab VALUES (2); INSERT INTO tab VALUES (3); INSERT INTO tab VALUES (4); COMMIT; END; / CREATE OR REPLACE PACKAGE tracker AUTHID DEFINER IS TYPE when_t IS TABLE OF INTEGER INDEX BY VARCHAR2 (100); dates when_t;...

I'd like to start off this post by thanking Connor McDonald , a member of my Oracle Developer Advocates team. I'm a traditional sort of Oracle developer. I learned a long time ago to use row-level triggers to keep track of who created and changed a row and when.  So, for example, for the DG_CLASSES table of the Oracle Dev Gym (which is used both for Dev Gym classes and AskTOM Office Hours), I have these two triggers: TRIGGER dg_classes_bir BEFORE INSERT ON dg_classes FOR EACH ROW DECLARE BEGIN :new.created_on := SYSDATE; :new.created_by := qdb_config.apex_user; :new.changed_on := SYSDATE; :new.changed_by := qdb_config.apex_user; END dg_classes_bir; TRIGGER dg_classes_bur BEFORE UPDATE ON dg_classes FOR EACH ROW DECLARE BEGIN :new.changed_on := SYSDATE; :new.changed_by := qdb_config.apex_user; END dg_classes_bur; Clearly, this kind of auditing has some drawbacks, including: I have no idea what  was changed, just that something was cha...