Implicit Cursors are from Venus, Explicit Cursors are from Mars

Domestic bliss. There’s nothing like it. There’s certainly nothing like it in our house, particularly when I indulge in one of my endearing little foibles that is guaranteed to get Deb pouting like Angelina Jolie suffering a nasty reaction to a wasp-sting.
Whether it’s leaving the toilet seat up, or hanging my clothes up on the nearest floor, there are some days where I just can’t seem to do anything right.
Having said that, I must confess that I myself, am not a model of toleration. My own personal pout trigger is a query that looks something like this :

SELECT NVL(COUNT(*), 0)
FROM some_table;

I’ve seen this quite a bit recently, usually in the form of an explicit cursor.
Once I’ve got the rant about this out of my system, I’ll then look at how you might make single-row sub-queries a bit more efficient without ending up knee-deep in implicit cursors.
I’ll also ponder what it actually is that we really know about cursors. Continue reading

Getting APEX to play with Ref Cursors

It’s that time of year again. Things are a bit tense around the house.
The other morning, I woke up to find that someone had placed a leek in my slippers.
Yes it’s Six Nations time again. England are playing Wales on Saturday. The lovely Debbie is getting into the spirit of the occasion…by exhibiting extreme antagonism to all things English.

Whilst the patriot in me would like to cheer on the Red Rose on Saturday, I have decided that discretion ( or in this case, cowardice) is the better part of valour and will instead, sit quietly in the corner, hoping for a draw. That way, I’ve not sold out completely and next week will be far more pleasant if Wales have not lost.

For those readers who know Rugby Union as merely another one of those odd games that we English let our former colonies win at, all you need to know is, the Welsh take this sport very seriously.

In the meantime, I’m trying to keep a low profile, which means playing around with APEX 4.1.

The heady excitement of discovering the first decent GUI development environment for PL/SQL programmers since Oracle Forms is now starting to be replaced by some of the harsh realities of modern web development.
For example, how can I reuse all those terribly useful functions that return Ref Cursors ?
I mean, they work fine in PHP and various other languages, and APEX itself is written in PL/SQL. Should be easy, shouldn’t it ?

Er, no.

APEX simply refuses to play. “I laugh in the face of your weakly typed Ref Cursor” it seems to say. Clearly, some persuasion is required if I’m not to end up with a lot of code locked away in my APEX application, unusable by any other programming language I might want to use to build a web front-end for my database.
The way to an APEX application’s heart is, as will become apparent, through Pipelined functions. Continue reading

Nested Tables – Flat-packed data in an Oracle Table

In the aftermath of the holiday season, there follows the inevitable January sales.
This year, I have been spared the inevitable trudge around the stores. Deb has hurt her knee and has therefore been restricted to browsing on-line.

I thought she “kneeded” cheering up, but to date, my attempts at lightening the mood, seem only to have given her the “kneedle”.

Sitting quietly, whilst Deb is wandering through various furniture store websites, I had cause to reflect on Oracle’s own version of Nested Tables.
These were introduced way back in Oracle 8, when Oracle confidently predicted that the Object-Relational Database was the way of the future.
Imagine if they were just bringing this feature out now. You can picture it. Larry would have spent months making disparaging remarks about IKEA’s occasional table range, before unveiling his own version, which was better, cheaper and more efficient.

Whilst you’re never going to be able to rest your pint on one, a Nested Table in Oracle may be useful on occasion. Continue reading

Upgrading to APEX 4.1 on XE 11g

It’s that time of year. Slay bells ringing, children singing…and the UKOUG Conference.
This year, I was lucky to get along to attend the last day in the company of my good friend Alan.

I love going to the Conference. You get the chance to see lots of great presentations about all sorts of things in the Oracle world.
Takeaways from this year? Well, apart from the stress-ball and the cuddly Rhino ( yes, we did have a wander through the exhibition hall as well), I learned quite a bit about Application Express.

Just in case they’re struggling for an angle for APEX in the Oracle marketing department, how about :
“Application Express – Forms 3.0 for the Internet Age”

I suppose I’d better do some explaining fairly quickly before I am taken to task by any APEX aficionados who happen to be reading.

