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

ENKript! Uploaded

I’ve just uploaded my Text Encryption / Decryption tool, ENKript!. It can be downloaded here.



ENKript! is a simple tool that allows you to input text and then encrypt it using industry strength (AES) encryption. You select you own key, a 4 to 16 character code, which is used to both encrypt and decrypt the text. The encryption key should be agreed in advance by you and the intended recipient. Once the text is encrypted you can select the email option and the encrypted text will be sent to your recipient.

Note: A 16 Character Key is recommended if you are serious about keeping the contents of your document private.

When the recipient receives the encrypted text then they can decode using the key you both agreed upon earlier. Either ENKript! can be used to decrypt the text or alternatively visiting my website - www.genesyssolutions.co.uk and selecting the ‘Online Utilities / ENKript!’ menu option will give you a page that can be used to decrypt the received text.

As email can generally be thought of as the digital equivalent of sending a postcard, ie: the contents are open and easily visible, a tool like ENKript! makes sense. Ideally you would agree in advance a different key for each of your associates (or group of associates) that you wish to communicate with in confidence.

Suggested use would be situations like mergers and acquisitions within the corporate environment, board level communication, the recipe for grandmas apple pie, in fact anything that you do not particularly wish to be visible to the outside world. In a corporate environment particularly, nothing is ever hidden from the guys in the IT Department - unless you ENKript! it ☺

ENKript also works as a command line program and can take input from a parameter file, this enables automated / batched jobs to be setup - maybe to take a text file from a secure location, encrypt it and then have it distributed as necessary. Decryption can also be automated from the command line.

ENKript! is written in .NET 2.0 and runs on the Windows platform only.

"The great French Marshall Lyautey once asked his gardener to plant a tree. The gardener objected that the tree was slow growing and would not reach maturity for 100 years. The Marshall replied, 'In that case, there is no time to lose; plant it this afternoon!'" - John F Kennedy

Roll Your Own :-)

I’ve just finished working on a System Migration project that was one of the more complex I’ve done. The original application was running on Oracle 9i on Unix. So far so good. However the documentation for the (over 1000) tables was poor to say the least and in addition the system had been heavily modified in the 20 or so years it had been in existence. The application was never initially designed for Oracle and used rule based indexing.

On to the point of the article ☺ . It can be useful in circumstances such as these to explore the data from code rather than relying purely on SQL query tools. I created a couple of tools to help with this job, one being ODBCDataTool which I wrote in an old, but effective language called Omnis7. Despite it’s age Omnis is still a fast and flexible cross platform development tool and works well even under the latest Operating Systems such as Vista.



What I required from this tool was a nice simple interface to the tables and columns within the database and then to create schemas based on these tables within Omnis. I could then switch to code and select, combine, loop and manipulate data to my hearts content. It’s been a few months since I have used Omnis and I enjoyed using it again. The application took an evening to write and worked perfectly. As I designed it to work against any database which has an ODBC driver, it will probably surface again in the future as the need arises.

"When life seems chaotic, you don't need people giving you easy answers or cheap promises. There might not be any answers to your problems. What you need is a safe place where you can bounce with people who have taken some bad hops of their own." - Unknown