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