There is a frisson of excitement in our household at the moment. It’s not simply because the mighty Hammers on their way to Wembley. Oh no. It’s not even that Luton will be making the same journey with the same aim (i.e. promotion).
In fact, it has little or nothing to do with the end-of-season fun and games in the football world. The truth is, that we are finally moving from sunny Milton Keynes to …er… somewhere else in sunny Milton Keynes.
To mark this momentous occasion, Deb has busied herself with organizing the packing, working out where all the furniture is going in our new house and eyeing a whole range of utensils that will go with our new kitchen.
For my part, I’ve written a PL/SQL routine to apportion Council Tax Liability.
It should be noted that Deb has now applied to change her name to “The Long Suffering Deb” by deed pole.
Apart from exploring the algorithm required to apportion Council Tax ( for those outside the UK, it’s a sort of local tax on all domestic properties), I’ll also have a look at how 11g now allows at least three different ways of calling a database function from SQL.

The Apportionment Algorithm

This algorithm applies equally to Water Rates, Business Rates and Council Tax.
The “tax” year, in this context, runs from 1st April to 31st March inclusive.
To work out how much you actually need to pay up to a given date you need to :

1. Take the amount you are paying for the year
2. Divide by the number of days in the year
3. Multiply by the number of days up to and including the day you move

Well that all seems fairly straightforward…

The function

Here’s the function. We’re passing in three parameters :

• i_move_date – the date that we are moving house
• i_charge – the amount that we are being charged for the year
• i_to_move – Y if we want to work out our bill up until the date we move, N to work out the bill from the date we move to the end of the year

Yes, the only way you’ll get out of paying the Council Tax is if you move somewhere they don’t have it (otherwise known as abroad).
Anyway, the code looks like this :

CREATE OR REPLACE FUNCTION calc_liability_fn(
	i_move_date IN DATE,
	i_charge IN NUMBER,
	i_to_move IN VARCHAR2 DEFAULT 'Y')
	RETURN NUMBER
IS
	l_start_year PLS_INTEGER;
	l_year_start_dt DATE;
	l_year_end_dt DATE;
	
	l_days_in_year PLS_INTEGER;
	l_daily_rate NUMBER;
	
	l_days_to_charge PLS_INTEGER;
	
	e_invalid_params EXCEPTION;
	
BEGIN
	--
	-- Check all the parameters have been passed in and are valid
	--
	IF i_move_date IS NULL OR i_charge IS NULL OR i_to_move NOT IN ('Y', 'N')
	THEN
		RAISE e_invalid_params;
	END IF;
	-- 
	-- Work out the start and end dates for the Council Tax/Rates Year
	--
	IF EXTRACT( MONTH FROM i_move_date) < 4 THEN
		--
		-- The move date is between January and March so the start of 
		-- the year is 1st April in the previous calendar year
		--
		l_start_year := EXTRACT ( YEAR FROM i_move_date) - 1;
	ELSE
		l_start_year := EXTRACT( YEAR FROM i_move_date);
	END IF;
	l_year_start_dt := TO_DATE('0104'||TO_CHAR( l_start_year), 'DDMMYYYY');
	l_year_end_dt := ADD_MONTHS(l_year_start_dt, 12);
	--
	-- Now check to see if this is a leap year. As this program is unlikely to be
	-- around in the year 2100, well just see if the year is divisible by 4. If so,
	-- it's a leap year.
	-- Note - we need to check the calendar year in which the tax year ends.
	--
	IF MOD( l_start_year + 1, 4) = 0 THEN
		l_days_in_year := 366;
	ELSE
		l_days_in_year := 365;
	END IF;
	--
	-- Next we need to work out the daily charge
	--
	l_daily_rate := i_charge / l_days_in_year;
	--
	-- Now check to see if we're returning the cost of the charge from the start of the
	-- year until the move date, or from the move date to the end of the year
	--
	IF i_to_move = 'Y' THEN
		--
		-- Calculate the total charge up to and including the date of the move
		--
		l_days_to_charge := i_move_date + 1 - l_year_start_dt;
	ELSE
	    --
	    -- Calculate the total charge from the day after the date of the move
		l_days_to_charge := l_year_end_dt - (i_move_date + 1);
	END IF;
	RETURN l_daily_rate * l_days_to_charge;
EXCEPTION
	WHEN e_invalid_params THEN
		RAISE_APPLICATION_ERROR(20000, 'Parameters missing or invalid.');
END;
/

“Well that’s nice”, I hear you say, “but what’s this three different ways of calling a function you mentioned earlier ?”

The Good, The Bad, and The Ugly

First off, let’s try the traditional approach :

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';

Session altered.

SQL> SELECT calc_liability_fn( '12-MAY-2012', 1382.06) to_pay
  2  FROM dual;

    TO_PAY
----------
159.031562

SQL> 

OK, so we’re passing in a date, followed by the charge for the year. We’re not bothering with the i_to_move parameter as we want to use the default value anyway.
Now, remember what you’re Mum told you about calling stored program units – always pass by reference, not by position.
Well, up to now, if you tried this in SQL, you would just get a rude message. With 11g however…

SQL> SELECT calc_liability_fn(
  2  	i_move_date => '12-FEB-2012',
  3  	i_charge => 1382.06,
  4  	i_to_move => 'N') to_pay
  5  FROM dual;

    TO_PAY
----------
 181.25377

SQL> 

I can just hear the sharpening of pencils as a myriad of Standards documents are being sized up for a quick edit.
Hang on though, there is another way…

SQL> SELECT calc_liability_fn( 
'09-AUG-2012', 
1382.06, 
i_to_move => 'N') to_pay
  2  FROM dual;

    TO_PAY
----------
886.032986

SQL> 

Yes, it is now possible to use a mixture of pass by reference and pass by position notation in the same call.

I’m not entirely sure why you would ever want to do this, but the fact is, you can if you are so inclined…except when you can’t…

SQL> SELECT calc_liability_fn(
'09-AUG-2012', 
i_charge => 1382.06, 
'N') to_pay
  2  FROM dual;
SELECT calc_liability_fn('09-AUG-2012', i_charge => 1382.06, 'N') to_pay
       *
ERROR at line 1:
ORA-06553: PLS-312: a positional parameter association may not follow a named association

SQL> 

After that little diversion, I need to return to working on the algorithm to calculate the number of Brownie Points required to let me watch the play-off final before I have to pack up the TV.

Filed under: Oracle, SQL Tagged: Council Tax apportionment, function call from sql, ORA-06553, pass by position, pass by reference

…it doesn’t mean they’re not all out to get you !
No, this isn’t sage advice for the new manager of the England Football Team ( although Mr Hodgson will surely come to appreciate it’s wisdom in the very near future), but rather something to be mindful of when writing database code.

In my experience, there can be a worrying complacency among database developers when it comes to Security.
It’s as if they feel that their code is invulnerable, protected by that firewall thingy and inaccessible to those unsavoury types who want to crack their system and uncover the goodies therein.

Sometimes, it gets to the point where I begin to wonder, is it just me who worries about this sort of thing ? Am I just being a bit too paranoid ?

Of course, there are a multitiude of techniques to exploit vulnerable code from either side of the firewall.
From the outside, there’s always the prospect of SQL Injection ( among many others).
Even within an organisation, there’s the possibility that someone may just be a little bit curious .

So, it’s comforting – to me at least – to know that either I’m not totally paranoid, or if I am, I’m in good company.

First off, this post from Jeff Kemp made me smile.

