Friday, September 10, 2010

How to Find the Maximum Length Value in a CLOB Column in Oracle

If you want to convert CLOB to VARCHAR2 for speed or to avoid LOB related complication in queries, you might need to know how long the VARCHAR2 will need to be.

To determine the length of the CLOB column in characters based on getting the actual value of the CLOB (which you'd need to do if you converted to VARCHAR2), use this query:

SELECT * FROM (
  SELECT LENGTH(DBMS_LOB.SUBSTR(your_column_name)) 
  AS "LENGTH" 
  FROM your_table_name 
  WHERE LENGTH(DBMS_LOB.SUBSTR(your_column_name)) IS NOT NULL 
  ORDER BY LENGTH(DBMS_LOB.SUBSTR(your_column_name)) DESC)
WHERE ROWNUM = 1

If you're using an older version of Oracle pre-8i, you'll need to use another method than rownum = 1 to get the value with the largest size, in which case you should read:

If you get:

 [Error Code: 6502, SQL State: 65000]
 ORA-06502: PL/SQL: numeric or value error: 
 character string buffer too small
 ORA-06512: at line 1
I think it means the CLOB is larger than can be held than in the variable Oracle uses to hold the result from DBMS_LOB.SUBSTR(your_column_name). I think that max length is 4000, by default. I skimmed results against our data and length got close to but never over 3999 without an error. I believe you might be able to add an additional argument specifying length.

But if you need CLOB length regardless of whether you could convert, use DBMS_LOB.GETLENGTH(your_column_name) to get the length. I saw some say the following might work to get large substr out: DBMS_LOB.SUBSTR(your_column_name, 4000, 1), DBMS_LOB.SUBSTR(your_column_name, 4000, 4001), DBMS_LOB.SUBSTR(your_column_name, 4000, 8001), ...

SELECT * FROM (
  SELECT DBMS_LOB.GETLENGTH(my_column_name) AS "LENGTH" 
  FROM my_table_name 
  WHERE DBMS_LOB.GETLENGTH(my_column_name) IS NOT NULL 
  ORDER BY DBMS_LOB.GETLENGTH(my_column_name) DESC) 
WHERE ROWNUM = 1

See:

No comments: