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

On January 14, 2020 at 9 AM Eastern , I am very pleased to hold a PL/SQL Office Hours session on one of the biggest challenges faced by developers setting up automated tests for database code: setup and teardown. No application will ever have zero bugs, but you sure want to keep them to a minimum. The best way to do this is to implement automated regression tests of your code, but "best" as usual does not equate to "easiest." Building and managing tests can be a big challenge, so in this Office Hours session, we will hear from developers who are doing just that. Learn from your peers about the obstacles they faced and how they overcame them. Bring your own stories and your questions, and let's all work together on improving our code quality! For this session, we have two presenters: Deepti Bandari and Jasmin Fluri. Deepti Bandari is a senior software engineer at Fidelity Investments since 2013. Her focus areas include database design and developm...

My #ThanksOGB post: I've been working - and personally benefiting from - Oracle Database technology since 1987. I joined Oracle as a pre-sales consultant, which meant back then I was a techie sidekick for one or more Oracle salespeople. I moved on to various other roles and in 1992 left to become a consultant. Two years later, wrote a book on PL/SQL and have been obsessed with that language ever since. In 1999, I released the first version of utPLSQL - unit test for PLSQL, my version of JUnit. Worked for Quest for many years (bringing Quest Code Tester for Oracle to the market, among other things), and in 2014 rejoined Oracle, where I now lead a team of developer advocates (Blaine Carter, Chris Saxon, Connor McDonald, Dan McGhan). It's been a great life - and I expect it to keep on being such for a while to come. Part of the point of my little historical review, though, is that I was always paid to provide resources to the community (some of them free, like utPLSQL and m...

So I signed up for my Always Free Autonomous Database (AFAD) and a boatload of other cloud services. OK, what shall I do with them? Hmmm....well....as some of you may know, I've gotten very concerned about climate change and human-caused extinctions. I started a project with Vincent Morneau called fabe - for all a beautiful earth - to help all of us reduce consumption, rescue species and reconnect to nature. Check it out at https://fab.earth . Yes, it is an APEX application and it is already running on an Oracle Database cloud service, so....what else? You know the saying "think globally, act locally"? Well, if fabe is global, then my work on invasive species is local, very  local. When I lived in Chicago, I went out to the nearest "wild" spaces I could find and cut back buckthorn trees that didn't belong in Chicago, out-competed native trees for sunlight, and killed off those native species. I rescued trees  - and the literally millions of living...

The Oracle catalog contains hundreds of views (generally referred to as "data dictionary views") that provide information about the objects stored in the database (tables, views, PL/SQL program units, etc.). Several of these views are extremely helpful to PL/SQL developers in analyzing and managing their code. Here are a few examples: ALL_OBJECTS - information about all database objects which can be accessed by the current user. ALL_ARGUMENTS - information about every argument of every packaged subprogram and schema-level program unit for which the current user has EXECUTE authority. ALL_IDENTIFIERS - information about identifiers in program units, gathered by the Oracle Database 11g PL/Scope feature. In this blog post, I explore a change in behavior for ALL_ARGUMENTS (and its USER* and DBA* variants) as of Oracle Database 18c. ALL_ARGUMENTS lists the arguments of the procedures and functions that are accessible to the current user. USER_ARGUMENTS, as you might kn...

I recently received this question in my In Box: Is FORALL faster with Associative Arrays or Nested Tables? Oracle 12.2 documentation  says : "The most efficient way to pass collections to and from the database server is to use associative arrays with the FORALL statement or BULK COLLECT clause." And a blog post  claims  Associative Arrays with "indices of" option is fastest in 10.2. Just wondering if you have noticed any differences and if so, how much faster Associative Arrays are in 12.2 than Nested Tables? Quick Answer There is no significant difference I can see in the performance based on different collection types (with the limited tests I have run). Don't trust performance tests run on very old versions of Oracle Database (e.g,, 10.2). Use the documentation as a starting, not ending, point of your exploration. Try it yourself! Writing and running the code will teach you more than reading the doc or my blog post. If you would like to read fur...

The bulk processing features of PL/SQL (BULK COLLECT and FORALL) are key tools for improving performance of programs that currently rely on row-by-row processing, an example of which is shown below. Use this blog post to quickly get to some of the best resources on bulk processing - from articles to quizzes to workouts to tutorials. LiveSQL Tutorial I offer a 19-module tutorial on all things bulk processing here . I complement the explanations with lots of code to run and explore, along with: Fill in the Blanks: partially-written code that you need to finish up, that reinforces the content of that module Exercises: You do all the coding to solve the stated requirement (be on the lookout for copy/paste opportunities from the module to speed things up). Oracle-BASE Content You can always depend on Tim Hall to offer comprehensive coverage of SQL and PL/SQL features, with straightforward, easy-to-run code snippets to drive the points home. You'll find his coverage of bu...

