pcWatcher 1.0 Uploaded

I have just uploaded pcWatcher to my website. A screenshot can be viewed here and the program can be downloaded free of charge here. Here’s an extract from the README file supplied with the program:

PCWATCHER is a Windows utility, written in .NET that performs three functions:

Memory Monitor: pcWatcher monitors the amount of memory that you are using and displays it in real time within the window.

File Monitor: pcWatcher can monitor files that are Created, Deleted or Changed and displays these to the screen.

Spy Alert: pcWatcher checks for the existence of the spy programs Spector Pro and eBlaster, and alerts you if these are installed on your system.

In addition pcWatcher displays additional information such as the status of your network connection, the version of windows you are using, the computer and user name.

"Time is the coin of your life. It is the only coin you have, and only you can determine how it will be spent. Be careful lest you let other people spend it for you." - Carl Sandburg

GenDAT: Fantastic Software

www.fantasticfreeware.com have included GenDAT in their collection of 2000 of the best Freeware programs. Good Stuff ☺

"If you treat people right they will treat you right - ninety percent of the time." - Franklin D Roosevelt

Oracle: User Account Security ...

Oracle has a bewildering array of security features to help you to make sure that your users access only what they need and to ensure that you have very fine control over their abilities.

A couple of things you should look at first, the following parameters are off by default and you should make sure that unless you have a very good reason, they stay set to false:

O7_DICTIONARY_ACCESSIBILITY = FALSE
REMOTE_OS_AUTHENT = FALSE

In addition you should check the privileges that have been assigned to PUBLIC and remove any that you are not comfortable with. Remember the principle of ‘Least Privilege’.

You should also absolutely minimise the amount of users who have ADMIN access.

Whenever an Oracle database is created the users SYS and SYSTEM are also created. If you use DBCA (Database Configuration Assistant) to create your database you will also have SYSMAN and DNSNMP.

SYS - Data Dictionary Owner
SYSTEM - Admin Account
SYSMAN - Used by Enterprise Manager (EM)
DBSNMP - Used by Enterprise Manager (EM)

Check all the default accounts that are created and make sure that those you don’t initially need are locked and have their password expired. The following statement shows how this is accomplished:

ALTER USER pip PASSWORD EXPIRE ACCOUNT LOCK

Assuming you are using database authentication of your users, then password security rules are enforced through profiles. Listed below are some of the password security profile parameters that you can use:

FAILED_LOGIN_ATTEMPTS - maximum consecutive count
PASSWORD_LOCK_TIME - days (or part of ie: 5/1440 = 5 Minutes) to lock the account
PASSWORD_LIFE_TIME - maximum days for this password to exist
PASSWORD_GRACE_TIME - number of days following the first successful login after the password expiry and the users are reminded to change their password, but can still log in
PASSWORD_REUSE_TIME - minimum days before password reuse
PASSWORD_REUSE_MAX - minimum changes of password before reuse allowed

These parameters are set in the following way:

CREATE PROFILE passwordlimit LIMIT
PASSWORD_LOCK_TIME 1
FAILED_LOGIN_ATTEMPTS 3

"Courage is fear that has said its prayers." - Dorothy Bernard

Oracle Views

An Oracle database consists of a vast amount of underlying metadata that stores information about the database itself. This metadata is extremely cryptic and as such Oracle have provided ‘user friendly’ views to enable the user or DBA to view this data in a meaningful format.

There are two types of Oracle Views:

Data Dictionary Views
Dynamic Performance Views

Data Dictionary Views begin with DBA_ , USER_ or ALL_ . The difference is as follows: DBA_ Views show information on all the tables in the database, ALL_ shows information on all the tables that you own or have been granted access to. USER_ shows information on all the tables that you own.

There are some differences between Data Dictionary Views and Dynamic Performance Views:

Data Dictionary Views usually have plural names (DBA_TABLES), Dynamic Performance Views, singular (V$DATAFILE)
Data Dictionary Views are only available whilst the database is open, some V$ Views are available when the DB is not fully open
The data in Data Dictionary Views is usually UPPERCASE whilst V$ data is usually lowercase
The data in Data Dictionary Views is static and not cleared when the DB is restarted, V$ data is cleared after a restart

Examples of Data Dictionary Views

DBA_TABLES
DBA_TABLESPACES
DBA_USERS
DBA_VIEWS

Examples of Dynamic Performance Views

V$VERSION
V$OPTION
V$DATABASE
V$SQL

"The truth is not simply what you think it is; it is also the circumstances in which it is said, and to whom, why and how it is said." - Vaclav Havel

Oracle: COMMENT ON ...

A little known / little used feature of Oracle is the COMMENT ON TABLE / COMMENT ON COLUMN facility. Basically this allows each table and column to have a descriptive text applied to it. It is common to have descriptive text for both tables and columns contained within your application schema, or in a separate data dictionary type document, but it would be nice for that information to be available via external tools that access and / or modify the database without referring to separate documentation.

The feature is used in the following way:

COMMENT ON TABLE mytable IS ‘This is my Master Table’

COMMENT ON COLUMN mytable.primarykey IS ‘Unique ID from Sequence SEQ_MASTER’

