Related Topics: ColdFusion on Ulitzer

CFDJ: Article

Upgrading Oracle databases to 8i

Upgrading Oracle databases to 8i

Chris shows how easy it is to improve life by upgrading one's Oracle database

Self improvement by upgrade
I was sitting and thinking to myself the other day.

"Self," I said, "when was the last time you..."

"Wait a second," I said, interrupting myself, "this isn't going to be that 'you should be dating' lecture again, is it?"

"No," myself said, "I don't think all the lectures in the world could get you a date! This is about you working to better yourself. Why, when was the last time you did some good old fashion 'self-improvement'?"

"You're right," I thought to myself. "And it's about time I did something about it!"

"What're you going to do?" myself asked.

"Well, let's see. I'm single, live alone, wear shorts and sandals to my programming job at WebCo., and eat fine cuisine like Spaghetti-os right out of the can and obviously I wouldn't want to change any of that. Why, I know what I'll do, I'll go into work and upgrade the company's Oracle databases to 8i!"

"But it's 2 am!" myself interjected.

"Perfect," I replied. "That'll give me plenty of time to get it running again before everyone comes into work in the morning. And they'll be so pleased to find their databases upgraded to the latest and greatest version!"

"Yeah right," myself muttered.

"What was that?" I asked.

"Errrr, I said 'Good Luck,'" replied myself.

At that point I set down my Atari joystick, got in my car and headed off to change the world for the better, picking up a six-pack along the way....

Before you begin
Comfortably installed at work by 3:10 am, I began to upgrade from Oracle Server v.7.3 to Oracle 8i.

I thought it would be a good idea to set up a development server for my ColdFusion applications to test against the data when it was imported into 8i. I recommend you do the same, since, as you'll see in the Date section below, there were some connectivity issues as well as unexpected code changes to make.

The minimum disk space required for the Oracle 8i database server is approximately 600MB. (The maximum amount of empty Corona beer bottles you can balance on an open CD-ROM drive is 4, but I digress....) Database space also eats up your resources, since each individual database created requires over 20MB of free disk space. In addition, I recommend you to have at least 128MB of RAM on the server and at least 96MB of swap space on the hard drive when executing database creation scripts or the Oracle Installer.

Since the installation is written in Java, it is slower and a memory hog. You'll want to free up as many resources as you can when running the install program.

After a few tries, I found that a good way to migrate the databases from Oracle 7.3 to 8i was to:

  • export the 7.3 database by running the export command in Oracle (this will create a .dmp file containing the contents of the database)
  • create a new database in 8i with the same database identifier (oracle_sid) that is in 7.3
  • create the same user accounts and tablespaces in the new database
  • import the .dmp file you created above into the new 8i database you just created
However, this is only an outline and may require some tweaking during the import, depending on the size of your database file.

Connecting to 8i databases with ColdFusion
With the Oracle server installed, and the databases migrated, I still had to connect the new databases to the ColdFusion Server using the CF Administrator.

As well as ColdFusion's Native Drivers, there are several other ODBC drivers that could be used but the Native Drivers are included with the ColdFusion Enterprise Server work well in most situations.

Merant (formerly Intersolv) sells a nice 8i database driver, but licensing is priced by the number of processors on your server so you may want to try the Native Drivers first.

An additional thing that the Merant data drivers do, besides provide blazingly fast data access, is handle the passage of data fields larger than 2000 characters. The Oracle and Microsoft ODBC drivers don't support passing more than 2000 character-valued variables from the ColdFusion Server to the Oracle Server.

This becomes a problem if you're using LONG, BLOB, or CLOB datatypes, each of which can hold 2GB and up of ascii or binary data. You can get around this 2000-character limitation with Native Drivers if you write Stored Procedures in Oracle to handle the insert or updates of LONG datatypes into the databases. This procedure is fast and relatively simple, and there are good examples of how to do this online at Allaire's ColdFusion Development Forum.

If you opt for the Merant drivers, configure them exactly as you would with the Windows ODBC Administrator Control Panel, using the Merant DataDirect Administrator. They are painless to configure and you can continue to code for large data fields without writing Stored Procedures.

If you decide on the Native Drivers, configure them for 8i just as you'd configure the Native Drivers for 7.3. You must have the 8i Client Libraries installed on your ColdFusion Server so that the 8i Native Drivers can work properly.

An unexpected glitch
I found an unexpected glitch when I set up the 8i Native Drivers: the Native Drivers will not function properly with 8i if your Oracle 7.3 Client Libraries are already installed on the ColdFusion Server and you then install the Oracle 8i Client Libraries. (This problem took a lot of digging around in Allaire's documentation to solve.)

...the Native Drivers will not function properly with 8i if your Oracle 7.3 Client Libraries are already installed on the ColdFusion Server and you then install the Oracle 8i Client Libraries...

When you configure the database connection in the ColdFusion Administrator and attempt to verify, you will receive the error message that ColdFusion Server cannot connect to the database. To fix this, you will have to uninstall your Oracle 7.3 Client from the ColdFusion Server — be sure to remove the tnsnames.ora and all other files and registry settings. When it is completely removed, install the 8i Client Libraries and the Native Drivers will work like a charm.

How 8i handles dates
8i handles dates differently than 7.3 and I was surprised by how dates were 1. inserted, 2. copied from one table to another, and 3. selected from the database.

1. Inserting Dates
With 7.3, as long as you are using the ColdFusion CreateODBCDateTime function, you can insert dates without calling SQL date functions. With 8i, when inserting or updating a date field, you'll need to call the TO_DATE function or the update will not understand it properly.

View the code for Example 1.1

View the code for Example 1.2

2. Table-to-Table Copies of Dates
Formerly, copying Date field data from one table into another required calling the SQL TO_DATE function. With 8i, you no longer need to re-cast the date by calling the TO_DATE function as it understands that it's already in Date format. If you do call the TO_DATE, it may cause an error in the execution of the CFQUERY containing the TO_DATE call.

View the code for Example 2.1

3. Selecting Dates
When selecting a date field out of a table in 8i, the time portion of the date field will not display properly unless a mask is put on it when selecting it. There was no Time casting needed in Oracle 7.3 as the time would properly display when selected.

View the code for Example 3.1

Now, although I know this makes no sense, if you display this date in ColdFusion, the date will display properly but the time will display as 12:00AM no matter what time is actually in the database. Even using the ColdFusion #TimeFormat()# function will not display the time correctly. To get the right date, a mask must be placed on the date/time field when it's selected in your CFQUERY.

View the code for Example 3.2

This will give you the correct time portion of the Date/Time field in 8i.

The beer burglar
Well, I did it. It was now 8:04 am, and people were starting to roll into work. It took me a while, true, but that was probably because I wasted a lot of time looking for my beer. Actually, and this is key: someone had drunk all of my beer. I swear it wasn't me, I couldn't have possibly drunk all that beer. Perhaps now that this upgrade is done, I thought, I will open an all-out investigation of this mysterious "Beer-Burglar" who drank my beer. But first some well-earned sleep...

As I proudly walked to my car, I suddenly realized that that was just the development server, and that I now had to do the whole thing all over again with the production server.

"Dagnabbit!" said I to myself.

"Hey, it wasn't my idea," myself replied.

A number of the drivers mentioned are available online, some free, some not.

Merant's Data Direct Oracle 8i Drivers

Microsoft's Data Access Components for ODBC (Contains Oracle Drivers)

Oracle's 8i Client Libraries are part of the Client installation of Oracle 8i

More Stories By Chris Cusack

Chris Cusack is a New Media Engineer at MapInfo Corp. where he is responsible for developing content management and new media applications.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.