Tag: plsql

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,…

Why PLSQL ?

With Collaborate 2016 under way, there seems no better time to reflect on why PL/SQL is the natural choice for anyone who loves to code, and loves their data

Dealing with URL’s

If you are creating or handling URL’s, there is a nice little package routine that assists with handling all those pesky special characters. I’ve added a little wrapper just to make the parameter handling easier SQL> create or replace function utl_url_escape(x varchar2) return varchar2…

FOLLOWS clause

In early Oracle releases, you could have 1 trigger per type of trigger per table (eg if you had a before-row-insert trigger, you could have only 1). Now there is no limit. But that does raise the question – if I have “n” BEFORE…

Is a year a leap year ?

This post seems timely given that yesterday was Feb 29. In almost every case I can think of, you should be relying on native Oracle date functions to perform any kind of date arithmetic. This is perhaps one of the very very few exceptions…

Loading LOB from a file

I observed this idiosyncracy recently when loading some lob from external files using PL/SQL: First we’ll create a file to be loaded, and ‘wc’ tells us it is 75 bytes in size SQL> !echo “This is line 1” > /tmp/lobfile SQL> !echo “This is…

Public / private cursors

As we all know (hopefully), we should always deploy named program units (procedures/functions) within packages. Its a great method of encapsulation of logic. However, not many are aware that its not just procedures and functions that can have a public (package spec) and private…

Bitwise operations

The long existing BITAND function is now within the documentation, to let you do logical AND on two numbers, and is also available from PL/SQL   If you need other bit operations, a little boolean math should suffice Just make sure you stay within…

Auto-backups of PLSQL source

I saw this on an ideas forum today and whilst most people would take care of this with a source code control system, its also true that people might do several compilations / tests with their PLSQL source before checking it in officially to…

Problematic SQL ? PL/SQL is your friend.

So then… I’ve written a crappy piece of SQL. It wouldn’t be the first time I’ve done it… and it probably won’t be the last time I do it But at least I’ve done one thing right…I’ve encapsulated the SQL inside a PL/SQL procedure….