The comments can be displayed by querying the following views:

DBA_TAB_COMMENTS
DBA_COL_COMMENTS
ALL_TAB_COMMENTS
ALL_COL_COMMENTS
USER_TAB_COMMENTS
USER_COL_COMMENTS

If you have a schema within your application that contains comments on both the table and column levels it should be very straightforward to write a small program to loop through your schema data and write those comments into Oracle. It could be a couple of hours well spent.

"The excellence of a gift lies in its appropriateness rather than in its value." - Charles Dudley Warner

Only In America ...

Rockdale USA. Police have initiated a program of placing a yellow sticker on your car if you happen to have left valuables on display. The sticker warns of the dangers of leaving valuables where thieves may see them.

Of course if you happen to be a thief you can just target the cars with yellow stickers .... mmmmm ....

For the article see here.

"If you are pained by external things, it is not they that disturb you, but your own judgment of them. And it is in your power to wipe out that judgment now." - Marcus Aurelius Antoninus

Would You Trust ...

Would you trust the UK Government to run a National ID Card Database ?

The recent loss of a disk containing 25 Million Child Benefit details is incredible. Not only did they lose the disk but they neglected to encrypt the information on it. It is quite possible to, very simply and cheaply, encrypt information in such a way that would make that disk worthless to anyone who gained access to it.

Instead, Bank Account details, Dates of Birth, National Insurance Numbers for millions of people are out in the wild. This should *never* happen. Ever. There is absolutely, categorically no excuse for this.

Pathetic.

"The guy who takes a chance, who walks the line between the known and unknown, who is unafraid of failure, will succeed." - Gordon Parks

Security, Slippery Slope ...

In October 2007 a law came into being that allows the government to force you to hand over encryption keys so that they can access your secured information. To the layman this is a necessary weapon in the war against terrorism, and that is exactly how it was sold to the public.

So why this month was this ‘Section 49 / Section 51’ law used to force a group of animal rights activists to hand over the encryption keys for their computers ?

A law that is only 2 months old and is already being used and abused by the powers that be. The ‘threat of terrorism’ is being used by both the British and American Governments to implement laws that would not be passed otherwise. Our personal freedoms are being eroded daily and most of us have not even noticed - yet.

Thanks for Bruce Schneier for revealing this.

"To die is nothing; but it is terrible not to live." - Victor Hugo

Trouble with Oracle 10g on a VM

I have been having trouble until recently running Oracle 10G within a Virtual Machine environment. I have tried Parallels / VMWare / Virtual Server and Fusion. Always the same result, the database runs ok and maybe survives a couple of reboots, however sooner or later the database will refuse to start. The Operating System installed within the VM is Windows Server 2003 R2.

I have found a solution that works to restart the database successfully, although I have not yet found the reason for the problem in the first place. If you find yourself in a similar situation and you are presented with the following when attempting to connect to your Oracle Database:

ERROR:
ORA-01034: ORACLE Not Available
ORA-27101: Shared Memory Realm Does Not Exist

then this is what you need to do to start the database:

Firstly open the Command Line shell and perform the following (shown in bold):

C:\ sqplus /nolog
SQL> conn system/password as sysdba
SQL> shutdown abort
SQL> startup

Here is an alternative method that has also worked for me:

Create a file in the root of drive C: called startup.sql, these are the contents:

startup
exit

then create a file called Startup.Bat with the following contents:

c:\Oracle\product\10.2.0\<SID>\bin\sqlplus -s "/ as sysdba" @ c:\startup.sql

(Obviously replace <SID> with your database ORACLE_SID)

When you wish to start the database just run the startup.bat batch file.

That’s it. With luck your database with startup as required and all is well with the world. Now to find out why it happens in the first place ...

"You cannot escape the responsibility of tomorrow by evading it today." - Abraham Lincoln

Oracle: Flashback Query

The Oracle Flashback Query allows users to look backwards in time and see a consistent view of the database as it was sometime in the past. This facility is only relevant if your server is configured for Automatic Undo Management. How far backwards you can see is determined by the UNDO_RETENTION parameter (in seconds) which can be configured in the init.ora file or alternatively like this:

ALTER SYSTEM SET UNDO_RETENTION = NNNN

This facility is mainly used for recovering from accidental updates and deletes as it is not practical to set the UNDO_RETENTION to an extended period. Nevertheless it is a potentially very powerful facility.

"True humor is fun - it does not put down, kid, or mock. It makes people feel wonderful, not separate, different, and cut off. True humor has beneath it the understanding that we are all in this together." - Hugh Prather

Oracle: Flash Recovery

A new feature available in Oracle 10g is Oracle Flash Recovery. Flash Recovery is designed to make it easier and more straightforward to perform Oracle Backups and Recovery by providing a Centralised Location for all the files related to your database backup.

The Flash recovery Area is an area of a disk, or a separate disk, that is dedicated to the storage of database backup files and recovery information. It is held completely separate to all other areas of the Oracle database.

The Flash Recovery Area is used in the following ways:

Enterprise Manager can stores its backups here
Oracle can store the Archived Redo Logs here
RMAN can store and maintain details of it’s files here

In short: All files necessary to recover the database in the unlikely event of a media failure are stored as part of the Flash Recovery Area.

