How to get size in bytes of a CLOB column in Oracle

Question

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

User · Answer

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   lt  lt TABLE NAME gt  gt      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     lt  lt  OWNER  gt  gt    order by s segment name  s partition name

User · Answer

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

User · Answer

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  lt CLOB-Column gt  3000 1     NVL LENGTHB TO CHAR DBM  S LOB SUBSTR  lt CLOB-Column gt  3000 3001    0   NVL LENGTHB TO CHAR DBM  S LOB SUBSTR  lt CLOB-Column gt  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

User · Answer

After some thinking i came up with this solution    LENGTHB TO CHAR SUBSTR  lt CLOB-Column gt  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

User · Answer

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

User · Answer

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

User · Answer

NVL length clob col name  0  works for me

[sql] How to get size in bytes of a CLOB column in Oracle?

Examples related to sql

Examples related to oracle

Examples related to clob