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.

Dig Deeper on Oracle database design and architecture