The default location for the Flash Recovery Area is ORACLE_BASE/flash_recovery_area. The size of this area defaults to 2GB but can be altered as necessary.

I see the Flash Recovery Area as a simple but extremely powerful and useful facility within Oracle 10g. Only the naming is unfortunate, this is not to be confused with another new feature that came out with 9i, the Flashback Query or Flashback Table facility.

"Everyone is necessarily the hero of his own life story." - John Barth

Oracle: Startup Procedure

The Oracle startup procedure follows these stages:

NOMOUNT: The Instance Starts and the Initialisation Parameter File is read and interpreted by Oracle.

MOUNT: The First Control File is read by the Oracle Instance.

OPEN: The Redo Log Files and the Datafiles are accessed by the Oracle Instance.

"Concentration comes out of a combination of confidence and hunger." - Arnold Palmer

Oracle: What Happens When ...

... An UPDATE statement is performed.

The following is an accurate (if fairly simplistic) description of what happens when an UPDATE statement is executed against an Oracle database.


The data set for the update is taken from the DB CACHE.

The ‘Pre Change’ data is written to the UNDO CACHE.

The ‘Post Update’ data is rewritten to the REDO CACHE as a delta (a piece of data that contains enough information to reconstruct the original data). The ‘Post Update’ data from the UNDO CACHE is also written to the REDO CACHE (alternatively known as the REDO LOG BUFFER).

After a COMMIT is issued the LOG WRITER Process (LGWR) writes the REDO CACHE to the REDO LOG FILES.

If ARCHIVELOG is enabled then the ARCHIVER Process (ARCH) writes a copy of the REDO LOG FILES to an alternative destination(s) (Up to 10) before the REDO LOGS are reused.

The CHECKPOINT Process (CKPT) is called regularly and this ensures that changed data (dirty blocks) are written from the DB CACHE and back to the datafiles. In addition this process updates the CONTROL FILES.

“Weaseling out of things is important to learn. It’s what separates us from the animals — except the weasel.” - Homer Simpson

OTT Enduro !

I spent Saturday tearing around the Peak District riding a Yamaha WR250F Enduro bike. Hammering gravel lanes and boulder strewn descents has long been an ambition of mine and so I got in touch with Overlander Trail Tours (OTT) and booked a days guided session with one to one tuition. It’s a long and tough day which starts at around 08:30 and finishes at 16:30, over this period you cover approx. 100 miles of prime Peak District terrain.

It’s worth mentioning here that OTT are a very professional outfit. They stick to tracks that are legal and treat all other trail users with respect and often stop and talk to people on the trail which serves as good PR for the sport, so many idiots give off-roading a bad name and it was good to see that OTT do their best to leave people with a good impression. Mike my guide was excellent both at riding his bike like he stole it and also at promoting the sport in the best possible way.

The drive up to the headquarters of OTT was interesting, taking me past the famous ‘Cat and Fiddle’ pub where I managed to stop and get a few pictures of the sun rising over the Peaks, and of the fantastic scenery over the moors.

Photo1
Photo2
Photo3

Having looked at their homepage I kind of expected lots of terrain similar to that shown in the top picture, however as I discovered later there seemed to be more of the kind of the terrain shown in the second picture ... So it was that I was a bit bemused when I had to wear knee, elbow, shoulder, back and chest armour as well as armoured boots and hard wearing jacket and trousers - I felt a bit like Judge Dredd ! As it was I had good reason to be thankful for the armour ...

Crash Number 1 occurred after around an hour of riding, I was getting used to standing up over the bars letting the bike do its thing as we hurtled up a track over boulders and ruts and I was feeling good, maybe going a bit faster than my newly found but limited abilities needed, anyway, near the top of the track prior to a 90 degree bend was a large dip in the ground which I entered and shot out of with no front wheel on the ground to steer with ... the wheel touched down just prior to the handlebar guards and my shoulder impacting a dry stone wall. Which luckily collapsed and so absorbed most of the impact ... So with no damage other than a few scuffs (to the bike, not me) I picked the bike up and we carried on.

A fantastic part of the day was spent thrashing the bikes down the gravelly quarry lanes, with only a 3 feet high chicken wire fence separating us from the sheer drop on our left, it never felt overly dangerous though as the quarry roads, although loose gravel, were fairly smooth and these bikes are fantastic, their handling and braking is in a league of their own.

Later on I did my first river crossing which was fantastic and seemed to involve very little skill to be honest. Keep the revs up, the speed constant, relax and let the bike do it’s thing. One thing I learned from this day is that courage is key - lose your bottle and you lose the bike, tense up and you will soon crash, the bikes are so good that they require minimum input to control, small relaxed fluid movements and a pair of big b******s are key to having a successfull and fun time when riding hard offroad.

Crash Number 2 (the reason that I am still stiff as a board 2 days later I think) occurred in the last 30 minutes of the day. Hurtling up a rock strewn ascent, that we had actually descended successfully earlier that day, I lost control, twisted the throttle back by accident and shot out of the rut into the wall at the side of the trail. This wall didn’t collapse and with the bike on top of me and the smell of petrol I lay under the bike feeling rather sorry for myself. I suspect I lost concentration or strength or both as by this time I was shattered and that was the result.

