Oracle – for when it was like that when you got there

Main menu

Post navigation

ROWNUM, Random updates and the Alert Log – A trip down Memory Lane

I met up with an old friend recently. Deb and I were in Toronto, hometown of a certain Simon Jennings.
Apart from being a top bloke, Simon was also my first mentor in the mysterious ways of SQL, a sort of Obi-Wan Kenobi to my Anakin Skywalker, but without the light-sabers.

Ah those heady days when the world was young…and I first discovered ROWNUM.
That pseudo column was one I used often when investigating the structure and data within tables. It was also, the source of some confusion.

Here we can see the “catch” with relying on ROWNUM. It’s applied to the result set of the query before the ORDER BY. If we want to associate ROWNUM to the result set in order we’ll have to do something like :

Since the days of Oracle 6, analytical functions have come on leaps and bounds. Let’s face it, since those days, dinosaurs have become extinct and this new-fangled interweb thing has taken over the planet.
In the intervening years, ROWNUM has all but disappeared from my SQL vocabulary.
Recently however, I’ve come across a couple of instances where this venerable pseudo-column has come in handy.

Sort of Random

Let’s imagine for a moment that I’m planning my next trip to Canada.
I want to catch some NHL action whilst I’m over there but can’t decide which teams I want to see.
I want to pick two teams to see, more or less at random, but I don’t want to drop into PL/SQL…

SQL> SELECT
2 CASE MOD( ROWNUM, 3)
3 WHEN 0 THEN 'Going to see '
4 ELSE 'Not going to see '
5 END
6 || team_name
7* FROM hockey_teams
SQL> /
CASEMOD(ROWNUM,3)WHEN0THEN'GOINGTOSEE'ELSE'NOTGOINGTOSEE'END||TEAM_NAME
--------------------------------------------------------------------------------
Not going to see TORONTO MAPLE LEAFS
Not going to see MONTREAL CANADIENS
Going to see OTTOWA SENATORS
Not going to see WINNIPEG JETS
Not going to see CALGARY FLAMES
Going to see EDMONTON OILERS
Not going to see VANCOUVER CANUCKS
7 rows selected.
SQL>

OK, so it’s not that random. However, if we take another example, the value of ROWNUM becomes a bit more apparent.

Say I want to test some new functionality on the HR schema – one of the example schemas that ships with the Oracle database.
Here, I want to account for employees that are paid either weekly or monthly.
I’ve written some fancy Payroll routine to identify and process all of the appropriate employees at the appropriate time. This imaginary routine relies on a flag set on a new column in the employees table.
The value of the colum must be either ‘W’ for weekly or ‘M’ for monthly.
Most employees will be paid Monthly, but I do want some set to Weekly to make sure I test both possibilities. At this point, it doesn’t really matter which records are assigned which value.

The table contains 107 records. Now we want to set the PAY_FREQUENCY. We want to have around 10% of the rows to be set to ‘W’ and the rest to ‘M’.
Now, we could mess around with PL/SQL and it’s fancy cursor for loops…or we could just do this…

Alert Log as an External Table

For the other example of ROWNUMs continued usefulness, I’m going to use an external table. Yes, I know what you’re thinking after my last post, when you’ve got a hammer, then everything looks like a nail.

Consider the DBA. First thing in the morning, the sugar rush from all that Maple Syrup has long since worn off and the caffeine from that first cup of coffee is still negotiating it’s way from the mug to his or her brain.
Whilst in this state of chemical imbalance, our DBA needs to make sure that all is well after last night’s batch runs.
Part of this morning ritual may well involve wading through the alert log.
Now wouldn’t it make things just that little bit easier if we could get Oracle to do the thinking for us and just flag up any relevant alert log entries ?

Well, we know we can take a flat-file and treat it as an external table. Couldn’t we do the same with the alert.log ?
Er, Yep.

Lovely, we have the alert log in the database….except there’s no key so just querying the bit you want is still a bit fiddly. On the plus side, the rows seem to be read in in correct order by default ( i.e. the order in which they are read from the file) so the result set from the query will be in the correct order, even though no ORDER BY clause is specified.NOTE – that last bit is supposition on my part. That’s the way it appears to be working, but I haven’t found anything in the Oracle documentation to confirm it.

But where does ROWNUM fit into all this, I hear you ask ? Well, if you want to query the alert log for a specific time window, you can do something like this :

That’s better. We can now just return the rows of the alert log that have been written since the start of yesterday. However, we may well want to rely on something a bit more precise that just yesterday’s date.

At this point, it’s probably worth looking at the incredibly useful KSDWRT.
Gesundheit ! I hear you cry. Thanks, but no, I haven’t just sneezed, I’m referring to DBMS_SYSTEM.KSDWRT.

Despite having a name that looks like you’ve just leaned on the keyboard, this procedure does have the incredibly useful ability to write a custom message to the alert log…

BEGIN
DBMS_SYSTEM.KSDWRT(2, 'Oooh, a message');
END;
/

Now, if we look in the alert log for this message…

SELECT line_no, line
FROM(
SELECT ROWNUM as line_no, line
FROM alert_log_ext)
WHERE line LIKE '%Oooh, a message'
/
LINE_NO LINE
---------- ----------------------------------------
38989 Oooh, a message

With an appropriate message, we can narrow down our query to just the bit of the alert log that we’re interested in.

If we return to our bleary-eyed DBA, we could help out by simply writing a message to the alert log at the start of the overnight batch window.

2 thoughts on “ROWNUM, Random updates and the Alert Log – A trip down Memory Lane”

On the plus side, the rows seem to be read in in correct order by default ( i.e. the order in which they are read from the file) so the result set from the query will be in the correct order, even though no ORDER BY clause is specified.

NOTE – that last bit is supposition on my part. That’s the way it appears to be working, but I haven’t found anything in the Oracle documentation to confirm it.