Skip to main content


Different SYSDATE behaviors in SQL and 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;
Recent posts

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 chan

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

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 := 10

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: 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 Barki,

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 difficu