Back in the good old days, when I still had hair, Forms 3 was the character based interface for the Oracle database. A major advance on Forms 2.3, you were able to code actual PL/SQL right into the triggers. Of course, everything ran on the server back then. Forms, the database ( we don’t talk about SQL*Reportwriter…ever !)

APEX has certain similarities to it’s ancestor. The code is stored in the database itself and you can write PL/SQL in it. Of course, it is also “web-aware”. It could easily be thought of as a UI for SQL and PL/SQL…without all that mucking about with Java.

Enough of this Oracle Tech naval gazing. The point of this post is that, if you’ve downloaded Oracle 11g XE, you will have APEX4.0 included. Due to the tiresome reluctance of software vendors to use major release numbers, you may have been under the misapprehension that APEX 4.1 was just a minor tweak. The truth is a rather different.

APEX is maturing rapidly. So, if you’re running XE 11g on a Debian OS ( or even 10g XE), you may very well be interested in getting the latest version of APEX to have a play with…

NOTE – I ran this installation on 11g XE running on Mint.
I’ve tried to highlight any differences you may get when installing on 10gXE, but I haven’t actually done the installation on this database version. Continue reading

Installing Oracle 11gXE on Mint and Ubuntu

Things have been a bit hectic lately. What with putting in a new kitchen, being insanely busy at work, and trying not to come out with embarrassing sheep jokes, I’ve ended up with quite a long list of things to do blog-wise.
Top of the list, until now, was installing the long-awaited Oracle 11gXE Release 2 onto one of my Linux machines.
Yes, the free version of Oracle’s RDBMS has finally had an upgrade from 10g and I really want to get my hands on it and have a good nose around.
As well as being based on the latest release of the RDBMS, the Express Edition has had one or two other improvements added. Maybe the most significant of these is that the limit for the amount of user data that XE can hold has been increased from 4GB to 11GB.
What I’m going to do here is :

  • Go through the package conversion process
  • Install the database using steps applicable both to Mint and Ubuntu ( and any other Debian based distro)
  • Apply some finishing touches so that the menu items work as intended
  • Along the way, we’ll find out just why Oracle can’t speak English (and lots of other languages), where Mint has hidden the .bashrc, and how Aliens can be friendly.

Because I’m trying to cover both distros in this post, the installation process will be done entirely on the command line. Don’t worry, it’s not as bad as it sounds.

But first…a small morsel of Linux history. Debian, the distro upon which both Mint and Ubuntu are based, was named after a Deb. I had to mention that as this will cause my beloved to think that I’m writing about her ( again), and thus give me enough time to finish writing this !

What are we waiting for then ? Let’s get going. Continue reading

Speed Dating – NLS_DATE FORMAT in Oracle

When dealing with dates in a program, I’ll tend to err on the side of caution and explicitly do the conversion from a varchar to a date, specifying the format so there’s no chance of anything unexpected happening if the program should run in a session with a different NLS_DATE_FORMAT from that I’m using.
However, if for example, I need to do a one-off data fix, sometimes, I just can’t be bothered with all that typing.
Continue reading

Help – DBMS_SCHEDULER keeps Spamming me…and can’t tell the time either

Sundays – a day of rest. Certainly true for me. Sunday morning is a time for lazing around leafing through the colour supplements and thinking about nothing in particular. Sunday 23rd October was a little bit different.
Wide-awake at 8 am ( I didn’t know that there was such a time as 8am on a Sunday), like several million others, I was wondering what would confront the All Blacks – the Gallic flair with which France had swept aside England or the Gallic shrug with which they had surrendered to Tonga ?
Look, I’m not really a New Zealander. Yes, I was born in Auckland but both my parents are English and I’ve lived most of my life in England. However, like anyone with a connection to the Land of the Long White Cloud, there is a part of my soul, however small, that takes the form of a Rugby ball.
At the end of the match, I was able to join my “fellow” Kiwis in, not so much paroxysms of joy as a huge collective sigh of relief.

On the whole though, I’d rather not have to see Sunday morning from that early on. So, if there is, for example, something that needs to run on my database on a Sunday morning, I’d rather the database just did it without my intervention.

What I plan to do here is :

  1. set up a scheduler job
  2. explore the ways in which we can control whether a class of job runs on a given database
  3. stop jobs running on database startup
  4. teach the scheduler how to tell the time – especially in terms of daylight saving

Continue reading