Thursday, October 27

Auto incremental primary keys in Oracle

Why doesn't Oracle support auto incrementing a number column?
The logical way to do this would be to allow a sequence to be tied to a column default value.

This "should" work.
create table itest (
id number(9) default seq_itest_id.nextval primary key,
name varchar2(10) not null,
age number(2)
);

But it doesn't. The concept exist in this way in Postgres (using a sequence, it can even do all the association itself if you specify datatype 'serial'. MySQL has its' own quite odd way to this, simply just "auto increment" the field without any real flexibility or control... the typical MySQL approach ;)

I've been helping a friend port a MySQL schema/app to Oracle without needing to change the app code to much, so auto incremental id's was a must.

This is the way to do it, using a very simple trigger.
drop table itest;

create table itest (
id number(9) primary key,
name varchar2(10) not null,
age number(2)
);

drop sequence seq_itest_id;

create sequence seq_itest_id;

drop trigger itest_id_trigger;

create trigger itest_id_trigger
before insert on itest
for each row
begin
select seq_itest_id.nextval into :new.id from dual;
end;
/

insert into itest(name,age) values('Scott',25);
insert into itest(name,age) values('Sarah',24);
insert into itest(name,age) values('Billy',33);

-- Ok, lets see what we got in our table
select * from itest;

ID NAME AGE
---------- ---------- ----------
1 Scott 25
2 Sarah 24
3 Billy 33

Not to difficult.

Thursday, October 20

Cute HTML reports from psql

Most people are aware that it is possible to dump quite decent HTML reports from Oracle SQL*Plus, but not many people know that the same is also available from PostgreSQL psql tool.

I've written a few scripts and template for a small project and thought it could be fun to share. The framework simply looks in a directory for .sql files and execute them using psql -f FILE.sql and expects the script to spool a file with the same name but the prefix .data.html. It then concatinates the script with a header and footer HTML file to create a pretty report. This is without using any PHP/Perl or server side parsing. Perfect for things like static weekly reports.
Modify the script after your needs and slam it in to cron.

Show example.

Download the basic framework tarball. The docs aren't great tho :)
Included the sample data and query to generate the example.

Tuesday, October 18

Day off and DSL installed

Have had the day off! Yes!

Was busy with 'something' this morning but all afternoon I slacked around. Or not slacked around perhaps, went shopping and even found time to get a hair cut.
First time in almost two years I've had a day off.
I think I'm going to take new Tuesday off as well. Things are sort of slow in the office right now so it shouldn't be a problem.

Secondly
My ADSL modem started flashing green today instead of the annoyingly red.
The PPPoA line syncs in at around 5.2Mbit, can't say I'm overthrilled by the speed but it is definately good enough. Someone told me that the Speedtouch 330 USB-modem Bulldog offers for free suck donkey balls and that the speed is usually alot better when using a high-end router.
I'll see if I feel downloady enough to bother.

IRC topic of the day
Todays topic in #oracle on FreeNode has been PL/SQL. Seems like alot of people are migrating from MySQL systems and tend to over-use PL/SQL wherever they can, even where plain SQL is enough. Hmm.
I should get around to write that small PL/SQL tutorial I've been thinking about for a while. Just some basics to stop people from pasting SQL-queries straight in to PL/SQL and expecting the column aliases to be referable.

Note to self.
Fix special character bug in the phpNuke WAP interface.

Monday, October 17

Cool breeze of AMD

Question:
-What is the average temp of a desktop CPU these days?

The last time I built a PC for myself and actually cared a bit about the components and performance of the machine it was a AMD XP1800+. I remember spending something like £40 GBP on a better CPU fan and chassi fan to get the temp down and keep it reasonably quiet. However, I still had a CPU temp in the high 40's. Usually around 47C if I remember correct.

The new box I built the other day with a AMD64 3200+ proc using the retail boxed fan and a normal 12cm chassi fan has a CPU temp of 28C. Yes, that is correct. Both the BIOS and the awful "GigaByte EasyTune" reports the same temp. I was quite impressed tbh.

Coolness! :-)

CD recommendation:

The Cardignas: Super Extra Gravity

Ok, it doesn't top Long Gone Before Daylight but the new Cardigans CD is great. I'd say number two on their list.

Sunday, October 16

apt-get install ubuntu

So I finaly got around to buying a new computer. Built a simple AMD64 box (Nope, did not go dual core) with parts bought from my Scandinavian friends over at Komplett.
With a new machine I of course needed an OS and since Ubuntu has gotten so much press lately I sort of felt it was my duty to test it.
Ok, I have to admit, I was quite hesitant of using a Debian distro after being forcefed RPM's for the last 5 years and Solaris 10 DVD's for the last year.

However, I was quickly proven wrong :)
Install was a breeze and the default system looked quite alright.
After first reboot X failed to start due to that the installer hadn't found my ATI x800 graphics card (PCI-e) but the driver was just an apt-get away. Downloaded and ran fglrxconfig to configure my settings (damn thing did not add the Zaxis-mapping to make the scroll-wheel work tho).
No more problems after that, I added a few more apt repos and installed some more basic software.
All in all good but I must say I felt a bit confused with the file structure of the PostgreSQL 8 install with files all over the place. I'm used to compiling it myself to a dedicated dir, most often /u01/postgres with the datadir under /u02/pgdata. No a big issue anyway.

If I feel motivated enough I will install Solaris 10 on the box as well. Need somewhere to run Oracle. Perhaps VMWare is the solution?

Oh, and I forgot to write that I bought a new monitor a month back, an Iiyama Prolite E481S (8ms response and 700:1 contrast). The picture quality is truly amazing. Almost comparable to a good CRT. It really came to good when hooked up to the new ATI card.

Le desktop de Ubuntu :-)
Desktop

Ciao!

PS. Finally got my Bulldog DSL install date. Tuesday 18th this week. *Yeay*

Thursday, October 6

London broadband suckyness

I got sucked in to a great broadband deal the other month.
8Mbit broadband and free telephone line for only £20 per month.
Great I thought.
Now six weeks later and not a beep from the ISP I am a bit puzzled if it was such a good deal after all.

By now I think about 90% of all brittons have figured out that I signed up to Bulldog Broadband a part of Cable & Wireless.
In short; they suck and I should have known better.

Bulldog fan sites
http://www.my-bulldog-hell.co.uk/
http://www.bulldog-hell.com/


I should just have waited for the Swedes :D
http://www.bethere.co.uk
24Mbit service in central London before xmas. Yes please!