Skip to main content


Implementing Enhanced Table Auditing in the Oracle Dev Gym

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 changed.Those tri…
Recent posts

The differences between deterministic and result cache features

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 the sa…

Generate code to move rows to string indexed collections

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 := 100; l_rows_by…

Office Hours June 2020: Exploring the PL/SQL Profilers

The PL/SQL engine offers two profiler utilities to help identify performance bottlenecks in your application:


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.


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 Barki, an experienced d…

Virtual Private Database: Beyond the Basics

Virtual Private Database (VPD), also referred to as row-level security or RLS, is a feature built into the Oracle Database that allows you to set up security policies on tables that restrict which rows a user can see or change based on the policy logic.

One of the nicest things about VPD is that this logic (and the fact that a filter is being applied) is completely invisible to the user. They just see the data relevant to them and none the wiser about all that other data in the data.

Here's a simple example to drive the point home: suppose I am building a health care application and it contains a patients table. The security policy is straightforward:
A patient can only see their own information.A doctor can see only the information about their own patients.A clinic administrator can see information only about the patients in their clinic. In all three cases, the user would sign on to the application and execute this identical query and only their rows would appear.


Why DBMS_OUTPUT.PUT_LINE should not be in your application code

A database developer recently came across my Bulletproof PL/SQL presentation, which includes this slide.

That first item in the list caught his attention:
Never put calls to DBMS_OUTPUT.PUT_LINE in your application code. So he sent me an email asking why I would say that. Well, I suppose that is the problem with publishing slide decks. All the explanatory verbiage is missing. I suppose maybe I should do a video. :-)

But in the meantime, allow me to explain.

First, what does DBMS_OUTPUT.PUT_LINE do? It writes text out to a buffer, and when your current PL/SQL block terminates, the buffer is displayed on your screen.

[Note: there can be more to it than that. For example, you could in your own code call DBMS_OUTPUT.GET_LINE(S) to get the contents of the buffer and do something with it, but I will keep things simple right now.]

Second, if I am telling you not to use this built-in, how could text from your program be displayed on your screen?

Not without a lot of difficulty, that's fo…

An Application Alerting Utility

A few weeks ago, Mike Hichwa asked me to come up with a package that would implement a simple alerting utility: specify a triggering event (based on a query or a PL/SQL expression) and then take the specified actions when it is triggered.

Seeing as he is my boss, I said "OK" and got to work (my favorite kind of work: writing some PL/SQL code). We did our usual bit to expand scope, then did our usual bit to agree that this is enough for a first pass. And then Mike said: "Go ahead and write a blog post about it, share it with the community."

So here goes.

The basic idea is we start up a job using DBMS_SCHEDULER that runs every N minutes. When it runs, it checks to see if any alerts need to be triggered. If so, it then takes one or more actions associated with that alert.

Let's start with our tables.
Utility configuration How often should the job wake up? Should it disable checking for alerts? What is the name of the "callout" for sending emails? And if…