OCA Study Guide eBook

I’ve finally finished the eBook version of my Oracle Certified Associate Exam Cram / Study Guide document. It is 66 pages long and available to download for free from here. Renowned Oracle guru, consultant and author Jonathan Lewis gave up his time to help with corrections and the technical editing.

Creating this document took up far more time than I envisaged, and was far more difficult than I expected ! I hope it is of use to someone :-)

"Trouble is only opportunity in work clothes." - Henry J Kaiser

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

Oracle Now On Intel Mac OSX

The Oracle Instant Client has now arrived for MacOSX Intel. Although I couldn’t find any mention of it on the Oracle site, it does appear to be OSX Leopard only. So Tiger users are out of luck unfortunately. You can download the client, free of charge, here, and below you can see a successful SQLPlus connection from my OSX Terminal :-)



There is a couple of hoops to jump through to get it running, but it’s really quite straightforward. I will blog the steps required if anybody requests. Interestingly it support database from 8i onwards, which is good news for those who have legacy databases to deal with.

What this long awaited download gives us is native, direct access from our Mac applications on our Intel boxes, no need for ODBC drivers any more :-) I’m looking forward to giving it a bit of a workout, unfortunately I have some Windows C# programming and a couple of other things to finish first :-(

"Do definite good; first of all to yourself, then to definite persons." - John Lancaster Spalding

Clock Utility Uploaded, Bigtime

Bigtime is a (very) simple utility that displays the time in the format of a 24 hour digital clock.



The screenshot above is from the MacOSX version and the screenshot below is from the Windows version running on 2003 Server.



Bigtime runs on top of all other windows on your screen. Bigtime remembers it's position on the screen when the application is closed by writing to the Preferences.txt file. This file is stored in the same directory as the application itself.

Bigtime for MacOSX, Windows and Linux can be downloaded here.

"It's not foresight or hindsight we need. We need sight, plain and simple. We need to see what is right in front of us." - Anonymous

IP Utility Uploaded, pcInfo

I have lots of Virtual Machines. With my new MacPro I often run several simultaneously, both clients such as XP and Vista and also servers, mainly Windows 2003 running various flavours of Oracle or SQLServer. As they often need to talk to one another I decided it would be useful to develop an application that could run on startup and display the network information such as IP Address, Subnet Mask and MAC Address in a small window on the desktop. No more clicking to access the IP details or opening a command window to do an ipconfig.

pcInfo is the result of scratching that itch yesterday afternoon. Below you can see screenshots of pcInfo running on MacOSX and also Windows Vista.





pcInfo can be downloaded here. The zip file contain four versions of the file to run on the following operating systems:

Mac OSX Intel
Mac OSX Universal Binary
Windows 95 through XP and Vista
Linux

Another situation is which this program would prove useful is if you are running banks of servers, maybe accessing them using RDC or VNC or maybe through a KVM switch ?

pcInfo remembers it's position on the screen when the application is closed by writing to the Preferences.txt file. This file is stored in the same directory as the application itself.

pcInfo is copyright Steve Cholerton 2008. You may freely download and use this application without charge. However if you use it and you find it useful, please drop me a line and let me know.

If you think of any ideas for improvements or additional features then I would also appreciate hearing from you.

"A doctor saves lives -- It's up to people to create lives that are worth saving." - Philip Gold

Ruby on Rails Introduction

Dan Benjamin has written a superb article on getting started with Ruby on Rails.

You can see it here.

"The meeting of two personalities is like the contact of two chemical substances: if there is any reaction, both are transformed." - Carl Jung

Online Encryption Utility

The ENKript! Online Utility can work standalone or in conjunction with the ENKript! client software which is Windows only and is available for download from www.genesyssolutions.co.uk.

To access ENKript! point your browser at http://www.genesyssolutions.co.uk. Select the ‘Online Utilities / ENKript!’ menu. You will see the screen shown below:



ENKript! allows you to encrypt and decrypt text. Without your secret key the text cannot be decrypted. The idea is that you agree in advance a ‘secret key’ between yourself and whoever else needs to view the decrypted text. Once this secret key is in place you can use ENKript! to encrypt your text by pasting the text into the ‘Plain Text’ area, inputting your secret key and selecting the ‘Closed Padlock’ icon. The encrypted text appears within the ‘Encrypted Text’ area. From here it can be selected and copied into an email or document.

