Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: plsql
The definition of proof
One of the pieces of advice that I often see on the ‘net is that undo space is somehow this incredibly precious thing, and as a consequence, one should always keep the amount of uncommitted changes in the database to a small size. Personally…
Same dog, different leash – functions in SQL
Let’s start with this somewhat odd looking result. I have an inline function that returns a random number between 0 and 20, and I call that for each row in ALL_OBJECTS and then I extract just those rows for which the generated random number…
SMON_SCN_TIME and ORA-8161? Digging deeper
In the recent versions of the Oracle database, we’ve had the ability to convert between a System Change Number (SCN) and the approximate time to which that SCN pertains. These functions are unsurprisingly called SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN. The only potential misnomer here is that…
With and without WITH_PLSQL within a WITH SQL statement
OK, let’s be honest right up front. The motivation for this post is solely to be able to roll out a tongue twisting blog post title . But hopefully there’s some value as well in here for you if you’re hitting the error: ORA-32034:…
Generic data models … generic applications … ugh
There’s a hesitation to publish this example, because publishing it may be interpreted as an endorsement of this approach and it certainly isn’t. Over the years there have been plenty of articles describing the long term pain that typically comes from generic data models….
PL/SQL – Don’t mix and match scope
Here’s a simple little PL/SQL block where we call an inner procedure PARAMETER_TESTER from its parent block. Pay particular attention to the parameter we pass to the procedure, and it’s value throughout the execution of that procedure. SQL> set serverout on SQL> declare 2…
Quick and easy masking
I had a request from a client a while back regarding masking of data. They had an application with sensitive data in the Production environment (where access and audit were very tightly controlled) but the issue was how to respect that sensitivity in non-Production…