The whole question of just who might be trying to get access to your data is covered very nicely
in this presentation from Alexander Kornbrust.

In it, Alex identifies 5 categories of attacker. Three of these categories will have access to the target
system from inside the firewall.

On the subject of mental instability, it’s that time of year again. Yes, Luton are in the play-offs so I will be offering Simon the usual moral support…whilst trying not to think about West Ham’s prospects against the mighty Cardiff.
Ironically, Luton are also up against Welsh opposition in Wrexham.
It may be my rampant paranoia, but I get the distinct impression that Deb is already sharpening her rapier wit in anticipation.

Filed under: Oracle, SQL

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.

You can count on COUNT

There is absolutely no point in using an NVL with COUNT because it will always return one row, even if the value it returns is zero.
That’s right, one row ! Count them !! ONE !!!
I realise that multiple exclamation marks are seen in some quarters as the sign of a diseased mind, so I shall take a deep breath and demonstrate via the medium of SQL*Plus

SQL> CREATE TABLE nuffin( nada VARCHAR2(1))
  2  /

Table created.

SQL> SELECT * FROM nuffin;

no rows selected

Now, if we set SQL*Plus to report the number of rows returned (even if it’s 1)…

SQL> set feedback 1
SQL> SELECT COUNT(*) FROM nuffin;

 COUNT(*)
---------
        0

1 row selected.

SQL>

NOTE – I normally resist using words like ‘always’ and ‘never’ so if you do have any instance where COUNT does not return one row, then let me know and watch me eat a large helping of humble pie.

Now I’ve got that out of my system, the next question to address is, which is the more efficient medium for a query returning a single row, an implicit cursor or an explicit one ?

What we “know” about Implicit Cursors

Conventional wisdom on implicit cursors is essentially this :

• the cursor will fetch the first row.
• if no data is found, the NO_DATA_FOUND exception (ORA-1403) will be raised
• if a row is found, the cursor will then attempt to fetch a second row
• if a second row is fetched then the TOO_MANY_ROWS exception (ORA-1422) will be raised

Surely then, this means that an explicit cursor should be used for single-row query. It only returns one row so the extraneous extra fetch is pointless, isn’t it ?
Let’s have a look…

ALTER SESSION SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER='implicit_count';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
DECLARE
        l_count PLS_INTEGER;
BEGIN
        SELECT COUNT(*) INTO l_count 
        FROM nuffin;
END;
/

If you want to play along, the trace file will be output to the USER_DUMP_DEST directory.
You can check the location by running the following query :

SELECT value
FROM v$PARAMETER
WHERE name = 'user_dump_dest';

If you then go to that directory, the tracefile we just generated will have a name ending in ‘implicit_count.trc’. In my case, the file is xe_ora_2206_implicit_count.trc.

Now run it through tkprof…

tkprof xe_ora_2206_implicit_count.trc implicit_count.prf explain=uid/pwd@db sys=no

Now we have a nice readable file called implicit_count.prf.
If we now look at the relevant bit of the file :

SELECT COUNT(*) 
FROM 
 NUFFIN 


call     count       cpu    elapsed       disk      query    current        rows 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
Parse        1      0.00       0.00          0          1          0           0 
Execute      1      0.00       0.00          0          0          0           0 
Fetch        1      0.00       0.00          0          3          0           1 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
total        3      0.00       0.00          0          4          0           1 

We can see that our implicit cursor does only a single fetch. Has the world gone mad ?

The “fact” that an implicit cursor always does a second fetch may indeed either be
(a) no longer true
or
(b) never have been true ever.

Honestly, Father Christmas, the Tooth Fairy, implicit cursors do a second fetch. I feel like all of my illusions are being shattered.

I’d better get the dustpan and brush ready to sweep them up.

So, if the implicit cursor is efficient enough not to waste time with that mythical extra fetch, where does that leave the explicit cursor ?

Meanwhile, back on Venus…

We now know that the implicit cursor is just as efficient. We also “know” that use of an explicit cursor causes a context switch because that’s what everyone says it does.
This efficiency question is offset somewhat by instances where you have to run the same query more than once.
Having the code in an explicit cursor makes it more maintainable. The query is in one place.
So, the question is, how do you balance the need for performance against the need for maintainability ?
Well, one solution is to have your cake and eat it…(just don’t get the crumbs everywhere).

The best of both worlds ?

I’ve knocked up some fairly simple examples to test three different scenarios :

• Using an explicit cursor
• Using multiple implicit cursors
• Using a call to a function where an implicit cursor is executed

I’ve executed each in turn, tracing the output.
The database has been re-started between each run to make sure the results were not skewed by anything hanging around in the cache.
These tests were done on an 11gR2 Enterprise Edition Database I happen to have lying around. Yes,I did tidy it up afterwards.

Before we do anything else, let’s add a few more rows into our table (yes, I’m recycling the one I used earlier – Deb would approve) :

DECLARE
    l_val nuffin.nada%TYPE;
BEGIN
    FOR i IN 1..100000 LOOP
        IF MOD(i, 5) = 0 THEN
            l_val := 'N';
        ELSE
            l_val := 'Y';
        END IF;
        INSERT INTO nuffin(nada) VALUES(l_val);
    END LOOP;
END;
/

Now for the “traditional” explicit cursor approach :

Explicit Cursor

First the code :

CREATE OR REPLACE PACKAGE countme_pkg AS
    PROCEDURE do_something_pr(o_count OUT NUMBER);
END countme_pkg;
/

CREATE OR REPLACE PACKAGE BODY countme_pkg AS
PROCEDURE do_something_pr( o_count OUT NUMBER) IS
    l_before PLS_INTEGER;
    l_after PLS_INTEGER;
    CURSOR c_nuffin IS
        SELECT COUNT(*)
        FROM nuffin;
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    -- call the cursor
    OPEN c_nuffin;
    FETCH c_nuffin INTO l_before;
    CLOSE c_nuffin;
    -- 
    -- flimsy pretext for re-using the cursor
    --
    INSERT INTO nuffin(nada) VALUES('Y');
    --
    -- call the cursor again
    --
    OPEN c_nuffin;
    FETCH c_nuffin INTO l_after;
    CLOSE c_nuffin;
    COMMIT;
    o_count := l_after;
END do_something_pr;
END countme_pkg;
/

One explicit cursor, defined once, called twice. Nice and manageable. But how fast is it ?

ALTER SESSION SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER='explicit';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
set serveroutput on
DECLARE
    l_count NUMBER;
BEGIN
    countme_pkg.do_something_pr( l_count);
    DBMS_OUTPUT.PUT_LINE('Count is '||l_count);
END;
/

If we look at the tkprof output we can see …

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          2          2          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.06         47        396          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.04       0.06         49        398          0           2

Implicit Cursor

Now let’s change the package body so that we’re not using explicit cursors at all :

CREATE OR REPLACE PACKAGE BODY countme_pkg AS
PROCEDURE do_something_pr( o_count OUT NUMBER) IS
    l_before PLS_INTEGER;
    l_after PLS_INTEGER;

    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    --
    -- call cursor
    --
    SELECT COUNT(*) INTO l_before
    FROM nuffin;
    -- 
    -- another spurious insert
    --
    INSERT INTO nuffin(nada) VALUES('Y');
    --
    -- and again
    --
    SELECT COUNT(*) INTO l_after
    FROM nuffin;
    COMMIT;
    o_count := l_after;
END do_something_pr;
END countme_pkg;
/

