varchar2 <-> long advantages/disadvantages

[hs92]

I am using Oracle 7.3. In my current system, notes for an account are entered in a separate table over as many rows as required using a varchar2(60). Sometimes a user needs to find specific words within these rows and to that end we have a query built in that searches for the users word by using - like ‘%word%’ where word is the word they are searching for. We are discussing changing the varchar2(60) to a long and only using one row per entry. What are the advantages or disadvantages of doing this? Will I still be able to search easily for specific words?

Thanks for any advice

 

[karluk]

My understanding is that the long datatype is poorly supported in Oracle 7. You can't do functions like substr, or compare long data to char or varchar. For example, the following typical sql statement

select * from your_table
where long_col like '%xyz%'

would generate an ORA-00932: inconsistent datatypes.

I'm also fairly sure that long columns can't be replicated using Oracle replication. This may or may not be an issue for you.

I think that the package, UTL_RAW, supplies some of the functionality lost when going from varchar2 to long. However in my opinion the problems outweigh the benefits. If you have a working system using varchar2 data, I would suggest leaving well enough alone.

 

[hs92]

If I was using Oracle 8 would you still suggest staying with varchar2? We will probably be migritating to Oracle 8 within the next year.

 

[carp]

Oracle8 works a little better (you would probably be using CLOBs instead of LONGs). On the other hand, have you considered just using a higher value for your VARCHAR2? In O8, you can go out to VARCHAR2(2000). This would cut down on the number of lines and still let you use simpler commands.

 

[Surb]

I'm struggling with SQL statements because of a database that uses long datatypes. With long datatypes in a select statement you can't union or group by.