Fortunately the only damage to the bike was a front mudguard snapped in two, which was not classed as a slight scrape and so I had to pay £30 to replace it. One of the good things about OTT is that your maximum financial exposure is £100 so even if I had written the bike off all I would have had to pay would be £100.

Here’s a photo of me post Crash Number 1 and pre Crash Number 2 ...

Following this incident we headed back to HQ, the end of a fantastic day. I will definitely go again. I’m still unsure whether I will buy my own bike and take it up as a regular sport but there is no denying it is a lot of fun and very good exercise. Definitely recommended ☺

“Facts are meaningless. You could use facts to prove anything that’s even remotely true!” - Homer Simpson

Applying for an EHIC

The EHIC (European Health Insurance Card) is a replacement for the old E111. It allows you to receive medical cover, free of charge when traveling abroad in most parts of Europe, similar to what you are likely to receive in the UK. Applying for the EHIC is easy - but can be expensive.

A web search for EHIC gives a website which gives you a telephone number (0871 050 0508), and advises you that this will cost 10p a minute. This telephone number gives you a second telephone number (0906 120 2345) which costs £1.50 per minute ....

After £12 worth of phone call you are finally given a web address where you can apply - WWW.EHIC.ORG.UK. £12 to be told that ... the card may be free but when it costs £12 to find out how to apply ...

The website takes all of two minutes to complete, your name, address, date of birth and national insurance number is all that is required. Your card should then be received in 7 working days.

So, save yourself the expense and hassle and go straight to www.ehic.org.uk. You know it makes sense ☺

"Do not protect yourself by a fence, but rather by your friends." - Czech Proverb

Yamaha XT660R Review

I have written a review of my XT660R motorbike. You can download the PDF here, or read the published version here.

“Well, I’m tired of being a wannabe league bowler. I wanna be a league bowler!” - Homer Simpson

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

New Upload: AutoSQL Oracle

I have just uploaded AutoSQL to my website (www.genesyssolutions.co.uk).

AutoSQL is a utility that allows you to execute regular queries against your database and email the results set to you. It is designed to run under Windows from a scheduled task. It starts up at a predetermined time, executes the query, emails the results to you, then exits.

AutoSQL supports Xcopy Deployment, and as such installation consists of dragging and dropping !

If you are looking for a tool that is freely and easily available, as well as being easy to use and powerful, that you can deploy without any installation or licensing issues then AutoSQL may be of use to you.

AutoSQL can also be downloaded directly from here.

"Don't be a fool and die for your country. Let the other sonofabitch die for his." - George S Patton

Wireless Broadband / No Security

I called in at a friends house Saturday, he’d just had his broadband package updated and moved from a broadband modem to a wireless router based system. After checking out the security for him these were my findings:

1. His computers are connected to the router via Ethernet cable, he has no need for the wireless capability of the browser.
2. Yet it was switched on at the router.
3. The wireless on the router was totally unsecured.
4. The router was accessible from the internet and there was NO administrator password required.

He installed this router himself, following the instructions provided by the ISP. It seems that either the ISP does not know about how to correctly install a broadband router, or the ISP couldn’t be bothered to get decent installation instructions written.

To be honest I was disgusted. It took all of 5 minutes to secure his system properly and to show him what what was required. The lesson is do not trust your ISP to give a damn about your security, even though it may affect their network.

"Every mile is two in winter." - George Herbert

New Upload: GenDAT

I have just uploaded GenDAT to my website (www.genesyssolutions.co.uk).

GenDAT is designed to be a useful, easy to use query tool for use with SQLServer and Oracle databases. There are a few of these type of programs around, including of course the ones supplied with the database clients themselves. GenDAT however is designed to be easy to use, fast and with some very nice features including multiple results grids, query history and export to XML and HTML.

One nice feature is that GenDAT supports very large grids, depending on the number of columns and the type of data contained, it is possible (if you really, really wanted) to load more than one million rows into the grid ! GenDAT is written using the .NET Framework 2.0 and as such requires that the framework is installed on the host machine.

GenDAT supports Xcopy Deployment, and as such installation consists of dragging and dropping !

GenDAT supports multiple instances, so if the eight Results Sets are insufficient, feel free to launch another version of the program.

If you are looking for a tool that is freely and easily available, as well as being easy to use and powerful, that you can deploy without any installation or licensing issues then GenDAT may be of use to you.

GenDAT can also be downloaded directly from here.

"Never feel self-pity, the most destructive emotion there is. How awful to be caught up in the terrible squirrel cage of self." - Millicent Fenwick

Soapbox Moment: Speed Kills

Speed Kills: So we are told. Only, it doesn’t. The term was coined during the seventies to warn people of the danger of meth-amphetamine and has recently been used to make us all feel guilty anytime our speedometer wanders over the posted limit. Speed doesn’t kill however and the public are not stupid enough to believe this.

The equation is as follows:

        Speed + Inappropriate Context + Bad Driving Skills = Danger

