Obsessed with Oracle PL/SQL

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

I posted the following puzzle on Twitter : A quick little #PLSQL puzzle: I set serveroutput on. After running the code you see in the block below, what will be displayed on the screen? Try it yourself before reading the rest of the post! White space so you do not immediately see my answer.  :-) The output is: 10 1 9 I expect most of you got the first two right and maybe the third one wrong. Note also that the block does not fail with any kind of exception, such as VALUE_ERROR. The reason for all this can be summed up in one phrase: implicit conversion. As noted by several people, this is a collection indexed by strings , not integers. Only associative arrays (INDEX BY) types support this. And that makes all the difference in this puzzle. The value being used in the assignment of 100 to elements in the array is an integer (indx). Since the index type is a string, the PL/SQL engine implicitly converts integers 1 through 10 yto strings "1", "2...

Dynamic means "run-time." Polymorphism means "multiple shapes." Synonyms for dynamic polymorphism include "runtime polymorphism" and "dynamic method dispatch." If you are a "traditional" relational database developer, these terms might sound unfamiliar. But how about overloading ? Are you familiar with that? Overloading occurs when you have more than one subprogram (procedure and/or function) with the same name in the declaration section of a block, package specification or package body. These subprograms need to differ by parameter list or type (procedure vs function) in a way that is sufficient for the compiler to distinguish. Well, guess what? Another name for overloading is "static polymorphism." Static means "compile-time." Polymorphism means "multiple shapes." Why, you might be wondering, does the Oracle Database need to wait till runtime to determine which method in which type in th...

When it comes to PL/SQL puzzles via Twitter, I decided to change things up this week. I presented it as multiple choice this time. Here's the puzzle: After executing the code shown in the image what will be displayed on the screen (serveroutput is on!)? a. "No dummy" b. Unhandled VALUE_ERROR exception c. Unhandled NO_DATA_FOUND exception d. Compilation error e. Your guess is as good as mine. Before I unveil the amazing, mind-boggling answer....I will give you a moment to try to solve it yourself. OK. So the first inclination you might have as regards the output from this block is, quite logically, "No dummy!". After all, there are no rows in the dual table (or any other table for that matter) for which 1 is equal to 2. So that SELECT-INTO is going to raise a NO_DATA_FOUND exception. No doubt about that at all. And there's an exception handler for NO_DATA_FOUND (well, no_data_found, wait they are the same thing in PL/SQL! :-) ). So ...