Re-start the database, run the script ( this time specifying a different TRACEFILE_IDENTIFIER), do the tkprof thing and…

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          2          2          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.04         48        412          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.03       0.04         50        414          0           2

That’s a bit quicker – less cpu and less elapsed time. That’s it then. If you want quick then implicit is the way to go. Oh well, I suppose it’s hardly worth trying the third option ….

The function

Now, apparently this sort of thing would be slower because there is more code for the PL/SQL engine to interpret…

CREATE OR REPLACE PACKAGE BODY countme_pkg AS
FUNCTION get_nuffin_fn RETURN PLS_INTEGER IS
        l_count PLS_INTEGER;
BEGIN
        SELECT COUNT(*) INTO l_count
        FROM nuffin;
        RETURN l_count;
END get_nuffin_fn;

PROCEDURE do_something_pr( o_count OUT NUMBER) IS
        l_before PLS_INTEGER;
        l_after PLS_INTEGER;
        PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
        --
        -- time to get funky
        --
        l_before := get_nuffin_fn;
        -- 
        -- more random DML
        --
        INSERT INTO nuffin(nada) VALUES('Y');
        --
        -- and another function call
        --
        l_after := get_nuffin_fn;
        o_count := l_after;
        COMMIT;
END do_something_pr;
END countme_pkg;
/

Let’s find out if more code really is more work …

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          2          2          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.03         48        412          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.03       0.04         50        414          0           2

Interesting. This is just as fast as the in-line implicit example with the extra bonus of having to do one less parse.
Additionally, with a function, we can replicate the functionality you’d have with an explicit cursor and pass in parameters to be used in the predicate at runtime.
Not only is it fast, but it’s easily maintainable ( cursor code is in one place) and it’s tidy. Deb will be pleased.

Annoying habits of highly famous people

Despite their best efforts, the great and the good of the Oracle world are only human. Sometimes they will give a definite and emphatic answer to a question. They will use absolute terms such as “always” and “never”.
If it’s good enough for them, then it’s good enough for me…

Never take someone’s word for it (especially not mine). Test it yourself on your application running on your system.
The correct answer to any Oracle related performance question will always be “it depends…”

Filed under: Oracle, OraDBPedia Syndication, PL/SQL, SQL Tagged: ALTER SESSION, explicit cursor, implicit cursor, NVL(COUNT(*)), set events, timed_statistics, tkprof, tracefile_identifier, user_dump_dest

My brother Steve lives near Brands Hatch in Kent. This is quite appropriate really. He’s always been a bit of a thrill-seeker. After his motorbike accident a few years back, he seems to have decided to treat the wheelchair as merely a transfer from two wheels to four.

“I’ve got a new Nissan” he told me the other day. “It’ll probably go round Brands Hatch quite slowly”.

What’s this ? Has the daredevil spirit suddenly disappeared and been replaced by a Nissan Micra ?

Er…No.

Steve’s latest mode of transport is a Nissan GT-R. It looks like it’s breaking the speed limit, even when it’s parked on the drive.
Steve assures me that hitting the throttle is not so much a means of increasing speed as a command to engage warp drive.

All of which speed-freakery brings me to the point of this post, namely sizing the SGA_TARGET so that your database will be just that bit faster.

The Problem

The database in question is running with a single block-size (8K). Automated memory management is enabled.
The DB_CACHE_ADVICE parameter is set to ON. This means that Oracle gathers information to enable it to predict the effect of changing the memory configuration of the database.
There are no objects using the RECYCLE or KEEP caches.

We know from monitoring that the application is waiting on buffer cache availability.
We want to increase the buffer cache without impacting negatively on other SGA components that are also being dynamically managed.
These include :

• Shared Pool
• Large Pool
• Java Pool
• Streams Poo

The server we’re running on is 32-bit. Having accounted for OS requirements and all other processes on the server, we know that we have physical memory available in which to expand the SGA. We also know, as it’s 32-bit, that the total memory avaialble to the database will not be able to exceed 4GB.

We know that dynamic memory management is enabled because the SGA_TARGET is set to a non-zero value ( in this case 584M).

If you want to check what memory is allocated to the SGA components in the database you can use:

SELECT component, current_size/1024/1024 as size_mb, min_size/1024/1024 as min_size_mb
FROM v$sga_dynamic_components
WHERE current_size > 0
ORDER BY component;

The results in our example are something like :

Component	Size(MB)	Min Size(MB)
DEFAULT buffer cache	452	452
java pool	16	8
large pool	4	4
shared pool	108	88

Obviously, this is a dynamic view and the results are not immutable as memory will be allocated between the various SGA components as and when Oracle thinks necessary. Having taken samples over a period of time and various workloads, the results above are fairly representative of the memory allocation overall.
However the memory is allocated, this query should return the same result -

SELECT SUM(current_size)/1024/1024
FROM v$sga_dynamic_components
/

In our case, 584 MB.

What will happen if I press this button ?

DBAs are by nature a cautious breed, especially when it comes to fiddling around with database settings. “Suck it and see” may be a useful approach in many walks of life, but does tend to accelerate the ageing process when vital production databases are concerned.
Fortunately, Oracle is on-hand to give us a hint as to the effects of various settings of the SGA_TARGET will have on performance. It does this by means of the V$SGA_TARGET_ADVICE view.
What we’re interested in here is, if we change the amount of memory available to the SGA, what will be the performance impact ?
There are three columns here that are particularly relevant, SGA_SIZE ( the size in MB); SGA_SIZE_FACTOR (the size relative to the current size); and ESTD_DB_TIME_FACTOR.
It does take a bit of digging around to establish exactly what DB_TIME is. The 10.2 documentation is a bit vague to say the least. However the 11.2 docs are a bit more specific.
In short, it’s probably easiest to think of the ESTD_DB_TIME_FACTOR as the amount of time required to process an operation which takes 1 second in the current configuration.

Let’s have a look at what it’s telling us at the moment :

SELECT sga_size, sga_size_factor, estd_db_time_factor
FROM v$sga_target_advice
ORDER BY sga_size ASC;

The results are :

SGA_SIZE	SGA_SIZE_FACTOR	ESTD_DB_TIME_FACTOR
292	0.5	2.2588
438	0.75	1.4914
584	1	1
730	1.25	0.4863
876	1.5	0.4265
1022	1.75	0.4255
1168	2	0.4255

From this, we can see that increasing the SGA size to 730MB will reduce the DB_TIME by over half. Increases above this mark will have a lesser impact to the point where Oracle predicts that there will be no difference in terms of performance, between having an SGA size of 1022MB and 1168 MB.

In summary, Oracle’s advice is to increase to 730MB at least, and 1022MB if you have the memory available to do it.

By contrast, I can guess Steve’s advice…

Just press the Loud pedal!

Given that Oracle is saying that increasing to 1168MB will have the same effect as increasing to 1022MB, and that I have the physical memory available to do the former, I’ll go with the Steve’s approach.

If you’re using an SPFILE in your database all you need to do to make the change is :

ALTER SYSTEM SET sga_target=1168M SCOPE=BOTH;

If you are just using an init.ora, then you need to change the sga_target setting there and re-start the database for the change to take effect.

You’ll notice at this point that I have not changed the SGA_TARGET_MAX_SIZE parameter.
This is because I want to see the effect of an SGA limited to the size we’ve set. If the SGA_TARGET_MAX_SIZE is set to a value lower than SGA_TARGET then Oracle will ignore it.

