Limites de los varchar2
Tengo definida una función almacenada en la BD. Esta función retorna un varchar2. Bien, la función me falla con un "buffer overflow" cuando el varchar2 a retornar supera los 4000 caracteres. Sé que se pueden definir variables de tipo VARCHAR2 hasta de 32767 caracteres y que el límite en una tabla con campos VARCHAR2 es de 4000 (en Oracle 8i). ¿Por qué me falla? ¿No debería ser el límite de 32727 igual que en un variable local? ¿Es un parámetro de BD lo que limita a la función?
Ayudarme.
Ayudarme.
1 respuesta
Respuesta de caskorro
1
Te pego una respuesta que da Oracle a un problema similar y que he encontrado en el support del propio Oracle.
Saludos.
From: Roger Troller 02-Apr-01 08:54
Subject: Re : Size limit on Varchar2 returning from function?
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
If you use your function within sql then the max size of the varchar depends on the sql datatype which is
4000 for a varchar2 with oracle8
2000 for a varchar2 with oracle7
when using the function as assignment for a variable within pl/sql then the size depends on the definition of that variable. The problem you have with dbms_output is that dbms_output.put_line is able to process strings up to a size of 255 characters only therefore you have to split your string into chunks.
Bye
Roger
Saludos.
From: Roger Troller 02-Apr-01 08:54
Subject: Re : Size limit on Varchar2 returning from function?
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
If you use your function within sql then the max size of the varchar depends on the sql datatype which is
4000 for a varchar2 with oracle8
2000 for a varchar2 with oracle7
when using the function as assignment for a variable within pl/sql then the size depends on the definition of that variable. The problem you have with dbms_output is that dbms_output.put_line is able to process strings up to a size of 255 characters only therefore you have to split your string into chunks.
Bye
Roger
