RE: Dratted LONG column

From: Singer, Phillip (P.W.) <psinger1_at_ford.com>
Date: Thu, 24 Jun 2004 13:13:28 -0400
Message-ID: <A45063A7D336504580F0161CEB7FEBE201AE7918@na1fcm60.dearborn.ford.com>

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Thomas Day
> Sent: Thursday, June 24, 2004 10:30 AM
> To: oracle-l_at_freelists.org
> Subject: Dratted LONG column

>=20

> I have to do a select using the long column in the where clause.

>=20

> Of course that won't work.

>=20

My sympathy. I have to deal with LONG RAW (!?!) columns.

> On a related note - if I'm going to build a new table (and I=20
> think that I
> am), the existing table has a poor design. There are 10=20
> attributes that
> are IDs. One and only one of them must be non-null. I.e.,=20
> one will have
> data and the other nine must be null. Rather than have 10=20
> fields in my new
> table (with only one of them having data at any one time) I'd=20
> like to have
> an ID number and an ID_TYPE that records the column name in=20
> the original
> table where the value was non-null. Any nifty ideas on how to do this
> (other than using a series of selects from the original table=20
> that tests
> each field for non-nullity)?

>=20

Knowing absolutely nothing about the specifics of your application, my=20 hunch is that you really need 10 new tables.=20

In other words, my guess is that you have 10 entities, each of which has one or more attributes needing a LONG column. The original designer started to move each column to its own table, then decided that there wasn't room on the ER Diagram for all those tables, and merged them into one.

Having had to do damage control for a number of tables where the designer looked and said: I have 3 (or more) tables which have almost the same column names, and the same types of data, and are used for similar purposes, so let's just merge them into one, because I can = always
keep track of what I should be doing .....

You don't want to go there.

My advice: =20

Create 10 tables, one for each key
Do 10 export from the old table, using a query parameter to only = take rows if the key is null.
Import each into it's corresponding table.
Alter each table, dropping the unused keys, and adding a CLOB column
Convert the LONG to CLOB
Drop the LONG Column
Congratulate yourself on entering the 21st century.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jun 24 2004 - 12:12:45 CDT