Obsessed with Oracle PL/SQL

The more things change, the more they stay the same? Or is it: No matter how much we want things to stay the same, they inevitably must change? Or perhaps it's: 2020's been such a horrible year in many respects, let's change things up in 2021 in every way we can! Whichever platitude or desire fits best, at the Oracle Dev Gym , things are about to change. From 2010 to 2014, this website (then known as the PL/SQL Challenge) offered daily quizzes on PL/SQL, plus SQL, Logic, database design, APEX (on and off), Java (for a while). In 2014, when I rejoined Oracle, the PL/SQL quiz also went to a weekly cadence. For 2021, we are going to switch things up a bit more. Starting January 9, the SQL, PL/SQL and database design quizzes will be "converged" into a single weekly Oracle Database quiz. I know that this change may not be very popular with the many players who have, with incredible dedication, taken pretty much every single one of those quizzes over the years.  I ...

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

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get ...

Write code for a living? Feeling kind of lazy? Then maybe you should find a way to generate  some code. That's the focus of this blog post. The bulk processing feature of PL/SQL was introduced a long, long time ago. It lets us, among other things, do this: Lovely, concise syntax. One downside, however, is that the target collection in the BULK COLLECT INTO clause must be indexed by integer (which means all nested tables and varrays, but only INDEX BY - associative array - collections that are index by PLS_INTEGER or variations therein). This means that if you do want to use a string-indexed collection, you need to first "dump" it into an integer-indexed array, and then move it over to a string-indexed array. I've done this, and my code usually looks like this: DECLARE CURSOR c IS select last_name, first_name, employee_id from employees where department_id = 30; TYPE t IS TABLE OF c%ROWTYPE; c_limit CONSTANT PLS_INTEGER := 10...

The PL/SQL engine offers two profiler utilities to help identify performance bottlenecks in your application: 1. DBMS_PROFILER That's the name of the package that provides an API to the profiler that computes the time that your PL/SQL program spends at each line, in each subprogram . Saves runtime statistics in database tables, which you can then query. 2. DBMS_HPROF The hierarchical profiler; this utility reports the dynamic execution program profile of your PL/SQL program, organized by subprogram invocations. It accounts for SQL and PL/SQL execution times separately. Requiring no special source or compile-time preparation, it generates reports in HTML. You can also store profiler data and results in relational format in database tables for custom report generation (such as third-party tools offer). You can find lots more information about these two profilers in the documentation . In our  June 2nd 2020 Office Hours  session, I am very pleased to have Shashank B...