One the recipient gets your email or document they can access ENKript!, paste your encrypted message into the ‘Encrypted Text’ area, input the secret key and select the ‘Open Padlock’ icon. The decrypted message will be shown in the ‘Plain Text’ area.

The secret key can be between 4 and 16 characters, 16 characters is recommended for maximum security as the strength of the encryption is directly related to the length of the secret key.

"There's only one corner of the universe you can be certain of improving, and that's your own self." - Aldous Huxley

Oracle: Temporary Tables

Temporary Tables can be extremely useful when manipulating large amounts of data within Oracle, for storing totals or aggregate values. To setup a temporary table:

create global temporary table temp_table
(description VARCHAR2(30), count NUMBER)
on commit delete rows

or

create global temporary table temp_table
(description VARCHAR2(30), count NUMBER)
on commit preserve rows

Temporary tables are visible only to the session that inserted data into it. The data will be deleted at the end of the transaction (on commit delete rows) or when the session is terminated (on commit preserve rows). While they exist and are populated they can be used in the same way as any standard Oracle table.

Beware so long as you live, of judging people by appearances." - La Fontaine

Oracle: Locking 101

Locking data is essential to prevent more than one user altering data in the database at the same time. Oracle can lock one row, multiple rows, or the whole table. Oracle attempts to lock at the lowest level possible to ensure minimal impact on the database. It is possible using LOCK TABLE to lock manually lock a complete table. I have personally never needed to use this statement and it is certainly not recommended for most applications with more than one concurrent user.

Querying the database will *never* produce a lock using Oracle, and unlike some databases locking will not stop Oracle producing consistent queries, as the query works from the UNDO tablespace. This is the technology that allows a long running query started at 1200 to finish at 1400 and show the results of that query with all the data as it was when the query was started at 1200, regardless of changes to the data during the time the query was running.

The first user to request a lock, gets the lock and other users are queued on a FIFO basis. If the design of your application requires that control is returned to the user immediately in the event of the requested data being unavailable for locking then the NOWAIT parameter can be used.

It is important to realise that all locks are released when the transaction is terminated via either a COMMIT or ROLLBACK, whether issued explicitly or implicitly.

The table below shows the different kinds of table lock modes available:

Row Share
Allows connect access to the whole table, stops users locking the entire table for EXCLUSIVE.

Row Exclusive
As Row Share, but stops other users locking in SHARE mode
(used by DML such as INSERT, UPDATE and DELETE)

Share
Permits concurrent queries but prevents any updates to the table.
(used by CREATE INDEX)

Share Row Exclusive
To query the whole table, and allow others to do so, but prevent them locking the table in SHARE mode or doing any updates.

Exclusive
Permits queries, prevents any DML.
(used by DROP TABLE)

To monitor the current locks in Oracle you can use the Enterprise Manager or alternatively query the following views:

V$SESSION
V$TRANSACTION
V$LOCK
V$LOCKED_OBJECT

You can obtain explicit locks on individual rows using the select ... for update statement:

select * from ords_data where ords_ref = ‘SJC001’ for update;

If somebody else attempts to get a lock on that row their session will either wait or if using the NOWAIT parameter control will be returned to them immediately with an Oracle Error: ORA-00054. You can trap for this within your application and bring up a message explaining that the record is locked and to try again later.

"A book is a version of the world. If you do not like it, ignore it; or offer your own version in return." - Salman Rushdie

K2 Back Pack

While at the Hunters today waiting for the service to be finished on my car, I sat browsing through one of their magazines, in the ‘gear and accessories’ section I saw a Back Pack that seems to be exactly what I’ve been after for a while.

I sometimes carry my laptop with me on my motorcycle and have often thought that there must be a higher quality, stronger back pack available that will also function as a back protector in the event of an accident. In addition I would like a stronger, more secure rucksack for use when hiking and walking in the country.

The K2 from Land Rover seems to be just the ticket. Although the dealer did not have any in stock they are available online from here. I’ve dropped them an email to check stock levels and if in stock I hope to order one tomorrow - if I could get it for the weekend that would be great :-)




"We know what a person thinks not when he tells us what he thinks, but by his actions." - Issac Bashevis Singer

My Workstation

Since my last post about my new MacPro I’ve had a few emails asking for a picture of my workstation, so for all your lovers of sexy hardware, here you go ☺



"Propriety was a rigid master, but one that must be obeyed if one wanted to keep a sterling reputation." - Lawana Blackwell