sql - insertar - Cómo consultar una columna CLOB en Oracle

manejo de clob en oracle

Question

Estoy intentando ejecutar una consulta que tiene algunas columnas que son un tipo de datos CLOB. Si ejecuto la consulta como siempre, todos esos campos solo tienen (CLOB) como valor.

Intenté usar DBMS_LOB.substr(column ) y recibí el error

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

¿Cómo puedo consultar la columna CLOB?

Answer 1

Al obtener la subcadena de una columna CLOB y utilizar una herramienta de consulta que tenga restricciones de tamaño / búfer, a veces deberá establecer el BÚFER a un tamaño mayor. Por ejemplo, al usar SQL Plus, utilice el SET BUFFER 10000 para establecerlo en 10000, ya que el valor predeterminado es 4000.

Al ejecutar el comando DBMS_LOB.substr también puede especificar la cantidad de caracteres que desea devolver y el desplazamiento desde el cual. Entonces, usar DBMS_LOB.substr(column, 3000) podría restringirlo a una cantidad lo suficientemente pequeña para el buffer.

Consulte la documentación de Oracle para obtener más información sobre el comando substr.

DBMS_LOB.SUBSTR ( lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;

Answer 2

Esto funciona

select DBMS_LOB.substr(myColumn, 3000) from myTable

Answer 3

Me encontré con otra condición con HugeClob en mi base de datos Oracle. dbms_lob.substr solo permitía un valor de 4000 en la función, por ejemplo:

dbms_lob.substr(column,4000,1)

entonces, para mi HughClob, que era más grande, tuve que usar dos llamadas en select :

select dbms_lob.substr(column,4000,1) part1, 
       dbms_lob.substr(column,4000,4001) part2 from .....

Estaba llamando desde una aplicación Java, así que simplemente concatené part1 y part2 y lo envié como un correo electrónico.

Answer 4

Otra opción es crear una función y llamar a esa función cada vez que necesite seleccionar la columna de clob.

create or replace function clob_to_char_func
(clob_column in CLOB,
 for_how_many_bytes in NUMBER,
 from_which_byte in NUMBER)
return VARCHAR2
is
begin
Return substrb(dbms_lob.substr(clob_column
                            ,for_how_many_bytes
                            ,from_which_byte)
            ,1
            ,for_how_many_bytes);
end;

y llamar a esa función como;

SELECT tocharvalue, clob_to_char_func(tocharvalue, 1, 9999)
FROM (SELECT clob_column AS tocharvalue FROM table_name);

Answer 5

Para agregar a la respuesta.

declare
v_result clob;
begin
---- some operation on v_result
dbms_lob.substr( v_result, 4000 ,length(v_result) - 3999 );

end;
/

En dbms_lob.substr

first parameter es clob que desea extraer.

Second parameter es la cantidad de trozo que quieres extraer.

Third parameter es de qué palabra quiere extraer.

En el ejemplo anterior, sé que mi tamaño de bloque es más de 50000, por lo que quiero los últimos 4000 caracteres.

Answer 6

Para grandes selecciones CLOB también se puede utilizar:

SELECT dbms_lob.substr( column_name, dbms_lob.getlength(column_name), 1) FROM foo

Answer 7

Si está utilizando SQL * Plus intente lo siguiente ...

set long 8000

select ...