sql - hugeclob - ¿Cómo obtener el tamaño en bytes de una columna CLOB en Oracle?

oracle blob

Question

¿Cómo obtengo el tamaño en bytes de una columna CLOB en Oracle?

LENGTH() y DBMS_LOB.getLength() devuelven el número de caracteres utilizados en el CLOB pero necesito saber cuántos bytes se usan (estoy tratando con conjuntos de caracteres multibyte).

Answer 1

¡La solución simple es lanzar CLOB a BLOB y luego solicitar la longitud de BLOB!

El problema es que Oracle no tiene una función que convierte CLOB a BLOB, pero simplemente podemos definir una función para hacerlo

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;

El comando SQL a usar para obtener el número de bytes es

SELECT length(clob2blob(fieldname)) as nr_bytes 

o

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

He probado esto en Oracle 10g sin usar Unicode (UTF-8). Pero creo que esta solución debe ser correcta utilizando la instancia de Oracle de Unicode (UTF-8) :-)

Quiero renderizar gracias a Nashev que ha publicado una solución para convertir clob en blob. ¿Cómo convertir CLOB a BLOB en Oracle? y para esta publicación escrita en alemán (el código está en PL / SQL) 13ter.info.blog que proporciona además una función para convertir blob en clob.

¿Alguien puede probar los 2 comandos en Unicode (UTF-8) CLOB, así que estoy seguro de que esto funciona con Unicode?

Answer 2

Después de pensar un poco encontré esta solución:

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

SUBSTR devuelve solo los primeros 4000 caracteres (tamaño máximo de cadena)

TO_CHAR convierte de CLOB a VARCHAR2

LENGTHB devuelve la longitud en Bytes utilizados por la cadena.

Answer 3

Estoy agregando mi comentario como respuesta porque resuelve el problema original para una gama más amplia de casos que la respuesta aceptada. Nota: aún debe saber la longitud máxima y la proporción aproximada de caracteres de múltiples bytes que tendrán sus datos.

Si tiene un CLOB mayor de 4000 bytes, necesita usar DBMS_LOB.SUBSTR en lugar de SUBSTR. Tenga en cuenta que los parámetros de cantidad y desplazamiento se invierten en DBMS_LOB.SUBSTR.

A continuación, es posible que deba restar una cantidad inferior a 4000, ya que este parámetro es el número de caracteres , y si tiene caracteres de múltiples bytes, entonces 4000 caracteres tendrán más de 4000 bytes de longitud, y obtendrá ORA-06502: PL/SQL: numeric or value error: character string buffer too small porque el resultado de la subcadena debe encajar en un VARCHAR2 que tiene un límite de 4000 bytes. La cantidad exacta de caracteres que puede recuperar depende de la cantidad promedio de bytes por carácter en sus datos.

Así que mi respuesta es:

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)
+...

donde agregue tantos fragmentos como sea necesario para cubrir su CLOB más largo, y ajuste el tamaño del fragmento según el promedio de bytes por carácter de sus datos.

Answer 4

NVL (length (clob_col_name), 0) funciona para mí.

Answer 5

Pruebe este para tamaños CLOB más grandes que VARCHAR2:

Tenemos que dividir el CLOB en partes de tamaños "compatibles con VARCHAR2", ejecutar lengthb a través de cada parte de los datos de CLOB y resumir todos los resultados.

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;
/

Answer 6

Solo funciona hasta 4000 bytes. ¿Qué pasa si el clob es más grande que 4000 bytes, entonces usamos esto?

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;

o

select (DBMS_LOB.getlength(your_column_name))/1024/1024 from your_table

Answer 7

Verifique el nombre del segmento LOB desde dba_lobs usando el nombre de la tabla.

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

Ahora use el nombre del segmento para encontrar los bytes utilizados en 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;