Another consideration at this point, especially if you’re running on a Windows server – if at all practical, then it’s probably worth re-booting the server itself, just to minimize the chances of it getting confused and leaking memory everywhere !

To check that the change has taken effect …

SELECT SUM(current_size)/1024/1024
FROM v$sga_dynamic_components;

This should now return the new SGA_TARGET size – in our case, 1168.

A little while later…

Fast forward a week and the database has been running the new configuration. The performance improvement has been noticed by end-users (always a good sign) and the batch runs are running that bit faster.
At this point, it’s probably worth checking what Oracle’s current advice is in terms of SGA size and whether it was in fact worth allocating more memory than it was recommending :

SELECT sga_size, sga_size_factor, estd_db_time_factor
FROM v$sga_target_advice
ORDER BY sga_size ASC;

The results are rather illuminating :

SGA Size	Size Factor	DB_TIME
292	0.25	9.0873
584	0.5	2.9462
876	0.75	1.0901
1168	1	1
1460	1.25	0.9494
1752	1.5	0.923
2044	1.75	0.6024
2336	2	0.6015

Remember, Oracle originally predicted a DB_TIME factor of 0.4255 for an increase in size to 1168MB. If this had held true, then the DB_TIME factor for an SGA size of 584 MB should now be reported as 2.35 (1/0.4255). In fact, the DB_TIME factor is 2.94.

Additionally, Oracle now claims that increasing the SGA size again will further benefit performance (e.g. a 2044MB SGA will reduce the DB_TIME factor to 0.6)

In light of this I think we can infer that increasing the SGA to the higher value was probably the way to go.

Steve has promised to pay us a flying visit in the next couple of weeks. If he’s driving the Nissan, I’m not sure what other type of visit it could be.

Filed under: Oracle, SQL Tagged: automated memory management, db_cache_advice, estd_db_time_factor, sga_target, sga_target_max_size, v$sga_dynamic_components, v$sga_target_advice

Ah, sunny Milton Keynes. There’s no place like it. Nestling in the heart of England, halfway between London and Birmingham, my home town has plenty to recommend it.
Yes, many of my countrymen like to poke fun at the Concrete Cows that are Milton Keynes’ most famous landmark. However, the one irrefutable benefit of living here, especially if you tend toward the geeky, is that Milton Keynes is also the home of the National Museum of Computing, hosted in Bletchley Park.
One thing about Milton Keynes is the interminable roundabouts throughout the city. Just keep turning left at every roundabout and you’ll be guaranteed to end up back where you started.

The same however, cannot be said of APEX – at least, not without a little bit of work.

In the example that follows, we have a page in an APEX application that can be invoked from a number other pages. The target page has a back button to return to the page you just came from. The question is, how do you make the target page re-direct back to the correct calling page ?
It must be said that the solution that follows can best be described as crude but effective. I’d be interested to hear if you come up with a more elegant solution.
Anyway, here goes….

The target page

I’ve created an application which has a single blank page called Welcome_MK. This will be the target page.

First of all, let’s put something on the page.

In the component view, in the Regions section, hit the Create Icon. BTW – just in case you’re wondering, the component view looks like this :

Now to create the target page :

From here, click Next.

In the next page, select HTML and click Next

The title is Welcome to MK. Click Next

In the HTML Region Source, enter what should be the city’s motto :
Welcome to Milton Keynes where all roads lead to….another roundabout

Click Create Region

Add a hidden item

Back in the Component View, we need to create a hidden item. This will hold the number of the page we’ve come from to get here :

In the Items section hit the create icon

In the Create Item dialog select an item type of Hidden.

Click Next

Item Name will be PX_PREV_PAGE where ‘X’ is the number of the target page in your application. In my case it’s Page 1 so… P1_PREV_PAGE

For Region, select the Region you’ve just created ( in my case, Welcome to MK(1) 11).

Click Next

Accept the default values and once again, Click Next

Source Used – set to Always, replacing any existing value in session state

Click Create Item

Create the Back button

Back in the Component View, go to the Button Section and hit the create icon

Select the same region for your button as the one you’ve just created your hidden item in.

Click Next

Select Create button in a region position and Click Next

Button name is BACK
Label is the same
Click Create Button

Create the branch

Now for the “clever” bit. We’re going to create a Branch to dynamically re-direct to whatever page number is in the P1_PREV_PAGE hidden item.

In the Component View under Branches click the create icon.

Branch Point – On Submit : Before Computation( Before Computation, Validation, and Processing)
Branch Type – Branch to Page Identified by Item ( using Item Name)

Click Next

Identify Item – the hidden item you just created

Click Next

When Button Pressed – select the Back Button you created

Hit Create Branch

Now we’ve got the target page, we need a page to call it from…

The Calling Page

Click Create New Page

Select Page Type : Report
Select Interactive Report

I can’t think of a suitable page name so I’ll just accept the defaults and click Next

Nope – don’t want to use tabs – Next

Now for the select statement. Let’s keep it simple :

SELECT 'Turn Left' FROM dual;

Link to Single Row – No
Next…

Finish.

Now we want to make the one column in the report a link to our target page….

In the component view under Regions, click on the Interactive Report link.

In the Column Attributes section, click on the Yellow Pencil icon to edit the TURNLEFT column.

Under the Column Link Section, select an appropriate icon for the Link Text. I decided on the magnifying glass.
Target is a Page in this Application and enter the Page number of the target page.

Item 1 – the Name is the name of the hidden column we created in the target page.
The value is the number of this page.

Hit Apply Changes

….and again.

I realise that, at this point, Alan Turing is probably turning in his grave. The place that he made famous mentioned in the same breath as the callous and unthinking use of what he would call “magic numbers” ( that’s hard-coded values to anyone younger than me…which is probably most of you).

Time to give our quick ‘n’ dirty solution a test drive.

Start by running the report ….

Click on the magnifying glass icon (or whichever one you chose for your link) …

Now for the moment of truth…click the Back button.

Well, it does work. Like I say, it’s not the most elegant solution, but it’ll do for now.

Filed under: APEX, Oracle Tagged: Alan Turing, APEX, branches

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.

A Simple Example

Let’s say we have a table called simple. It’s created like this :

CREATE TABLE simple (
	first_name VARCHAR2(30),
	msg VARCHAR2(50))
/

INSERT INTO simple( first_name, msg) VALUES('MIKE', q'[Oh, it's you]')
/

INSERT INTO simple( first_name, msg) VALUES('DEB', 'Hey gorgeous!')
/

COMMIT
/

We’ve also got a function that PHP plays nicely with, which returns a Weakly Typed Ref Cursor :

CREATE OR REPLACE FUNCTION get_simple_fn RETURN SYS_REFCURSOR 
IS
	l_ret_rc SYS_REFCURSOR;
BEGIN
	OPEN l_ret_rc FOR
		SELECT first_name, msg FROM simple;
	RETURN l_ret_rc;
END;
/

We want to reuse this function in our APEX application. For this purpose, we need to turn to a technique more usually associated with ETL, the pipelined function.
To do this, we will need some or all of :

• a database Object Type
• a database Table Type of the Object type
• a pipelined function to act as a wrapper for the Ref Cursor
• a giant inflatable plastic daffodil

Deb was looking over my shoulder so I had to add that last item.

The Database Types Method

Create the Database Object Type

CREATE OR REPLACE TYPE simple_typ AS OBJECT (
	first_name VARCHAR2(30),
	msg VARCHAR2(50))
/

And now for the Table of objects type…

