Oracle Portal Session Variables

Because of the way Oracle portlets work with Oracle Portal you can't use standard PL/SQL package session variables.

Instead you need to use the built in Portal session API wwsto_api_session.

Firstly declare a variable to act as an object to store the session data in.

l_store portal30.wwsto_api_session;

Then load your session, if it doesn't exist yet it will get created. Domain and subdomain do not have anything to do with web addresses but instead would be better described as a unique reference, I would define the domain as the application and the subdomain as a page within the application.

l_store := portal30.wwsto_api_session.load_session ('DOMAIN', 'SUBDOMAIN');

Then you probably want to save some data to your session, you do this by defining a attribute name and value in set attribute.

l_store.set_attribute ('NAME', 'VALUE');

If you want to grab a variable that has been set then you would use get_attribute_as_varchar2, there are also date and number variations of this API function.

my_var := l_store.get_attribute_as_date ('NAME')

If you've set any attribute variables then you need to make sure you save your session

l_store.save_session;

The session and session variables will automatically get dropped when you log out of Portal or close down your browser, you can manually drop the session with.

wwsto_api_session.drop_session('DOMAIN','SUBDOMAIN');

Finally if you want to grab your session id you can use.

wwctx_api.get_sessionid

Croc Clips

Was thinking about electrical circuits today (as you do) and the thought look me back to school days where we used an application called Crocodile Clips. I remember hours of fun being had coming up with random electronic circuits using this application.

The great news is this program is still around, although now it's called "Yenka" and it's free for home use. So if you have kids that need to use it for school projects or you just want to have a play with it it's free for use at home.

Even better it now includes gears & cog, scientific and mathematical features.

Check it out here.

Conditional Unique Constraints

Well here was the chalenge, I wanted to be able to mark a record as deleted, what is so tricky with that?

Well the record has a unique constraint on everything apart from the id column and the delete column, so if I want to insert an identical row again it will throw up a uniqueness exception. If I've marked a row as deleted it still wont let me insert the same row again, so you would not be able to insert row 2 in the following example:

ID	data	delete
1	ud1	1
2	ud1	0

So what you need is a Function Based Index, FBIs have been around since Oracle 8i so they are not a new thing. A normal unique index will allow you to create a unique constraint on multiple columns however a unique contraint using FBIs will only allow one input so you have to concatonate all of the columns together into one string.

Anyway this is some code that should get you up and running, I only used one condition to test the delete column but you could use as many a you want to really:

CREATE UNIQUE INDEX U01
ON [ TABLENAME ] (
CASE WHEN [ CONDITION 1 ]
THEN [ COLUMN 1 ]||[ COLUMN 2 ]||[ COLUMN N ]
CASE WHEN [ CONDITION 2 ]
THEN [ COLUMN 1 ]||[ COLUMN 2 ]||[ COLUMN N ]
ELSE
NULL;
END);

Server Side Image Map

I found an interesting PL/SQL feature today, when you use the

htp.formImage

procedure in a PL/SQL form it will return the value of the form image and also the coordinates of the click made on that image, so you get 3 variables returned.

• name - the value
• name.x - x coordinate
• name.y - y coordinate

So you could then use this information to create server side image maps, these could be created dynamically from a database table and updated easily by users.

User Centered Design

A colegue put me on to this really great infographic User Centered Design today.


This was researched and designed by Pascal Raabe right here in Bristol and can be applied to design in general but is definitely applicable to software design.

Sharepoint Lists

I had observed a bit of inconsistency between SharePoint 2007 List Views, some were showing

(More Items...)

At the bottom of the list and some were not, why? Well it turned out to be stupidly simple, all you had to do is initially switch your list view to

< summary >

This will initialy remove all the columns that you had in your view, click the Edit the current view link and add those columns back in again and hit apply.

Personaly I think it's a better option to set up a view that allows you to click through to the next set of records in batches of the specified size, but, if you NEED a (More Items...) link that is how you would do it.