Huh, what? Make sure my code fails  to compile? Why would I want to do that. Well, suppose that you had a compute-intensive procedure that ran every hour and benefited greatly from full PL/SQL compiler optimization (level set to 3, to take advantage of subprogram inlining and everything else it does). Next, suppose that somehow as the procedure (newly enhanced, fully tested) was being deployed to production, the optimization level was mistakenly set to 0 or 1. This would cause severe performance problems. So in that case, wouldn't it be nice if you could build a "circuit breaker" into that procedure so that the compiler says "No go" even if the code itself compiles just fine? I think it would be nice - and you can accomplish precisely that with the error directive of the conditional compilation feature of PL/SQL. First, here's the code that demonstrates precisely the scenario outlined above. CREATE OR REPLACE PROCEDURE compute_intensive AUTH...

3rd in a series on conditional compilation. See end of post for links to all posts in the series. Do you write code that must run on more than one version of Oracle Database? This is almost always the case for suppliers of "off the shelf" applications. And when confronted with this reality, most developers choose between these two options: Use only those features available in all versions ("lowest common denominator" or LCD programming). or Maintain separate copies of the code for each supported version, so you can take advantage of new features in later versions of the database ("sure to create a mess" or SCAM programming). And let's face it, both have some serious drawbacks. The LCD approach ensures that your code will compile on all supported versions. But you will sacrifice the ability to take advantage of new features in the later versions. That can be a high price to pay. The SCAM approach, well, "sure to create a mess" ...

Waaaaay back in 2010, on April 8 to be specific, I started a website called the PL/SQL Challenge. It featured a daily PL/SQL quiz (yes, that's right - a new quiz every weekday!) and gave Oracle Database developers a way to both deepen and demonstrate their expertise. Players were ranked and competed for top honors in our annual championships. Not quite as waaaaay back, in 2014, I rejoined Oracle Corporation after 22 years away (from the company, not from the technology). The PL/SQL Challenge came with me, and a year later we rebranded it as the Oracle Dev Gym . Today, we offer quizzes on SQL, PL/SQL, database design, logic, Java and Application Express. We've added workouts and classes. Yesterday we celebrated the ninth anniversary of the Dev Gym / PL/SQL Challenge. And my oh my but Oracle Database developers have been busy! Here are some stats from those nine years: Almost 35,000 developers and DBAs have taken quizzes on the site, a total of 1.27M answers subm...

2nd in a series on conditional compilation. See end of post for links to all posts in the series. In the previous (first) post in my series on conditional compilation, I covered use cases and presented some simple examples. In this post, I show you how you can confirm what code is actually going to be executed after compilation. Without conditional compilation, this is of course a silly exercise. The code that is executed is the same as the code you see in your editor. But with conditional compilation, the code that is compiled and therefore runs could depend on any of the following: The version of the database in which it is compiled The values of user-defined conditional compilation flags The values of pre-defined (system) conditional compilation flags, like $$plsq1_optimize_level It can be a little bit nerve-wracking for a developer to not be entirely sure what is going to execute, so we provide the DBMS_PREPROCESSOR package, with its two subprograms: print_...

This question was submitted as a comment in one of my videos today: Do we have to include an exception section for each individual subprogram or can we have a single handler for all subprograms? The quick answer is: if you want an exception raised in a procedure or function defined in a package, you need to add an exception to that  subprogram. I can certainly see why this question would come up. A package body can  have its own exception handler. Here's an example: CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER IS PROCEDURE proc; END; / CREATE OR REPLACE PACKAGE BODY pkg IS PROCEDURE proc IS BEGIN RAISE NO_DATA_FOUND; END; BEGIN NULL; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('Was proc executed?'); END; / And it kinda, sorta looks like if I execute the following block, I will see "Was proc executed?" on my screen. BEGIN pkg.proc; END; / But I would be wrong. Instead, I will see: ORA-01403: no da...

DatelineDB: April 1st 2019 The European Union turned heads today with a surprise announcement: Starting 1 January 2020, all business logic in applications must be made available via code stored inside the database. While we recommend that you use Oracle Database and PL/SQL , that will not be required. This position was apparently taken after close review of the groundbreaking research conducted by Toon Koppelaars of Oracle Corporation, in which he showed that by putting business logic in the database, the overall work - and therefore energy consumption - of the application is reduced, sometimes by as much as 235%. While improving the overall performance of the application by 500%. A close confidant of the President of the European Union told DatelineDB that the EU would soon adopt a resolution stating that we are now in a climate emergency and every effort must be made in every aspect of human activity to slow down the warming of our planet. "So the decision to requ...