CREATE OR REPLACE TYPE simple_tab_typ IS TABLE OF simple_typ
/

Finally, the pipelined function to act as a wrapper…

CREATE OR REPLACE FUNCTION simple_pipe_fn( i_cursor SYS_REFCURSOR)
	RETURN simple_tab_typ PIPELINED IS
	l_row simple_typ := simple_typ(NULL, NULL);
BEGIN
	LOOP
		FETCH i_cursor INTO l_row.first_name, l_row.msg;
		EXIT WHEN i_cursor%NOTFOUND;
		PIPE ROW( l_row);
	END LOOP;
	RETURN;
END;
/

So, the Pipelined function takes a REF CURSOR as an argument and returns a value of the table type we’ve just created.

Now we test this little lot from SQL …

SELECT * 
FROM TABLE(simple_pipe_fn(get_simple_fn)); 

FIRST_NAME MSG 
---------- -------------------- 
MIKE	   Oh, it's you 
DEB	   Hey gorgeous! 

Let’s see what APEX makes of all this…

In Application Builder, go to whatever your playground application is and Create Page
I’m going for an Interactive Report.

Page Name is Pipeline Test
Region Name is Simple.

Now to enter the select statement :

Click through the rest of the creation wizard and then run it …

OK, not the most elegant report ever, but it does actually work.

There are one or two things that are a bit unsatisfactory with this approach ( apart from the obvious drawback of having to persuade a PL/SQL development tool to play with a Ref Cursor).
First off, this database type business. Well, it’s not exactly robust, is it. If I change the table definition, I’ll need to remember to change the object type as well.

The other minor niggle is, well, there does seem to be quite a bit of type-ing. Ahem.
Moving swiftly on, let’s see if we can solve both of these issues in one fell swoop….

And now for something completely different…

Right, we’re going to drop those boring fuddy-duddy database types we’ve just created and use a PL/SQL package instead.

DROP TYPE simple_tab_typ 
/ 

Type dropped 

DROP TYPE simple_typ 
/

Type dropped

Now to replace these with a package header :

CREATE OR REPLACE PACKAGE pipe_types_pkg AS
	TYPE simple_tab_typ IS TABLE OF simple%ROWTYPE;
END pipe_types_pkg;
/

As we’ve declared this type in a package, we can use an anchored declaration to base it on the table.
If the table structure changes, so will the type.

Finally, we need to change the pipelined function to reference the type we’ve declared in the package :

CREATE OR REPLACE FUNCTION simple_pipe_fn( i_cursor SYS_REFCURSOR)
	RETURN pipe_types_pkg.simple_tab_typ PIPELINED IS
	l_row simple%ROWTYPE;
BEGIN
	LOOP
		FETCH i_cursor INTO l_row.first_name, l_row.msg;
		EXIT WHEN i_cursor%NOTFOUND;
		PIPE ROW( l_row);
	END LOOP;
	RETURN;
END;
/

If we now test this again …

SQL> SELECT * FROM TABLE(simple_pipe_fn(get_simple_fn)); 

FIRST_NAME MSG 
---------- -------------------- 
MIKE	   Oh, it's you 
DEB	   Hey gorgeous! 

SQL> 

It must be said that, even though we haven’t explicitly created a database type , Oracle has taken matters into it’s own hands

SELECT object_name, object_type 
FROM user_objects 
WHERE TRUNC(created) = TRUNC(SYSDATE)
/
SYS_PLSQL_27148_21_1	       TYPE 
SYS_PLSQL_27148_DUMMY_1        TYPE 
SYS_PLSQL_27158_9_1	       TYPE 
SYS_PLSQL_27158_DUMMY_1        TYPE 
PIPE_TYPES_PKG		       PACKAGE 
SIMPLE_PIPE_FN		       FUNCTION 
GET_SIMPLE_FN		       FUNCTION 
SIMPLE			       TABLE 

Hmmm, not sure why it’s felt the need to define four types on my behalf.
On the plus side, we can leave the database to look after it’s own types. Ours is all future-proofed and low maintenance.

Of course, you have the option of declaring all of your required types in a single package header, or leaving them in the packages in which your Ref Cursor functions reside.

To prove a point, let’s see what happens if we make a change to the simple table :

ALTER TABLE simple MODIFY (
    msg VARCHAR2(100))
/

Let’s see how our package version of the code copes :

SELECT * 
FROM TABLE( simple_pipe_fn( get_simple_fn)) 
/ 

FIRST_NAME MSG 
---------- -------------------- 
MIKE	   Oh, it's you 
DEB	   Hey gorgeous! 

SQL> 

Running the report in APEX shows a similar lack of concern for the change in the table structure.

Whilst all this does mean that you have to create an API for the web API you’ve already got, it does mean that you can re-use the code in APEX.

Deb has just wandered by humming “Land of My Fathers” which I will take as my queue to run away and hide in the cupboard under the stairs for a bit.

Filed under: Oracle, OraDBPedia Syndication, PL/SQL, SQL Tagged: apex 4.1, create type, create type as object, create type is table of, pipelined functions, REF CURSOR, sys_refcursor, weakly typed ref cursor

For those of us who use Linux on the Desktop, it’s probably fair to say that we live in interesting times. Having sat on the fence that is Gnome 2, whilst looking on as the relative merits of Gnome Shell, Unity, KDE and XFCE are hotly debated, I was recently given a fresh perspective on this particular debate by the lovely Debbie.

What follows is the story of how Deb converted to Linux, told (for reasons which will become apparent) through the medium of fairytale.

Once Upon A Time

Princess Debbie was having a difficult morning. It had been a rapid promotion. In the previous post, she had been merely the Welsh Ambassador. Elevation to the status of royalty had been, she was told, due to narrative causality – i.e. fairy tale heroines have to be Princesses.
Debbie herself, suspected that her change in station had rather more to do with an unfortunate lapse of memory on the part of the author, resulting in a shortage of Valentine’s cards.

How ever she’d got to this point, Debbie was feeling particularly frustrated. Standing between her and her favourite shoe shopping website was the seemingly ever-present Windows wheel, “spooling”, as she called it, whilst Internet Explorer laboured to load the objects of her affection.

It was whilst gazing around her, wondering if Windows would ever get to the point, that she noticed the Frog. It was gazing back…although not directly at her. It was looking at her computer with, what Debbie judged to be, unusual interest…for a frog at least.

“Well, they say you have to kiss a few frogs”, said Debbie to herself. More in hope than expectation, she scooped up the irate amphibian and bestowed upon it, a royal peck,

There was a puff of smoke….

Debbie was mildly disappointed but not entirely surprised. Standing before her was not the hoped-for handsome prince, come to rescue her from Windows drudgery with a shiny new Mac, but what could best be described as a Geek.
“Not so much handsome as passable in poor light”, thought the Princess. Oh well, now he was here he could make himself useful.

“Is there anything you can do to make this computer go faster ?” she demanded of the Geek, “Windows is driving me mad.”

“You look like you’re pretty well set here, what with living in a palace, have you thought about getting a Mac, you highness ?” he asked.

“Have you any idea how many pairs of shoes I could get for the cost of a Mac ?”, the Princess replied haughtily. Then she sighed, “I had an Apple many years ago. It was my first computer. Unfortunately, Apples in fairy tales always spell trouble for princesses.”

“OK, so Windows is too slow and Mac is too expensive, let’s see if we can find something that’s just right”, said the Geek, getting into the fairytale spirit.