Video Conversion

I found a great GNU (Free but not Open Source) application today for converting video called WinFF.

If you are in a situation where you are limited by the format or codec that your video uses this might help you convert to the format you require. It is fairly straight forward and basically provides a graphical interface to FFmpeg. Check it out here.

Mime-ing Excel

I'm currently overhauling some old PL/SQL code, tidying up almost a decade of comments and on the fly changes.

Part of this included Excel generation from PL/SQL, in the past in my own code I've headed down the route of using XML Spreadsheets however one of the previous developers managed to format the data into a proper XSL formatted spreadsheet using Data Interchange Format (DIF).

One of the issues I was having though is the generated file was being named after the PL/SQL package and process name (package.process), it was missing all the nice MIME information. So for reference this is how you should be structuring your basic Excel MIME header in PL/SQL:

owa_util.showpage;
owa_util.mime_header('application/excel',False);
htp.print('Content-Disposition:attachment;filename="NAME_HERE.XLS"');
owa_util.http_header_close;

Line 2: Mime header has False defined so the header isn't closed
Line 3: Defines the file name
Line 4: Closes off the header, important because you left it open on line 2

Many years ago I had a bit of software from a magazine cover disk that allowed you to share your mouse and keyboard between multiple machines across a network. It was unfortunately a limited trial and I have not used it for ages.

Prompted by a friend's tweet to day I tried to find this software again, but instead I found a piece of software called Synergy2 which does the same thing and by the looks of it a bit more with clipboard changing, nice. Plus it's open source, free!

If anyone has had any experience of this software then leave your experiences of it bellow.

ZX: All The Colours (Colors?) of the Rainbow

It is probably this machine (ZX Spectrum+, see photo) that started me on the rocky road to working in IT. Many an hour was spent loading programs from tape, the reassuring electronic screech still rings in my ears (I should get that checked), the multicoloured loading pattern is etched in my memory and the electronic attempt at the sound of a motor as I played F1 simulators.

I spent many an hour coding in BASIC, typing in reams of code from books to be disappointed by the resulting game. We all have to start somewhere but .NET kids nowadays don't know the half of it!

Many computers followed, BBC Micro, Acorn, Atari, 286, 386, 486, Cyrix 586 and then they get a bit boring and modern for my liking.

Great news for iPhone users though, for the princely sum of £0.59 you can get a ZX Nostalgia app and play some classic games so check it out.

That is my inner super geek purged for a while.

LOBs the wrong and the wright way

I've been troubleshooting an ORA-06502: PL/SQL: numeric or value error although there is an official explanation of what causes this problem it is all to often not obvious where to look to resolve it, no line numbers or anything.

Although clobs can in theory store gigs and gigs of data if you dont handle them correctly Oracle will throw up an error long before you even get to 1 gig of data. Secondly if you do not do things correctly then Oracle will re-index a clob every time you append data to it, this uses up unnessary processing power on the database server.

So if you are writing clobs make sure you

1. CREATE TEMP CLOB
2. OPEN CLOB
3. WRITE/APPEND CLOB
4. CLOSE CLOB
5. FREE CLOB

The code might look a bit like this:

DECLARE

V_CLOB CLOB;
V_IN VARCHAR2(10) := 'TEXT IN';
V_IN_LEN NUMBER;

BEGIN

--Creates clob or blob in temp tablespace
dbms_lob.createtemporary(V_CLOB, TRUE);

--opens the clob in read/write mode
dbms_lob.open(V_CLOB, dbms_lob.lob_readwrite);

--if you have lots of data to append you can use a loop here

--get lenth off data to add to clob
V_IN_LEN := LENGTH(V_IN);

--use writeappend to add text to the clob
dbms_lob.writeappend(V_CLOB, V_IN_LEN, V_IN);

--if you were using a loop you would probably end it here

--close clob
dbms_lob.close(V_CLOB);

--although it's closed you can still access the clob here

