39

How do I get the size in bytes of a CLOB column in Oracle?

LENGTH() and DBMS_LOB.getLength() both return number of characters used in the CLOB but I need to know how many bytes are used (I'm dealing with multibyte charactersets).

Improve this question
3
  • Why are you concerned with the size in bytes of the CLOB? Commented Oct 21, 2013 at 19:19
  • 4
    Not the OP, but in my case I was retrieving CLOBS across a DB link and had to cut them into 4000-byte chunks, and I wanted to know how many chunks I needed for my data. Commented Oct 24, 2013 at 12:14
  • this is insane, I want to squeeze Chinese clob into varchar2 and can't do it because I can't determine the size Commented Nov 3, 2017 at 15:25

7 Answers 7

Reset to default
22

After some thinking i came up with this solution:

 LENGTHB(TO_CHAR(SUBSTR(<CLOB-Column>,1,4000)))

SUBSTR returns only the first 4000 characters (max string size)

TO_CHAR converts from CLOB to VARCHAR2

LENGTHB returns the length in Bytes used by the string.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

You could say LENGTHB(TO_CHAR(DBMS_LOB.SUBSTR(<CLOB-Column>,3000,1)))+NVL(LENGTHB(TO_CHAR(DBMS_LOB.SUBSTR(<CLOB-Column>,3000,3001))),0) - this works up to 6000 bytes but could be extended indefinitely. If you're dealing with multi-byte charsets you need to substring to less than 4000 chars, or you'll get ORA-06501 character string buffer too small. Also note that DBMS_LOB.SUBSTR inverses order of the amount and offset parameters.
See also stackoverflow.com/questions/10331912/…
20

I'm adding my comment as an answer because it solves the original problem for a wider range of cases than the accepted answer. Note: you must still know the maximum length and the approximate proportion of multi-byte characters that your data will have.

If you have a CLOB greater than 4000 bytes, you need to use DBMS_LOB.SUBSTR rather than SUBSTR. Note that the amount and offset parameters are reversed in DBMS_LOB.SUBSTR.

Next, you may need to substring an amount less than 4000, because this parameter is the number of characters, and if you have multi-byte characters then 4000 characters will be more than 4000 bytes long, and you'll get ORA-06502: PL/SQL: numeric or value error: character string buffer too small because the substring result needs to fit in a VARCHAR2 which has a 4000 byte limit. Exactly how many characters you can retrieve depends on the average number of bytes per character in your data.

So my answer is:

LENGTHB(TO_CHAR(DBMS_LOB.SUBSTR(<CLOB-Column>,3000,1))) +NVL(LENGTHB(TO_CHAR(DBM‌​S_LOB.SUBSTR(<CLOB-Column>,3000,3001))),0) +NVL(LENGTHB(TO_CHAR(DBM‌​S_LOB.SUBSTR(<CLOB-Column>,6000,6001))),0) +...

where you add as many chunks as you need to cover your longest CLOB, and adjust the chunk size according to average bytes-per-character of your data.

Improve this answer

3 Comments

See answer by TobiK for the same technique expressed as a loop so that you don't need to check whether you've added enough chunks to cover your largest CLOB.
So this solution relies on hope and guesswork to avoid ORA-06502 errors? Surely there is a robust way to split a CLOB in to VARCHAR2-sized chunks!
@PhilHibbs It's enough for my purpose because I only have to deal with French and can make assumptions about the proportion of multibyte characters. But I agree it's unsatisfactory and I'd be delighted to see a better answer.
8

Try this one for CLOB sizes bigger than VARCHAR2:

We have to split the CLOB in parts of "VARCHAR2 compatible" sizes, run lengthb through every part of the CLOB data, and summarize all results.

declare my_sum int; begin for x in ( select COLUMN, ceil(DBMS_LOB.getlength(COLUMN) / 2000) steps from TABLE ) loop my_sum := 0; for y in 1 .. x.steps loop my_sum := my_sum + lengthb(dbms_lob.substr( x.COLUMN, 2000, (y-1)*2000+1 )); -- some additional output dbms_output.put_line('step:' || y ); dbms_output.put_line('char length:' || DBMS_LOB.getlength(dbms_lob.substr( x.COLUMN, 2000 , (y-1)*2000+1 ))); dbms_output.put_line('byte length:' || lengthb(dbms_lob.substr( x.COLUMN, 2000, (y-1)*2000+1 ))); continue; end loop; dbms_output.put_line('char summary:' || DBMS_LOB.getlength(x.COLUMN)); dbms_output.put_line('byte summary:' || my_sum); continue; end loop; end; /
Improve this answer

3 Comments

This is better than my answer if you need the request to be executable in future with no a priori knowledge of the maximum possible CLOB size. I'd write it as a function in that case, though.
I get this error when running the above code: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 14 ORA-06512: at line 14 06502. 00000 - "PL/SQL: numeric or value error%s"
Disregard my previous comment. It was because I had some null values in the CLOB column. It works ok if I just add "where column is not null" in the query.
7

The simple solution is to cast CLOB to BLOB and then request length of BLOB !

The problem is that Oracle doesn't have a function that cast CLOB to BLOB, but we can simply define a function to do that

create or replace FUNCTION clob2blob (p_in clob) RETURN blob IS v_blob blob; v_desc_offset PLS_INTEGER := 1; v_src_offset PLS_INTEGER := 1; v_lang PLS_INTEGER := 0; v_warning PLS_INTEGER := 0; BEGIN dbms_lob.createtemporary(v_blob,TRUE); dbms_lob.converttoblob ( v_blob , p_in , dbms_lob.getlength(p_in) , v_desc_offset , v_src_offset , dbms_lob.default_csid , v_lang , v_warning ); RETURN v_blob; END;

The SQL command to use to obtain number of bytes is

SELECT length(clob2blob(fieldname)) as nr_bytes

or

SELECT dbms_lob.getlength(clob2blob(fieldname)) as nr_bytes

I have tested this on Oracle 10g without using Unicode(UTF-8). But I think that this solution must be correct using Unicode(UTF-8) Oracle instance :-)

I want render thanks to Nashev that has posted a solution to convert clob to blob How convert CLOB to BLOB in Oracle? and to this post written in german (the code is in PL/SQL) 13ter.info.blog that give additionally a function to convert blob to clob !

Can somebody test the 2 commands in Unicode(UTF-8) CLOB so I'm sure that this works with Unicode ?

Improve this answer

Comments

5

NVL(length(clob_col_name),0) works for me.

Improve this answer

1 Comment

No, length returns the length in characters, not bytes.
3

Check the LOB segment name from dba_lobs using the table name.

select TABLE_NAME,OWNER,COLUMN_NAME,SEGMENT_NAME from dba_lobs where TABLE_NAME='<<TABLE NAME>>';

Now use the segment name to find the bytes used in dba_segments.

select s.segment_name, s.partition_name, bytes/1048576 "Size (MB)" from dba_segments s, dba_lobs l where s.segment_name = l.segment_name and s.owner = '<< OWNER >> ' order by s.segment_name, s.partition_name;
Improve this answer

Comments

0

It only works till 4000 byte, What if the clob is bigger than 4000 bytes then we use this

declare v_clob_size clob; begin v_clob_size:= (DBMS_LOB.getlength(v_clob)) / 1024 / 1024; DBMS_OUTPUT.put_line('CLOB Size ' || v_clob_size); end;

or 

select (DBMS_LOB.getlength(your_column_name))/1024/1024 from your_table
Improve this answer

1 Comment

But doesn't DMBS_LOG.getlength(...) return the number of characters rather than bytes?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.