Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: plsql
The death of UTL_FILE
In a previous post I covered a technique to improve the performance of UTL_FILE, but concluded the post with a teaser: “you probably don’t need to use UTL_FILE ever again”. Time for me to back that statement up with some concrete evidence. UTL_FILE can…
When WHEN went faster
Yeah…try saying that blog post title 10 times in a row as fast as you can But since we’re talking about doing things fast, this is just a quick post about a conversation I had a twitter yesterday about the WHEN clause in a…
Juicing up UTL_FILE
Think about your rubbish bin for a second. Because, clearly this is going to be an oh so obvious metaphor leading into UTL_FILE right? OK, maybe a little explanation is needed. I have a basket next to my desk into which I throw any…
“Call me!” Many many times!
Some readers might recall that classic Blondie track “Call me”. Of course, some readers might be wishing that I wouldn’t harp on about great songs from the 80’s. But bear with me, there is a (very tenuous) link to this post. If you haven’t…
Concurrency … the path to success and the path the failure
Let’s face it. Concurrency is a good thing when it comes to database applications. After all, if there is only a single user of your application, then chances are, it is not a successful application . Of course there are exceptions to this rule,…
NULL’s vs NOT NULL’s and Performance
When it comes to giving the cost based optimiser the best possible chance to make the “right” decisions, many DBA’s are diligent in keeping statistics up to date, using histograms where appropriate, creating more indexes (or removing surplus indexes). However one often neglected area…
Dealing with IP addresses
Kris Rice made mention on Twitter about the free (but limited lifetime) IP address to Country mappings that you can get from Maxmind. If you ever need to deal with IP addresses, you might find the following utility package I wrote a while back…
“Oh…another language is too hard”
We had a request on AskTOM a few days ago asking for an implementation of the XIRR function in PL/SQL. I didn’t really know much about what that function was, or what it did, but a quick web search yielded plenty of examples in…
Instrumentation … not just for debugging
Yeah I know. You’re probably thinking “Here’s another blog post from someone telling us how important it is to instrument our code, so we can get better debugging, better performance, blah blah blah”. If that is your sentiment, then I’m thrilled because it means…
Haversine PL/SQL
I didn’t see a PL/SQL version on https://rosettacode.org for the Haversine formula so here’s a version for anyone that needs it. SQL> create or replace 2 function p2p_distance( 3 p_latitude1 number, 4 p_longitude1 number, 5 p_latitude2 number, 6 p_longitude2 number) return number deterministic is…
Simple demo of message propagation
If you’re using AQ, then it’s simple to setup simple enqueue and dequeue facilities on your local database to provide all sorts of asynchronous style processing in your applications. As long as you’re applications are designed and built to handle it, the “fire and…
BULK COLLECT into nested table
I had an observation come to me last week about PL/SQL and populating nested tables. “The BULK COLLECT into statement cannot be used repeatedly to append results into a table. Instead, it silently truncates the target table each time. “ This is true. However,…