“I know where you’re going with this”, warned Debbie, “and any attempt to compare me to some blonde bimbo with a penchant for house-breaking will have consequences !”

Whose the fairest of them all ?

The Geek knew when not to push his luck. Instead, he wordlessly pulled a bundle of Live CD’s from his pocket ( yes, this being a family fairytale, his transformation from frog to human had left
him fully clothed).

“Right, we’ve got Mint, Ubuntu, Fedora and SUSE. Let’s try them out and see if anything takes your fancy.”

Mint

A quick reboot later, the Princess was looking, rather critically, at Mint, complete with the more traditional Gnome 2.3 desktop, the Geek’s personal favourite.
“Too green” was the Royal Proclamation. At a stroke, our raven haired heroine had firmly put the kybosh on any further Goldilocks references.

Ubuntu with Unity

Slightly taken aback at the summary nature of this setback, the Geek was somewhat surprised by the reaction to his next offering.
The Princess was immediately taken by the colour-scheme, having something of an affinity to purple.
Also, she couldn’t help noticing the enticing shopping bag icon on the Unity dock.
“Looks a bit like a Mac”, she said aloud. “We’ll give that one a try”.

At this point it’s worth reflecting upon the difficulty of writing a decent fairytale when the characters insist on ignoring the most basic rules.
In fairytales, things come in threes. Three witches, three little pigs, three wishes … three furry burglary victims. Apparently, this rule does not apply to Linux Distros.

Installing a Happy Ending

So the Geek huffed. And he puffed. And he…dual-booted the machine.
Some finishing touches by Royal Command…the background image was changed to a picture the Princess had taken sometime earlier and quite liked…even though there was a fair amount of green in it.

Clicking on the enticing shopping bag, the next step was to find and download the Ubuntu Restricted Extras package to get access to all those naughty but nice proprietary video and audio codecs.

Next up, he configured Thunderbird to access her web-mail – a task so easy that he didn’t even have to remember the default port for SMTP (25).
Finally, he copied the music library across. Banshee was ready to go.

And the moral of the story…

Simply this. If you want to live happily ever after without shelling out a small fortune for an Apple, give Linux a try. On the evidence of this sample of one, that Mark Shuttleworth knows what he’s doing ( although for my part, I’ll wait for a bit, at least until the Long-Term Support version comes out).
Oh, and if you are going to take the word of a fairytale princess when she says “let’s not bother with Valentine’s Day”, be sure to check out the strength of the Wi-Fi signal in the Garden Shed.

Filed under: Linux, Ubuntu Tagged: ubuntu restriced extras, Unity

This post was going to begin with one of my occasional bulletins on the fortunes of Luton Town, beloved club of my mate, Simon. However, the man himself has been bitten by the blogging bug and the resultant musings on all things Teradata ( and various other topics) can be seen here.
So instead, I’d invite you to consider the Nordic majesty that is Milford Sound. Nestled in the heart of Fjordland in New Zealand’s South Island, this watery expanse evokes awe and wonder, even in a land where jaw-dropping scenery is always just around the next bend.
Milford Sound was itself named after the equally picturesque sounding Milford Haven. Yes, Milford Haven in Wales, site of one of the largest oil terminals in Europe.
I should note at this point that the Welsh Ambassador has demanded that I point out that Milford Haven does have some nice bits.

Moving swiftly on, in the vain hope of avoiding domestic disharmony, my point is, the fact that two things share common characteristics doesn’t mean that they are necessarily identical.

All of which provides a somewhat tortuous link to the subject of this post, namely, setting up remote desktop access via a VPN on Mint.

Now, you’d think this was pretty much the same as on Ubuntu, and it is…up to a point.

First off, the download for the VPNC Network Manager package

sudo apt-get install network-manager-vpnc

Click on the network manager icon …

Select a Cisco compatible VPN

Provide the information you got from your network Admin (Gateway, Group Name, Group Password).

NOTE – at this point, I had to re-boot before my newly created VPN connection appeared in Network Manager.

Now all you need to do is go to the Internet Menu and select Terminal Services Client…except it’s not there on Mint. Oh.
Where as Ubuntu offers the tsclient utility as it’s standard, friendly GUI to type in all the requisite information ( IP Address, login details etc), Mint doesn’t include it out of the box.
Instead, we need to look at using rdesktop.
Fear not, dear reader, for although setting this up does, initially at least, require a foray into the wacky world of shell scripting, it’s easy enough to set this up to run without having to go to the command line every time you feel the need to logon to work.

rdesktop

There’s a useful forum post on rdesktop here.

Anyway, if I want to remote onto my PC (which for the sake of this example has an IP Address of 192.168.1.57) …

rdesktop 192.168.1.57 -u mike -p mypassword -r clipboard:PRIMARYCLIPBOARD -g 80%

So, we pass in the IP address, the username on the target machine (-u mike), the password (-p mypassword),

The -r switch enables re-direction of a device ( in this case, the clipboard on the target machine).

The -g switch specifies the screen size of the rdesktop window as a percentage of the total screen size.

Making it Prettier with Zenity

Despite being, what could be (and has been) characterised as a “Command Line Codger”, I would quite like to have a nice GUI rather than doing all that typing, especially if I need to connect to more than just my work PC.
Fortunately, Zenity is on hand to offer a bit of GUI goodness without too much messing about

#!/bin/sh 
targetIP=$(zenity --list \ 
   --title="Choose the Host you want to connect to" \ 
   --column="Target IP" --column="Host Description"\ 
            192.168.1.57 "My Desktop"\ 
            192.168.1.80 "Prod Server" ) 
uname=$(zenity --entry \ 
    --title="Username"\ 
    --text="Enter Username") 
pwd=$(zenity --entry \ 
    --title="Password"\ 
    --text="Enter Password" --hide-text) 

rdesktop $targetIP -u $uname -p $pwd -r clipboard:PRIMARYCLIPBOARD -g 80% -a16 
exit 0 

Run this and we get :

Now all we need to do is obviate the need to open a Terminal Window altogether. Have a look here for the steps for adding a script to the menu. On this occasion ( at least on Mint11), the steps are identical to those on it’s Ubuntu cousin.

International relationships are at a delicate stage at the moment. It’s Deb’s “touchy time”…Wales are playing England in the Six Nations soon. Hopefully a foot-massage and a judiciously applied glass of white wine will avert a diplomatic incident…for now.

Filed under: Linux, Shell Scripting, Ubuntu Tagged: Mint, network-manager-vpnc, rdesktop, tsclient, vpn, vpnc, zenity

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.

The Application Error Log

In many Oracle database applications you will find a utility that’s used for debugging issues.
It consists of :

• a table to hold the error log information
• a procedure to write to the table
• a large amount of logging data

The structure of the table will probably be something like this :

CREATE TABLE error_logs(
    username VARCHAR2(30),
    datetime TIMESTAMP,
    program_name VARCHAR2(30),
    parameters VARCHAR2(4000),
    message VARCHAR2(4000))
/

And the procedure is likely to be a variation on theme of :

CREATE OR REPLACE PROCEDURE log_error_pr (
    i_prog_name VARCHAR2, i_params VARCHAR2, i_err VARCHAR2) AS

    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO error_logs(
        username, datetime, program_name,
        parameters, message)
    VALUES(
        USER, SYSTIMESTAMP, i_prog_name, 
        i_params, i_err);
    COMMIT;
END log_error_pr;
/ 