dbms_output.put_line(V_CLOB);

--when you dont need the clob data anymore ditch it!
DBMS_LOB.FREETEMPORARY(V_CLOB);

END;

ESCAPE!

Just a quick and simple tip today.

If you are using TO_CHAR in PL/SQL or Oracle SQL you can escape any non-formatting characters by surrounding them in double quotes. For some reason they don't mention this on PSOUG or techonthenet.

Try it out with this SQL statement:

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD"T"HH:MM:SS"Z"') FROM DUAL

XPath Visualizer

Having been doing a bit of work with XML and XPATH recently I've been using the XPath Visualizer tool which has been a bit of a life saver.

It allows you to test your XPath statements against any given XML and highlights the XML that would be selected, very simple but very handy.

Check it out here: http://xpathvisualizer.codeplex.com/

SOAP on a rope

I'm doing some work with SOAP requests at the moment and I wanted a program to test the web services without having to use my own code. There were not that many offerings out there but soapUI seemed to fit the bill.

It comes in an open source FREE flavour and a pro version but the open source version is pretty good.

Javascript Window Woes!

I'm trying to get windows to resize in Javascript, this sounds simple however it turns out to be a pain in the backside.

I am opening a window using window.open() and then trying to resize it and reposition it using resizeto() and moveto(), this is fine in FireFox but in Internet Explorer I hit issues. Because the page in the child window that I open is hosted on a different domain than the parent page IE hits you with a Permission Denied error.

For now I have got around it by defining the height and width in the window.open() command but this is far from perfect as it does not guarantee the position of the browser on the screen.

You can add your country's FIFA World Cup 2010 fixtures to your Google Calendar by:

1. Clicking Add under Other Calendars on the left hand side
2. Select Browse Interesting Calendars
3. Select the Sports tab
4. Select Soccer
5. Select FIFA World Cup
6. Click Subscribe next to you preferred teams

