Obsessed with Oracle PL/SQL

                                 You can take quizzes, workouts and classes at the Oracle Dev Gym solely with the objective of learning and improving your expertise in Oracle technologies. You can also play competitively , which means that you will be ranked when you take a tournament quiz. You can see all the rankings for tournaments by clicking on the Leaderboard tab. But what if you'd like to see how you ranked compared to your co-workers or friends? What if your entire dev team wants to take a workout together and then compare how you all did? I have one answer to those questions: ranking tags! Click on your name in upper right, select Profile Settings. Click on the Ranking Tags tab. It's empty! OK, now it's time to talk to your friends or co-workers. Decide on a tag that you can use to identify your little circle. Aim for something obviously unique. These are "just" tags, so if you use something li...

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

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

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

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

Another Oracle Dev Gym championship completed! Find below the rankings for the PL/SQL Challenge Championship for 2019. The number next to the player's name is the number of times that player has participated in a championship. Below the table of results for this championship, you will find another list showing the championship history of each of these players. Congratulations first and foremost to our top-ranked players: 1st Place: MarkusId 2nd Place: mentzel.iudith 3rd Place: NielsHecker Next, congratulations to everyone who played in the championship. We hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, we will make the championship as a whole available as a workout, so you can take it just like these players did. Finally, our deepest gratitude to our reviewer, Elic, who has once again performed an invaluable service to our community. Rank Name Total Time % Correct ...

You will find below the rankings for the Logic Annual Championship for 2019, held in February 2020 on the Oracle Dev Gym . The number next to the player's name is the number of times that player has participated in a championship. Below the table of results for this championship, you will find another list showing the championship history of each of these players. Congratulations first and foremost to our top-ranked players: 1st Place: Stelios Vlasopoulos 2nd Place: Sartograph 3rd Place: NielsHecker Next, congratulations to everyone who played in the championship. We hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, you can take the quizzes through the Practice feature. We will also make the championship as a whole available as a Workout, so you can take it just like these players did. Finally, many thanks to Eli Feuerstein, Logic Quizmaster who provided a very challenging set of quizzes...