It’s only when the above three factors come together that we have a problem, and with that speed has been relegated to 33% of the equation rather than the 100% that the government currently tell us. I can and do speed, when it is safe to do so and I do not take unnecessary risks with my life or those of others. I am constantly alert, watching the road, reading the traffic. As I now often ride a bike (both motorbike and pushbike) I drive defensively, on a bike you don't have 2 tons of metal cage and half a dozen airbags to protect you.

At 100+mph overtaking in the outside lane of the motorway watching the road, the traffic, the weather, I am far less of a danger to the public than the sales rep in his Audi driving in the middle lane, six inches from the bumper of the car in front, appointment book open on his knee, cigarette in his mouth, phone in one hand and McDonalds shake in the other. But who are the police going to target ?

Why the obsession with speed ? Is it because it’s something that can be controlled and restricted without any human involvement or discretion and without considering context, through the use of Speed Cameras (Piggy Banks), removing the need for real policing or human intervention ? A simple equation:

        69Mph, Homicidal Criminal = No, 71Mph, Homicidal Criminal = Yes

The problem with drumming into our heads that Speed Kills is that everything else is driven from our minds, and with no police on the roads the other dangers are allowed to continue unchallenged. Here’s one example:

        Drunk Drivers. As long as they don’t speed, they get away with it because Speed Cameras can’t detect drunk driving.

Here’s where context comes into it. A drink driver is dangerous no matter what speed he or she is doing. Take for example a wet country lane during autumn with leaves likely to be on the road and a 90 degree bend. The speed limit is 60, but in this context only an idiot would take the bend that fast. How about driving at 50 mph, within the speed limit, of the A38 one morning in thick fog. Too fast given the context. I’m sure you can think of many examples yourself.
        
The bad driving part of the equation has to be tackled. We live in a state where we cannot smoke inside public buildings (public safety issue) and yet we allow a 17 year old with minimal training, a hoodie and a bad attitude to jump into a two ton machine and tear around the roads without fear of retribution (unless he goes through a speed camera) - where is the sense in that ?

When is someone in government going to look at the issue of safety on the roads and put forward a program of changes to address the real issues ? And can we please drop the ‘Speed Kills’ campaign, surely a product from the mind of the mentally challenged !

"Our imagination is the only limit to what we can hope to have in the future." - Charles F Kettering

Types of SQL

There are three basic types of SQL:

DDL, DCL and DML.

DDL:        Data Definition Language
                Used By:                DBA
                Examples:        CREATE, ALTER, DROP

DCL:        Data Control Language
                Used By:                DBA
                Examples:        GRANT, REVOKE

DML:        Data Manipulation Language
                Used By:                USERS, DBA
                Examples:        SELECT, INSERT, UPDATE, DELETE

"Creativity can solve almost any problem. The creative act, the defeat of habit by originality, overcomes everything." - George Lois

Stuff

"Every thing you own takes energy away from you. Some give more than they take. Those are the only things worth having."

Paul Graham writes an excellent article that is so pertinent to me at the current time. I have spent my life, like most other people, accumulating ‘Stuff’ that I don’t need and wouldn’t miss. One of my future goals is to get rid of a lot of this stuff, clear the garage, clear the attic, take it to the tip, sell it on Ebay - whatever. Clutter is not good.

“That lemon tree’s a part of our town, and as kids, the backbone of our economy. We’ll get it back, or choke their rivers with our dead!” - Bart Simpson

Goats Sacrificed to Fix Jet !

From the BBC. Remind me to only fly British Airways in future ...

“The doll’s trying to kill me and the toaster’s been laughing at me!” - Homer Simpson

Speed Cameras - Stealth !

Apparently due to recent changes the Police are no longer allowed to receive the revenue generated by speed cameras, therefore they no longer need to publicise the locations ... Cumbria have already stopped publicising the locations of cameras and I suspect other forces will soon follow. So no longer will we be able to rely on our Road Angel or similar GPS based devices ...

Keep your eyes peeled boys and girls, oh and while you’re scanning the pavements for the tall yellow piggy banks try not to hit anything on the road ...

“Getting out of jury duty is easy. The trick is to say you’re prejudiced against all races.” - Homer Simpson

Oracle Segments

In Oracle a Segment if defined as an entity that consumes physical storage space within the database. A Table is obviously one kind of segment, as is an Index.

Segments are made up of Extents. A Segment can have between one and two billion Extents.

Extents are made up of Oracle Database Blocks. The minimum size of an Extent is five Oracle Database Blocks.

Database Blocks are made up of one or more Operating System Blocks.

The structure for Oracle’s Segments looks like this:

        Segment
                Extent
                        Oracle Database Block
                                Operating System Block


Marge: This is the worst thing you’ve ever done!
Homer: You say that so often that it lost its meaning.

Finding a Good Camping Spot

I’m on holiday the moment on the East Coast and although I’m staying in a chalet at the moment, in comparative luxury, I am looking forward to my next motorcycle camping trip, probably in September. It’s been a while since I’ve camped and I’ve spent some time thinking about finding a good spot and the extra requirements of camping overnight with a motorcycle. I’ve compiled the list of tips below:

1. This should go without saying given the problems recently with flooding, but do not be tempted to camp too close to water in low lying areas. Flooding can occur quickly and unpredictably and can be inconvenient at best and deadly at worst.