At this point, it’s worth considering the parameters column. Yes, it is a VARCHAR2(4000) and whilst this may, in exceptional circumstances, prove to be too small to hold the requisite data, on the whole it should be OK.
Unfortunately, getting the required data ( in this case the parameter names and values) into the required format to go into the table requires a fair bit of messing about.

CREATE OR REPLACE PROCEDURE trouble_pr(
    i_name IN VARCHAR2, i_payday IN VARCHAR2,
    i_been_to_ikea IN VARCHAR2)
     AS
    --
    -- This procedure only exists to cause trouble
    -- It starts off having just returned from a well-known 
    -- furniture store
    --
    l_msg VARCHAR2(4000);
    e_bits_missing EXCEPTION;
    l_params error_logs.parameters%TYPE;
BEGIN
    --
    -- here it comes...
    --
    l_msg := 'Back to the shop';
    RAISE e_bits_missing;
EXCEPTION
    WHEN OTHERS THEN
        l_params :=
            'i_name : '||i_name
            ||' i_payday : '||i_payday
            ||' i_been_to_ikea : '||i_been_to_ikea;
        log_error_pr( 
            i_prog_name => 'TROUBLE_PR',
            i_params => l_params,
            i_err => l_msg);
        RAISE_APPLICATION_ERROR(-20000, l_msg);
END trouble_pr;
/

Of course, you can have a peek in the data dictionary to get the parameter names by means of DBA_ARGUMENTS. You could then use this information in the error logging procedure meaning that you only had to build a string of parameter values in the order in which they appear in the program. However, looking at the parameter column, especially when there are a large number of parameters passed into the program in question, can get a bit tedious.

Let’s take a different approach which, for the want of a better gag, we’ll call the IKEA method.

Object Relational thingys

The steps for this are :

• create a database type to hold the data
• create another type – essentially an arary of the first type
• create the table with a column defined as the array type
• enter and retrieve the data using the new column

The base type essentially defines a record. The record consists of two fields – the parameter name and the parameter value :

CREATE or REPLACE TYPE paramlist_typ AS OBJECT( 
  param_name VARCHAR2(30), 
  param_val VARCHAR2(4000)) 
/  

Now to create an array of the first type. This will enable us to use the base type as a Nested Table :

CREATE or REPLACE TYPE paramlist_tab_typ AS TABLE OF paramlist_typ 
/

Next up, the table :

CREATE TABLE error_logs( 
    username VARCHAR2(30), 
    datetime TIMESTAMP, 
    program_name VARCHAR2(30), 
    parameters paramlist_tab_typ, 
    message VARCHAR2(4000)) 
    NESTED TABLE parameters STORE AS paramlist 
/

Insert and Select on a Nested Table

Probably a good idea to go through a simple insert statement at this point to see how this nested table thing works :

INSERT INTO error_logs( 
    username, datetime, program_name, 
    parameters, message) 
VALUES( 
    USER, SYSTIMESTAMP, 'TEST', 
    paramlist_tab_typ( 
        paramlist_typ('PARAM1', 'Someval'), 
        paramlist_typ('PARAM2', TO_CHAR(SYSDATE, 'DD-MON-RR HH24:MI')), 
        paramlist_typ('PARAM3', 123456)), 
    'Some error message') 
/

If we want to select from the nested table, we can do :

SQL> select parameters 
  2  from error_logs 
  3  where program_name = 'TEST'; 

