Obsessed with Oracle PL/SQL

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

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 the same query: SELECT * FROM patients and only thei...

I posted the following puzzle on Twitter : What change(s) can you make to this code so that "ORA-00001: unique constraint" appears on the screen after execution? Try it yourself before reading the rest of the post! White space so you do not immediately see my answer.  :-) Here are the answers from the TwitterSphere: Change line 5's assignment to dbms_sql.number_table(1=>1,2=>1) In other words, try to insert the same value twice. Since there is a unique index on the column, that will cause ORA-00001 to be raised. So that will do it, right? Wrong. Hans and Dirk both point out why that is not enough, and offer the second part of the solution: The value deposited in the error_code field of the SQL%BULK_EXCEPTIONS array is unsigned . In other words, 1 rather than -1 is stored. Unfortunately, the SQLERRM function assumes that the error code you pass it will be signed (negatively). So you must multiply the value in the pseudo-collection by -1. Then...