?
Solved

Need a replacement data type in Oracle

Posted on 2016-11-23
6
Medium Priority
?
158 Views
Last Modified: 2016-11-23
I have a couple of tables in a database that are using the LONG data type. It's a horrible data type and we need to get rid of it. The question is what do I replace it with? I've narrowed it down to LOB, BLOB, or CLOB but the differences between those seem subtle and I can't quite figure out which is the best choice and why. Some of the LONG fields are storing bitmap image data. Others are storing HTML markup text. I'm OK with using a different data type for each of those. I just need to get rid of the LONG.
0
Comment
Question by:Russ Suter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 2000 total points
ID: 41899757
LONG data types are actually no longer supported.  I believe that support for them was dropped in Oracle 9.

For character data (like HTML) use CLOB.
For binary data (like images) use BLOB.

LOB is just a general term for both of them.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 41899758
For bitmap image data, you have to use a: BLOB (short for Binary Large OBjects).  For html data, you can use a: CLOB (short for Character Large OBjects).  I think a LOB is just a newer replacement for LONG, but still ambiguous, since it can contain either binary or character data.
0
 
LVL 20

Author Comment

by:Russ Suter
ID: 41899760
That's basically what I thought. Is there any reason why I can't use a BLOB to store the HTML data also?
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 35

Expert Comment

by:johnsone
ID: 41899763
There is no reason you cannot store HTML in a BLOB.  CLOB has more features (like you can do substrings and things like that).
0
 
LVL 20

Author Comment

by:Russ Suter
ID: 41899767
That's good to know. Obviously we're not using substrings and things like that with the LONG data type so it sounds like we won't be missing anything by just using BLOB.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41899770
You probably wouldn't be missing anything, but it allows you do do more things without having to convert it later.
1

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One event, two days, a great line-up of speakers, and 48% female presence. Still have no idea what I’m talking about?
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question