Obsessed with Oracle PL/SQL

Way back in Oracle Database 12c Release 1, the PL/SQL team added whitelisting to the language. This means you can use the ACCESSIBLE BY clause to specify the "white list" of program units that are allowed to invoke another program unit (schema-level procedure, function, package). For details on the 12.1 ACCESSIBLE BY feature set, check out my  Oracle Magazine  article,  When Packages Need to Lose Weight . In that article, I step through the process of breaking up a large package body into "sub" packages whose access is restricted through use of the ACCESSIBLE BY  feature. I'll wait while you read the article. Tick, tock, tick, tock.... OK, all caught up now? Great! In 12.2, there are just one enhancement, but a really nice one: You can now specify whitelisting for a subprogram within a package.  This is a very nice fine-tuning and is sure to come in handy. In this post, I also show how you can specify the "unit kind" (program unit type) ...

Oracle Database raises an exception when something goes wrong (examples: divide by zero, duplicate value on unique index, value too large to fit in variable, etc.). You can also raise exceptions when an application error occurs (examples: balance too low, person not young enough, department ID is null). If that exception occurs in your code, you have to make a decision: Should I handle the exception and let it propagate out unhandled? You should let it propagate unhandled (that is, not even trap it and re-raise) if handling it in that subprogram or block will not "add value" - there are no local variables whose values you need to log, for example. The reason I say this is that at any point up the stack of subprogram invocations, you can always call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and it will trace back to the line number on which the error was originally raised. If you are going to handle the exception, you have to make several decisions: Should I log the e...