Oracle / PLSQL: Determine the length of a LONG field

Question: I have a table in Oracle that contains a field with the data type of LONG. I want to find out how many characters are stored in this LONG field for a particular record in the table.

How can I count the number of characters in a LONG data type field?

Answer: It doesn't appear that you can find the length of a LONG field in SQL. However, you can use PLSQL code to determine the length of a LONG field.

Here is an example of a function that returns the length of the LONG field called SEARCH_CONDITION. This function accepts the primary key values (owner and constraint_name fields) and returns the length of the SEARCH_CONDITION field for the selected record.

CREATE or REPLACE function Find_Length
   ( av_owner varchar2, av_cname varchar2)
   RETURN number

IS
   long_var LONG;

BEGIN

   SELECT SEARCH_CONDITION INTO long_var
   FROM ALL_CONSTRAINTS
   WHERE owner = av_owner
   AND constraint_name = av_cname;

   return length(long_var);

END;

You can modify this function to reference your table and field names. Give it a try!

Once the above function has been created, you can reference this function in your SQL statement. For example,

SELECT owner, constraint_name, Find_Length(owner, constraint_name)
FROM ALL_CONSTRAINTS;

This SQL statement would retrieve the owner and constraint_name fields from the ALL_CONSTRAINTS table, as well as the length of the SEARCH_CONDITION field.

You can modify the Find_Length function to reference your own table and field names. You will also need to modify the function parameters to pass in your own primary key values.