Obsessed with Oracle PL/SQL

Software is constantly evolving: bugs fixed, new features added, and better ways to do things discovered and implemented. A great example of this dynamic from PL/SQL itself is the UTL_CALL_STACK package. This package was first introduced in Oracle Database 12c Release 1, and it improves upon the functionality already provided by the following functions in the DMBS_UTILITY package: FORMAT_CALL_STACK, FORMAT_ERROR_STACK, and FORMAT_ERROR_BACKTRACE. The same thing happens in PL/SQL code that is developed by customers. The now-outdated subprograms (or other elements) of one's API cannot be removed immediately; that would break existing code. But everyone would like to make sure that any new code uses the new API. The new DEPRECATE pragma (compiler directive) in Oracle Database 12.2 will help you accomplish this transition in a smooth, error-free fashion. It provides a formal way to communicate information about deprecated elements [my change ok? SF YES] with a power that ordi...

You are writing PL/SQL code to provide secure, high performance access to your data and implement business rules. [reference: Why Use PL/SQL? ] Right? Good. And you use collections (associative arrays, nested tables, arrays) because they offer all sorts of great functionality. [reference: Collections in PL/SQL YouTube playlist ] Right? Good. So here's a quick reminder about COUNT, one of many methods available for collections (others include DELETE, FIRST, LAST, NEXT, PRIOR, TRIM, EXTEND): It works pretty much like COUNT in SQL. If the collection is empty, COUNT returns 0, not NULL. If you try to "read" an element at an undefined index value, Oracle Database raises NO_DATA_FOUND. Just like a SELECT INTO that identifies no rows. If you check to see if a collection is empty with a call to COUNT, it doesn't raise NO_DATA_FOUND. To verify what I've said, and to have a bit of fun while doing it, you can take a quiz on this topic at the Oracle Dev Gy...

By "Oracle Database development", I mean, more or less, writing SQL and PL/SQL. I assume in this post that you have access to Oracle Database (which you can get via Cloud services, Docker , GitHub and OTN ). A. Use a powerful IDE, designed with database programming in mind. There are lots of editors out there, and many IDEs that work with Oracle Database. Sure, you could use Notepad, but OMG the productivity loss. You could also use a popular editor like Sublime, and then it get it working with Oracle. I suggest, however, that you  download  and install Oracle's own own, free, powerful IDE: SQL Developer . If you like to complement your graphical IDE with a command line tool (or OMG if you actually prefer  a command line tool to a graphical interface), you should also check out the relatively new and generating-lots-of-excitement SQLcl . B. Enable compile-time warnings and PL/Scope. The database has tons of useful functionality burned right into it, ready for...

The best way to build applications on top of Oracle Database is to build lots of APIs (application programmatic interfaces) to your data with PL/SQL packages. And that means you end up with lots of packages in your application. That's just great! Now, when a user selects a feature of your application that in turn references an element in a package (invokes a procedure or function, or reads the value of a constant), that package must be instantiated and initialized for that user's session. As described in the documentation : When a session references a package item, Oracle Database instantiates the package for that session. Every session that references a package has its own instantiation of that package.  When Oracle Database instantiates a package, it initializes it. Initialization includes whichever of the following are applicable: Assigning initial values to public constants Assigning initial values to public variables whose declarations specify them Executing t...