Tag: plsql

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…