2. Avoid hollows in the ground, both water and cold air can collect there.

3. Be sure to check the ground for creatures, creepy crawlies, rocks, roots, animal trails, broken glass etc.

4. Positioning your tent on very slightly sloping ground, and sleeping with your head at the highest point may make you more comfortable.

5. Put something under the stand of your bike if parking on soft ground.

6. Keep yourself out of sight of roads or houses if possible. Outside of the UK particularly you may be at risk camping, especially alone.

7. Avoid camping under trees. Objects can and do fall out of trees, from branches to creatures, it can also be more dangerous during a storm because of lightning.

8. Erect your tent with the door facing downwind, you will be warmer and are less likely to have something undesirable blown into your tent

Something to bear in mind is that in England you need permission to camp pretty much everywhere. As you are unlikely to even know whom to seek to ask for permission, be sensible and as much as possible invisible. Respect the land. Peace Out !

“How is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. Remember when I took that home winemaking course, and I forgot how to drive? - Homer Simpson

Oracle: TO_CHAR Solution

Working with a colleague this morning and the requirement, on the surface was simple: for a selected product, show the sales, in a window, for each month, going backwards in time. Initially we are going to create a loop going backwards in time, a month at a time, doing a SELECT against the database to retrieve the sales for each month.

Andrew Deacon, my colleague, came up with this superb solution which is astoundingly quick and efficient, letting the database do all the work. All we had to then was display the results set on the form, with appropriate column headings. Here is the solution we used:

select TO_CHAR(std_date, 'MONTH YYYY'), TO_CHAR(std_date, 'MM'), TO_CHAR(std_date, 'YYYY'), SUM(STD_TOTAL) from guser.f_strline where std_prcode = ‘PRODCODE’ GROUP BY TO_CHAR(std_date, 'MONTH YYYY'), TO_CHAR(std_date, 'MM'), TO_CHAR(std_date, 'YYYY') ORDER BY TO_CHAR(std_date, 'YYYY') DESC, TO_CHAR(std_date, 'MM') DESC

With a combination of Oracle’s TO_CHAR functionality and GROUP BY, the solution is slick. I like stuff like this ☺

“You can’t keep blaming yourself. Just blame yourself once, and move on.” - Homer Simpson

Oracle String Functions

I was going to do an in-depth article on Oracle String Functions, however these guys have already done one, and it is excellent. Click here to have a look.

“I guess one person can make a difference. But most of the time, they probably shouldn’t.” - Marge Simpson

The Stig

For Fans of Top Gear, here are some of the quotes from Jeremy Clarkson regarding The Stig. Hilarious ☺

"Some say he once threw a microwave oven at a tramp, and long before anyone else he realised that Jade Goody was a racist, pig-faced, waste of blood and organs."

"Some say he communicates only to a car's on board computer through binary code, and that he once killed a man who was towing a caravan, using only a steering wheel."

"Some say he sucks moisture out of ducks and he models his crash helmet on Britney Spears' head."

"Some say he appears on high-value stamps in Sweden, he's illegal in 17 U.S. states, and that his voice can only be heard by cats."

"Some say that his politics are terrifying, and that he once punched a horse to the ground."

"Some say that he has no understanding of clouds, and that his earwax tastes like Turkish Delight."

"Some say he was born in space, he's scared of bells, and confused by stairs."

"Some say his skin has the texture of a dolphin's, and that wherever you are in the world if you tune your radio to 88.4 you can actually hear his thoughts."

"Some say his fingernails have 330 bhp and that he was raised by wolves."

"Some say he once had a vicious knife fight with Anthea Turner, and that he is in no way implicated in the cash for honours scandal."

Some say he only ever points magnetic north, and that he was born in space.

All we know is, he's called the Stig.

“Well, if it isn’t my arch nemesis, Bart Simpson. And his sister Lisa to whom I’m fairly indifferent.” - Sideshow Bob

Oracle: Natural Joins

If you setup your database structure and within two tables you use the same column name, you can then ask oracle to use these columns as the join using the Natural Join facility. For example:

Customer Table
CUSCODE
NAME
ADDRESS
BALANCE

Contact Table
CUSCODE
FULLNAME
JOBTITLE

select cuscode, name, fullname, jobtitle from contact natural join customer

gives this result, automatically using CUSCODE as the Join:

SJC001        Steve’s Bargains Limited                Steve Cholerton        Head of Bargains
SJC001        Steve’s Bargains Limited                Joey Bloggs                Sales Manager

“Well, he’s kind of had it in for me ever since I accidentally ran over his dog. Actually, replace ‘accidentally’ with ‘repeatedly,’ and replace ‘dog’ with ‘son.’ - Lionel Hutz

Oracle SQL Tips

Below I have documented some useful tips that I have found when working with SQL on Oracle.


The COUNT Function
When using the COUNT function to find the amount of rows that match match a particular criteria, many people use count(*) in this format:

select count(*) from f_customer where cus_balance > 0

The problem with this method is that it is slow. You can use a column from the table in the count function like this:

select count(cus_name) from f_customer where cus_balance > 0

The problem with this method is that if the column contains a NULL value then the row will not be counted. The foolproof way of using COUNT is as follow:

