Q
Manage Learn to apply best practices and optimize your operations.

How to get the lengths of values in "long" datatype

How do you get the lengths of the values stored in a "long" column in Oracle? I tried LENGTH(), but I got the error "ORA-00932: inconsistent datatypes: expected NUMBER got LONG". DBMS_LOB.getlenth() also doesn't work.

The LONG datatype will not work with the LENGTH function. The LENGTH function expects one of the character datatypes...

or a number datatype and the LONG datatype is inconsistent with these datatypes. This is one of the problems with the LONG datatype. And since this data is not a LOB datatype, the DBMS_LOB package cannot be used either.

This very low level of functionality and interaction is a reason why the LONG and LONG RAW datatypes are nearing the end of their road. You should convert your application to LOB datatypes instead.

So if you need the length of the character data in your LONG datatype, then you'll have to jump through a series of hoops. One option is to write a program to read the LONG value and then use the application platform's functions to determine the length of the data you just read.

The other option is to copy the data to another table and convert it into a CLOB at the same time. A SQL statement similar to the follow illustrates this example:

INSERT INTO new_table (ColX, clob_col)
SELECT colX,TO_LOB(long_col) FROM old_table;

Once the data has been converted to a CLOB, you can use the DBMS_LOB package to determine the length of the data.

This was last published in December 2004

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close