PARAMETERS(PARAM_NAME, PARAM_VAL) 
-------------------------------------------------------------------------------- 
PARAMLIST_TAB_TYP(PARAMLIST_TYP('PARAM1', 'Someval'), PARAMLIST_TYP('PARAM2', '0 
7-JAN-12 20:44'), PARAMLIST_TYP('PARAM3', '123456')) 

Hmmm, not the most elegant output. By casting the parameters column in the error_logs table to a TABLE, we can flatten out the nested table, which looks quite a bit better :

SQL> SELECT pl.param_name, pl.param_val 
  2  FROM error_logs err, TABLE( err.parameters) pl 
  3  WHERE err.program_name = 'TEST'; 

PARAM_NAME	     PARAM_VAL 
-------------------- -------------------- 
PARAM1		     Someval 
PARAM2		     07-JAN-12 20:44 
PARAM3		     123456 

SQL> 

Now we’ve got that sorted, we can see how this translates into our error logging utility.
The procedure to log the errors first :

CREATE OR REPLACE PROCEDURE log_error_pr ( 
    i_prog_name VARCHAR2, 
    i_params paramlist_tab_typ, 
    i_err VARCHAR2) AS 

    PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN 
    INSERT INTO error_logs( 
        username, datetime, program_name, 
        parameters, message) 
    VALUES( 
        USER, SYSTIMESTAMP, i_prog_name, 
        i_params, i_err); 
    COMMIT; 
END log_error_pr; 
/ 

And now for a procedure to call it :

CREATE OR REPLACE PROCEDURE more_trouble_pr( 
    i_name VARCHAR2, 
    i_somedate DATE, 
    i_some_num NUMBER) IS 

    l_msg VARCHAR2(4000); 
    e_bits_missing EXCEPTION; 
    
BEGIN 
    l_msg := 'Where did I leave that lump hammer ?'; 
    RAISE e_bits_missing; 
EXCEPTION 
    WHEN OTHERS THEN 
        log_error_pr( 
            i_prog_name => 'MORE_TROUBLE_PR', 
            -- 
            -- Might be a good idea to do type conversions in 
            -- the program calling the error logging routine 
            -- as at this point we know, for example, what the 
            -- date format should be 
            --           
            i_params => paramlist_tab_typ( 
                paramlist_typ( 'i_name', i_name), 
                paramlist_typ( 'i_somedate', TO_CHAR(i_somedate, 'DD-MON-YYYY')), 
                paramlist_typ( 'i_some_num', TO_CHAR(i_some_num,9999)) 
            ), 
            i_err => l_msg); 
        RAISE_APPLICATION_ERROR(-20000, l_msg); 
END; 
/

And now to run the procedure :

SQL> exec more_trouble_pr('MIKE', TO_DATE('03012012','DDMMRRRR'), 1234) 
BEGIN more_trouble_pr('MIKE', TO_DATE('03012012','DDMMRRRR'), 1234); END; 

* 
ERROR at line 1: 
ORA-20000: Where did I leave that lump hammer ? 
ORA-06512: at "MIKE.MORE_TROUBLE_PR", line 28 
ORA-06512: at line 1 


SQL> 

Now let’s have a look at the entry in the error_logs table :

SQL> SELECT pl.param_name, pl.param_val 
  2  FROM error_logs err, TABLE(parameters) pl 
  3  WHERE err.program_name = 'MORE_TROUBLE_PR'; 

PARAM_NAME	     PARAM_VAL 
-------------------- -------------------- 
i_name		     MIKE 
i_somedate	     03-JAN-2012 
i_some_num	      1234 

I could go on, but Deb has just forbade me from making any more “kneedless” puns on pain of having to live in the shed.

Filed under: Oracle, OraDBPedia Syndication, PL/SQL, SQL Tagged: create type as object, dba_arguments, nested table

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.

Pre-Installation Checks

There are a few bits and pieces to check here :

• shared pool size
• Oracle XDB version
• PL/SQL Web Toolkit version

Shared Pool Size

The shared pool size needs to be at least 100 MB. Shouln’t be a problem. In SQL*Plus you can check this by :

show parameter shared_pool_size

Alternatively, you can just run this query :

SELECT value
FROM v$parameter
WHERE name='shared_pool_size';

VALUE

--------------------------------------------------------------------------------

0

Hmmm, not quite so straight forward then.

When this parameter is set to 0 then Oracle automatically determines the size of the shared pool.
If you’re running on a 64-bit OS then this should ( according to the documentation) equate to 128 MB. If you’re on a 32-bit OS however, this will default to 64MB.

If you need to change this parameter, then you can simply :

ALTER SYSTEM set shared_pool_size=100M;

If you do need to make this change then it’s probably best to stop and re-start the database at this point to make sure that the additional memory is utilized.

Oracle XDB

The Oracle XDB should be there by default on XE.
Just to check :

SELECT comp_name, version, status
FROM dba_registry
WHERE comp_id = 'XDB';

Provided you get a row back from this query where the status is VALID, you’re good to go.

PL/SQL Web Toolkit

You need to have version 10.1.2.0.6 or later. Once again, this should already be there on XE. If you want to check then :

SELECT owa_util.get_version
FROM dual;

On 11gXE you should get version 10.1.2.0.8.
On 10g XE you’ll probably be on version 10.1.2.0.4.

The APEX 4.1 download does come with files to install version 10.1.2.0.6 and you should be able to run this upgrade by means of the owainst.sql script which will be in the apex/owa directory after you’ve unzipped the download.
If you are going to do this, then I’d suggest you do a full backup of the database beforehand because (a) it’s just good practice and (b) I’ve no idea if this will do what it claims without error on 10gXE.

In fact, even if everything is fine at this point, it’s still a good idea to backup your database before you run the installation… unless you don’t mind losing what you have on there already.

Download Apex 4.1

Next step is to download the Apex4.1 zip file from OTN.

NOTE – if I’m starting to bore you at this point and you’re tempted to follow the “official” installation instructions, the ones for XE are a lot more concise and less confusing than the full-blown APEX installation guide ( which is here).

Once you’ve downloaded the file – apex_4.1_en.zip to give it it’s full name ( I’ve just downloaded the English Language version), you need to copy it somewhere sensible.

In my case, I did the following :

cp apex_4.1_en.zip /u01/app/oracle

Wherever you choose to drop the file, you need to make sure that there are no spaces in the path. Otherwise, the script to install the icons isn’t very happy and you end up with a blank screen…er..so I’ve heard. I mean, I’ve got this friend who tried it. No obviously, I wasn’t dumb enough to do that myself. Ahem.

Anyway, next step is to unzip the file. To save lots of messing about with file permissions and such like, I just switched to the oracle user :

cd /u01/app/oracle
sudo su oracle
sudo unzip apex_4.1_en.zip
cd apex

That last cd was to the newly created apex directory. From this point on, this is where we’ll be running the apex upgrade scripts themselves.
There is one more bit of admin before we get to that, but as the rest of this stuff happens on the database, we can connect now.

At last – the installation

You need to connect to Oracle as SYS as SYSDBA. If you’re the oracle user on the OS then – after making sure that your $ORACLE_SID is set to XE, you should simply be able to do :

sqlplus / as sysdba

If you want to be a bit more boring and conventional ( and haven’t su’d to oracle) then :

sqlplus sys as sysdba@xe

Now, whenever I’m in a multi-database environment, I do like to make sure that I’ve connected to the database I think I have. This makes it so much easier to avoid those hilarious situations where you mistakenly drop a production database or similar.
Anyway, if you’re feeling equally paranoid then :

SELECT name FROM v$database;

This will either set your mind at ease ( in my case by returning ‘XE’) or cause you to quit the SQL session with unseemly haste.

Once we’re on the correct database as SYS then we first need to unlock the APEX_PUBLIC_USER:

ALTER USER apex_public_user UNLOCK;

Next, we need to find the default and temporary tablespaces for the APEX user in the database ( it should be SYSAUX and TEMP respectively) :

SELECT default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = 'APEX_040000';

NOTE – if you’re running this on 10g XE with APEX 2 installed ( the version that 10gXE shipped with) then the username will be FLOWS_020100. There will also be a FLOWS_FILES user and you will need to make a note of the default tablespace for this user.

Now we can get on with running the scripts.
First up is apexins.sql, which takes three arguments :

• default tablespace of the APEX user (usually SYSAUX)
• the same as the first argument, unless you have the FLOWS_FILES user, in which case it’s the default tablespace of the FLOWS_FILES user( this is also SYSAUX by default)
• the APEX user’s temporary tablespace (usually TEMP)
• a virtual directory for the APEX images ( I used “/i/” like it says in the installation notes)

So, assuming your setup is like the one I’m using :

@apexins.sql SYSAUX SYSAUX TEMP /i/

You’ll be pleased to know that this script creates a logfile in the apex directory with a name in the format installYYYY-MM-DD HH24:MI:SS.log.
You’ll be slightly less pleased to know that it’s a bit big (over 700K). Yes, it doesn’t sound that huge…until you have to wade through it.
Anyway, this script takes a little while to run, especially on my asthmatic, ageing laptop with a whopping 1GB RAM and 2GHz Celeron Processor.

Eventually, I got the cheery message :
“Now beginning upgrade. This will take several minutes”

This did cause me to wonder vaguely what on earth the script had been doing for the previous 25 minutes.
Finally, the script completed and logged me off.

Now to load the images – by means of apxldimg.sql.
This script accepts one parameter – the directory you unzipped apex into ( in my case, /u01/app/oracle).

Reconnect to the database as SYS as SYSDBA and run the script

@apexldimg.sql /u01/app/oracle

The final step is to set the password for the APEX ADMIN user :

@apxchpwd.sql

Enter the new password when prompted (it’s hidden so you won’t see anything when you type it in).
You’ll be asked to reset the password as soon as you use it for the first time so it doesn’t need to conform to the databases password complexity rules at this stage.

Post Installation steps

If you now click on the link on your desktop to the database start page, you may get a bit of a shock.
Instead of the start page, you’ll get the rather unfriendly “Error – ERR-1014 Application not found”.

Don’t worry, you haven’t missed a step ( necessarily). We’ll fix this in a minute.
For now, point your browser at http://localhost:8080/apex/apex_admin.

You will now be presented with a login screen.
Enter admin as the username and the password you’ve just set for the admin user.

At this point, you’ll be asked to set a new password, which will have to conform to the password complexity rules ( unless you’ve disabled them) – i.e. at least one uppercase, and one punctuation character. The password will also need to be a minimum of 8 characters.

Once the password change has been processed, you’ll be asked to connect again using the new password.

You should now see :

Fixing the desktop link

When you install 11gXE, a link to the APEX home page is automatically created on your desktop.
The link calls a script called gettingstarted.sh in $ORACLE_HOME/config/scripts.

In order to fix the link, you need to edit this file.

The line :

/usr/bin/$ihttp://localhost:8080/apex/f?p=4950

needs to change to :

/usr/bin/$ihttp://localhost:8080/apex/

Deb is starting to fret that we haven’t quite got enough alcohol in the house to see us over the festive period, so it looks like I’m going to have to venture out into the cold winter evening and brave the crush at the all-night supermarket. Christmas – it’s for the kids really.

Filed under: Linux, Oracle, OraDBPedia Syndication Tagged: apex 4.1, apex_public_user, dba_registry, ERR-1014, flows_files, Mint, oracle 11gXE, Oracle XDB, owa_util.get_version, PL/SQL Toolkit, shared_pool_size, sys as sysdba, Ubuntu