Getting output from Ref Cursors in PL/SQL

A colleague of mine (Martin, you know who you are), remarked the other week that he wasn’t overly interested in the contents of the blogosphere. He said that it usually put him in mind of the cartoon of the tag-cloud consisting solely of the word “me”. This got me to thinking, why do I do this ?
Let’s put my ego to one side for a moment ( pause to sounds of straining, followed by a dull thud). That was heavier than it looked.

One of the reasons for maintaining this blog is that I’ve got a quick reference to look at if I come across something I did a while ago and need a quick reminder of syntax etc. Also, my Mum likes to know what I’m up to.
The starting point for this entry was to attempt to drag together all the basic bits about Ref Cursors in PL/SQL – specifically, accessing them from within PL/SQL itself.

Whilst I was writing this, it was pointed out to me that SQLDeveloper doesn’t handle Ref Cursors quite as nicely as Toad. The specific issue was the difficulty in dumping the results into a grid, from whence it can be transferred to Open Office Spreadsheet ( or Excel).

For the most part, Ref Cursors are used to transfer data from the database to a web application. So, why would you need to start fiddling about with getting results back in PL/SQL ?
There are probably several answers to this question. However, for me, it’s mainly a case of having to trace problems raised in various support calls. Knowing what data results from each of the calls in a process usually helps a bit. Continue reading