suburban exile

Oracle slow CLOB / STRINGS

2012-03-15T17:23:00.003+00:00

For those of you that have used oracle string functions such as SUBSTR you'll know that it can end up being very slow if you are trying to repetedly scan through long strings of data.

I sometimes use this kind of function when looping through rows of a CSV or XML file, it gets increasingly slow as it gets further in to the string.

To get around this once you have processed a row of a CSV file chop the bit you have finished with off and then there is not as much to search through next time around.

Oracle Apex Reports

2011-12-07T11:19:00.002+00:00

In Oracle Apex 3 (possibly 4) there are a number of views that allow you to query the tables that form the apex pages, this includes vies of forms, items, buttons etc.

I've found this particularly useful when looking for particular bits of SQL and PL/SQL code where it is not practical to look through all of the pages.

To list the apex tables run this query on the system schema:

select * from ALL_VIEWS
where VIEW_NAME LIKE 'APEX_%'

and the views that I've found most useful are the APEX_APPLICATION_PAGE_* tables.

Exception Exception!

2011-05-03T11:25:00.002+01:00

Exception messages can be less than helpfull sometimes so for that extra bit of information user

DBMS_UTILITY.FORMAT_ERROR_STACK

Within your exception statement to provide further information about the location of the error.

Oracle Portal Session Variables

2010-10-13T08:20:00.003+01:00

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

2010-09-05T18:26:00.006+01:00

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

2010-08-11T16:13:00.007+01:00

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

2010-08-09T15:15:00.001+01:00

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

2010-08-09T10:50:00.000+01:00

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

2010-08-02T17:39:00.000+01:00

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

2010-07-14T11:24:00.000+01:00

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

2010-07-13T17:48:00.000+01:00

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

2010-07-05T14:43:00.000+01:00

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

2010-06-30T15:27:00.000+01:00

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

2010-06-25T13:39:00.000+01:00

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

CREATE TEMP CLOB
OPEN CLOB
WRITE/APPEND CLOB
CLOSE CLOB
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!

2010-06-17T10:37:00.000+01:00

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

2010-06-15T12:49:00.000+01:00

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

2010-06-08T08:31:00.000+01:00

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!

2010-05-25T15:17:00.000+01:00

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.

2010-05-19T14:51:00.000+01:00

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

Clicking Add under Other Calendars on the left hand side
Select Browse Interesting Calendars
Select the Sports tab
Select Soccer
Select FIFA World Cup
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.

2010-05-02T19:21:00.000+01:00

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.

2010-05-01T22:05:00.000+01:00

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

2010-04-30T10:45:00.000+01:00

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

2010-04-30T09:52:00.001+01:00

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

the problem with jobs

2010-04-27T15:10:00.001+01:00

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

2010-04-27T14:58:00.001+01:00

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.