select count(ROWID) from f_customer where cus_balance > 0

This is the fastest and most reliable application of the COUNT function.


DUAL
Every select statement must have a table and a column. This is where DUAL comes in handy. DUAL is a ‘fake’ Oracle table and has many uses as a ‘scratchpad’ for retrieving answers from the database. Look at these examples:

select SYSDATE from DUAL - This returns todays data, from the Oracle server.

select 34267*2 from DUAL - This returns 68534


Column Alias
You can make your sql query results easier to read by making use of the ALIAS facility. This allows you to name the result column in a more meaningful way. For example:

select cus_code as CODE, cus_name as NAME, cus_balance as BALANCE from f_customer

This names the columns using the words shown in capitals rather than the column names. The ‘as’ can also be omitted if you like:

select cus_code CODE, cus_name NAME, cus_balance BALANCE from f_customer


ORDER BY
The ORDER BY facility is extremely useful and often used in the following way:

select cus_code CODE, cus_name NAME, cus_balance BALANCE from f_customer
order by cus_balance

You can however use the column position to sort the data the same way, ie: 1 for Column 1, 2 for Column 2 Etc.

select cus_code CODE, cus_name NAME, cus_balance BALANCE from f_customer
order by 3

You can even order by the ALIAS:

select cus_code CODE, cus_name NAME, cus_balance BALANCE from f_customer
order by BALANCE


Homer: Oh Lisa, there’s no record of a hurricane ever hitting Springfield.
Lisa: Yes, but the records only go back to 1978 when the hall of records was mysteriously blown away.

Stored Procedures: Advantages

When accessing a SQL database such as Oracle there are generally two ways of implementing your data access code, either by creating the SQL statements as a string and passing them to the database from your application, or by using stored procedures. A stored procedure on Oracle is created using the language PL/SQL. A stored procedure is an executable piece of code thats sits within the database and is called by your application, it is generally created and written outside of your application using tools supplied by the database vendor.

I am convinced that stored procedures offer valuable benefits over creating the SQL within your application. For future projects I will be spending far more time with stored procedures than I do currently. Below I have listed the main reasons that I believe stored procedures (in many circumstances) are the more beneficial and efficient way of working:

PL/SQL Code is Stored within the Database
This means that the code once written and installed within the database is available to all users of that database, in addition performance is likely to be considerably better than code sent from the application.

PL/SQL Makes it Easy to Process Huge Amounts of Data
Using the ‘cursor for’ loop you can query large amounts of database rows and cycle through them iteratively.

PL/SQL Allows you to Abstract the Logic away from the Application
By putting logic in the database we can ensure that even people who access the database without your client application will still have the data processed or presented according to the system requirements. This feature often requires the use of Triggers.

PL/SQL Supports Packages
A Package is a logical group of procedures or functions that work together. As they are all loaded into memory together performance is enhanced. Standard procedures are only loaded into memory when called.

and my personal favourite:

PL/SQL Code is Portable
The PL/SQL code can be deployed on any platform on which Oracle runs, so a procedure developed on a Unix or Linux based system can be moved over to run on a Win2K3 based system with no alterations. Try doing that if the logic is written in VB.NET instead !

Other DBA’s and Developers may have additional reasons that they use PL/SQL, the above list is my opinion only of the features that I consider the most important to me when considering PL/SQL.

Live Long and Prosper - Dr Spock

Oracle Import/Export 2: IMP

For getting data back into Oracle that you have previously exported from Oracle using using the EXP command, you need to use the IMP Command. Below I have given a couple of examples of how to use the IMP command.

To IMPORT a complete database, open a Command Shell and type the following:

IMP USERID=USERNAME/PASSWORD@DATABASENAME COMMIT=Y FEEDBACK=10000 FULL=Y FILE=C:\EXPORTFILE.DMP INDEXES=Y DESTROY=Y ROWS=Y LOG=LOG=C:\IMPORTFULL.LOG

For this to work you do of course have to DROP the database before the import.

To IMPORT one database table, open a Command Shell and type the following:

IMP USERID=USERNAME/PASSWORD@DATABASENAME COMMIT=Y FEEDBACK=10000 TABLES=(TABLENAME) FILE=C:\EXPORTSINGLEFILE.DMP INDEXES=Y DESTROY=Y ROWS=Y LOG=C:\IMPORTSINGLEFILE.LOG

And in this case you need to drop the table before importing, ie: Open SQLPlus and issue the following command:

drop table TABLENAME;

“And how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. Remember when I took that home winemaking course, and I forgot how to drive?” - Homer Simpson

Oracle Import/Export 1: EXP

Oracle comes with a pair of commands that are used for getting data out and then back into a database. The commands are called IMP and EXP. There are plenty of places on the net where you can find a full description of these commands and all their options, what I will do here is give you a couple of solid examples that you can use for your own purposes and will cover your most common needs.

If you wish to EXPORT data from your database then generally you want to export either *all* the data (including the structure, necessary for recreating the database on another computer) or *one* of the tables, maybe for backup or archive purposes.

To EXPORT the complete database, open a Command Shell and type the following:

