Obsessed with Oracle PL/SQL

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

FORALL is a key performance feature of PL/SQL. It helps you avoid row-by-row processing of non-query DML (insert, update, delete, merge) from within a PL/QL block. Best of all, almost always, is to do all your processing entirely within a single SQL statement. Sometimes, however, that isn't possible (for example, you need to sidestep SQL's "all or nothing" approach) or simply too difficult (not all of us have the insane SQL writing skills of a Tom Kyte or a Chris Saxon or a Connor McDonald ). To dive in deep on FORALL, check out any of the following resources: FORALL documentation Videos at Practically Perfect PL/SQL  Tim Hall on Bulk Binds In this post, I am going to focus on special features of FORALL that make it easy to work with space collections: the INDICES OF and VALUES OF clauses. Typical FORALL Usage with Dense Bind Array Here's the format you will most commonly see with FORALL: the header looks just like a numeric FOR loop, but notice: no l...

Collections in PL/SQL make it easy for you to implement lists, arrays, stacks, queues, etc. They come in three flavors: associative arrays, nested tables, and varrays. The three types of collections share many features, and also have their own special characteristics. Here are some tips for making the most of collections. At the bottom of the post, I offer links to a number of resources for diving in more deeply on collections. You Can Query From Collections Collections are, for the most part, variables you will declare and manipulate in PL/SQL. But you can query from them using the TABLE operator (and in 12.2 and higher you even leave off that operator). Use this feature to: Manipulate table data and in-session collection data within a single SELECT. Use the set-oriented power of SQL on your in-session data. Build table functions (functions that return collections and can be called in the FROM clause of a query. Here's a simple demonstration: CREATE OR REPLA...

Oracle Database 12c Release 2 built upon the 12.1 SQL/JSON features by adding a number of builtin object types (similar to classes in object-oriented languages) for manipulating JSON data in PL/SQL blocks. In this post, I explore some of the array-oriented JSON features, all made available through the JSON_ARRAY_T type and its methods. Just like a class, an object type offers a pre-defined constructor function to instantiate new instances of that type, static methods and member methods. Here are the methods you are most likely to use: A couple of things to remember, generally, about working with JSON elements generally and JSON arrays specifically in PL/SQL: Error Handling Behavior By default, if an error occurs when you call a member method for your JSON array (or object), NULL is returned. In other words, an exception is not  raised back to your block. If you want errors to be propagated from the method as an exception, call the ON_ERROR method and pass a value gr...

Collections (Oracle PL/SQL's data structure to implement arrays, lists, stacks, queues, etc.) are not only handy in and of themselves, but are used for many key features of this powerful database programming language, including: High performance querying with BULK COLLECT Super-fast, bulk non-query DML operations with FORALL Table functions (functions that can be treated like a table in a SELECT's FROM clause) PL/SQL offers three types of collections - associative arrays, nested tables, and varrays - each with their own characteristics and ideal use cases. If you are not already using collections on a regular basis in PL/SQL, you are really missing out. Use this article as starting point for accessing a number of useful resources for getting up to speed on collections, and putting them to use in your programs. Documentation The PL/SQL User Guide offers detailed coverage of collection features here . It starts by reviewing the differences between collection...