If you want to add all fixtures then Ryan Cullen has done the hard work for you (http://blog.artesea.co.uk/2009/12/world-cup-2010-google-calendar.html) , you can click the + Google Callendar link at the bottom of his callendar to add it to yours.

Oracle have bought in the much needed PIVOT feature in their 11g database to make our lives much easier, for those of us still living in the past it is still possible to "pivot" your data.

Take the following data for example recording (rare) bird sightings:

Id Bird Sighting
===================
1 Pigeon 01-JUN-10
2 Gull 01-JUN-10
3 Crow 02-JUN-10
4 Gull 02-JUN-10
5 Gull 02-JUN-10

To pivot this data you need to use DECODE. Decode allows to take a value, compare it and if it matches the value replace it with another value (check out PSOUG.org for a better example).

DECODE(input,compare,replace,else)

The following SQL will take the data and give you the bird names across the top and dates down the side:

SELECT Sighting,
SUM(DECODE(Bird,'Pigeon',1,0)) Pigeon,
SUM(DECODE(Bird,'Gull',1,0)) Gull,
SUM(DECODE(Bird,'Crow',1,0)) Crow
FROM Birds
GROUP BY Sighting

And you should get something like this:

Sighting Pigeon Gull Crow
==========================
01-JUN-10 1 1 0
02-JUN-10 0 2 1

This is fine however you need to hard code all the bird names, this is not very easy to maintain in the long run. In this example to way to get over this is to have the SQL query stored as a view, if you have a table containing the bird names you can then use a trigger to rewrite the view every time a bird is added to the table.

To run a create or replace view from within a PL/SQL trigger you can use the EXECUTE IMMEDIATE command.

Those sneaky people at Microsoft have been trying to hide some of the sound recording functionality in Windows 7.

If you want to record the stereo mix from your sound card you might find that it has been disabled by default, to enable it do the following.

1. Right click on the speaker and click Recording Devices.
2. Right click within this window and select Show Disabled Devices.3. You should then be able to see a Stereo Mix icon, right click on this and select enable.
You should now be able to use your stereo mix as a recording source.

Computer Based Training

There is no substitute for face to face training however if you are doing an upgrade or new installation of an application for thousands of employees this might not be feasible.

You could take the approach of using "Lead Users" within departments to disseminate good practice and training or you might be in the fortunate position to be able to send out a team of trainers in to you workplace to deliver training sessions.

One other solution that you could use to compliment your other training is Computer Based Training (CBT), this could take many forms, you could use screen recording software, pod casts, online quizzes, vod casts etc. One solution that I have Quarbon Viewlet Builder, this has the advantage of being similar to screen recording software but being able to modify mouse interactions, add in quizzes and getting users to interact with the CBT as they would with the software when they come to use it.

Definitely worth a look, version 6 was a big improvement over version 4 with lots more added functionality. I had some issues opening version 4 files in version 6 but the support guys at Quarbon quickly sorted them out and patched the software up with the new changes.

Take a look at what they have to offer here:

Carriage Return & Line Feed

Why use chr(10)||chr(13) when you could user utl_tcp.crlf?

the problem with jobs

Often a job only breaks because it wasn't able to access another server temporarily for example, in the meantime the server comes back online but the job is still broken.

Unless a user tells you you might never know that then job has broken, but there is something you can do about it. You can create a "fixer job", this is a job that looks at all the other jobs at a set interval and if it finds one that is broke then it fixes it, you could even get it to log the fix or email you when one of the jobs break.

This is the procedure I use in PL/SQL to fix it:

CREATE OR REPLACE PROCEDURE job_fixer
AS

CURSOR broken_jobs_cur
IS
SELECT job
FROM user_jobs
WHERE broken = 'Y';

BEGIN
FOR job_rec IN broken_jobs_cur
LOOP
DBMS_JOB.BROKEN(job_rec.job,FALSE);
END LOOP;
END job_fixer;

My view on views

I love materialised views, if you're pulling information in from another database over a slow network they can be very use full, they avoid having to do a live query on the remote data every time and speed things up for the user.

The problem comes when you have to re sync the data, now you could do this intelligently and only pull in the stuff that has been updated but quite often I do a complete refresh of the view. When doing a refresh sometimes queries can take a long time (5-15 mins) and while this is going on your users can't access the data.

To get over this problem you can refresh your data in a temporary materialised view which is held locally to where you need to use it, this is the bit that takes a long time. Then you can refresh your "live" materialised view with the data in the temporary view meaning that the user experiences almost no downtime.

Multiple row update from select

Whenever I have to do a multi row update from a select statement in Oracle I always come back to this bit of code from the good people at psoug.org who show you how it should always be done, it's not as simple as it might seem on the surface. If you don't do it correctly you end up updating all of the records in the table, some potentially with null data.

UPDATE test t
SET (tablespace_name, extent_management) = (
SELECT DISTINCT (u.tablespace_name, u.extent_management)
FROM user_tables a, user_tablespaces u
WHERE t.table_name = a.table_name
AND a.tablespace_name = u.tablespace_name)
WHERE t.table_name LIKE '%A%';

You can see their full low down on updates here: http://psoug.org/reference/update.html

BBC iPlayer Freezing

If you are having any issues with BBC iPlayer freezing and pausing intermittently it probably isn't a problem with your bandwidth but a problem with Adobe Flash.
When you install Adobe Flash on your PC or laptop it uses hardware acceleration by default. So when you are viewing a BBC iPlayer movie left click on the movie and go to Settings.


Then untick the Enable hardware acceleration setting and click Close


This should hopefully get you working.

If you are ever trying to combine two Oracle PL/SQL collections using MULTISET UNION and get an error that looks something like this:

PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2045; Type 0x0A3B7470 has no MAP method.; NEWS_FEEDS__UP__F__103002[7, 5]]

You probably need to run this command on the object type of that data you are merging:
alter type [Item Type Name] add map member function m  return number invalidate/