EXP USERID=USERNAME/PASSWORD@DATABASENAME CONSISTENT=Y FEEDBACK=1000 FULL=Y INDEXES=Y STATISTICS=ESTIMATE ROWS=Y GRANTS=Y CONSTRAINTS=Y LOG=C:\EXPORT.LOG BUFFER = 10485670 FILE=C:\EXPORTFILE.DMP

This creates a export dump file called EXPORTFILE.DMP and a log file called EXPORT.LOG, in their designated directory locations. Using the EXPORTFILE.DMP you can recreate the database in full on an entirely separate machine. Useful for creating offsite backup databases or updating your DSS (Decision Support System) databases.

To EXPORT one database table, open a Command Shell and type the following:

EXP USERID=USERNAME/PASSWORD@DATABASENAME CONSISTENT=Y FEEDBACK=1000 TABLES=(USER.F_TABLENAME) INDEXES=Y STATISTICS=ESTIMATE ROWS=Y GRANTS=Y CONSTRAINTS=Y LOG=C:\EXPORT.LOG BUFFER = 10485670 FILE=C:\EXPORTSINGLEFILE.DMP

This creates a export dump file called EXPORTSINGLEFILE.DMP and a log file called EXPORT.LOG, in their designated directory locations. Using the EXPORTSINGLEFILE.DMP you can archive a database table in full, or transport it to an entirely separate machine. Useful for backing up a table before performing significant changes.

“Well, Bart, your Great Uncle Horace used to have a saying: ‘Shoot ’em all, and let God sort them out.’ Unfortunately, one day put his theory into practice. It took 75 federal marshalls to bring him down. Now let’s never speak of him again.” - Marge Simpson

Dangerous Creepy Crawlies

It’s important to keep perspective when talking about Snakes, Spiders and Scorpions. After all, what have they got to gain from biting you, they can’t eat you ! If they bite or sting it’s because they are defending themselves (some are more aggressive than others) and feel that you are a threat to them, which you could be, we can kill them, even by accident, far easier than they can hurt us.

Spiders

Many spiders can give a painful bite but there are only four species that are dangerous to humans:

Sydney Funnel Web
Black Widow / Redback
Recluse Spider
Brazilian Wandering Spider

Although anyone who is allergic can die from a reaction to a bite, whether it be from a spider or anything else, the four listed above are the only ones that can kill humans due to the strength of their venom and our bodies reaction to it. As far as I can ascertain their have been no deaths from these spider bites since the easy availability of antivenom. If you are bitten:

Clean the wound
Apply a cold pack
Take a painkiller
Go to hospital - quickly !

It’s an interesting fact that the ‘big daddy’ of scary spiders, the Tarantula is actually harmless to humans and can make a great pet. A bee sting is likely to hurt more than the bite from this beast !

Snakes

Snake bites are extremely rare and hardly ever fatal. Even the most deadly snakes rarely inject enough poison to cause death. The most deadly snake, and the most dangerous snake are two entirely different concepts. Some of the snakes with the most toxic venom, ie: the most deadly, rarely bite people.

The Most Dangerous Snakes are those that kill the most people. Asian Cobra and Russell's Viper probably kill most of the people who die of snake bite annually in the world

If you are bitten:

Be Calm
Cover the wound
Bandage the limb tightly, starting nearest the heart
Immobilise the limb with a splint
Take painkillers, not aspirin
Get to hospital quickly

Scorpions

The chances of a scorpion bite being deadly is almost zero. For the worst cases antivenom is available. Some experts say there is no need to even visit a hospital following a scorpion sting. If you are bitten follow the same procedures as for a spider bite.

Summary

Despite what I have said many people have an irrational fear of Spiders, Snakes and Scorpions. Personally I have a slight fear of spiders, but scorpions fascinate me (I’ve spent time in the Sahara searching under rocks and bushes for them !) and snakes don’t bother me either way. Understanding is the way to enlightenment some guy once said (was it Budda ?) and I hope the above was of interest to someone, and maybe dispelled a few myths ☺

Mr. Burns: Quick Smithers. Bring the mind eraser device!
Smithers: You mean the revolver, sir?
Mr. Burns: Precisely.

Sending Email via Telnet

Pull up your Terminal program and run Telnet, I am using Terminal on my Mac but on Windows XP use the CMD Shell. At the prompt type the following:

>telnet mailserver 25 (mailserver is the name of your mailserver and may look something like this: mailgate.chollie.com)

After receiving the answer prompt, type:

>helo domainname (for example: helo chollie.com)

Following the response enter your email address:

>mail from: emailaddress (mail from sjc@chollie.com)

After the ‘250’ response you need to enter the recipients email address:

>rcpt to: emailaddress (rcpt to barney@rubble.com)

You’ll see a message confirming that the recipient is OK, you can now input your message.

>data (then press enter)

Type your email message and end it with a blank line followed by a single period (.) on a line by itself. The email server will conform with a message that says something similar to ‘Message Accepted for Delivery’

Type ‘quit’ to exit Telnet.

This may not be the most elegant way of sending an email but it is useful to know and can be used anywhere on any device that supports Telnet. It is also very useful to understand how the process works behind the scenes.

"Enquire not what boils in another's pot." - Thomas Fuller