ORA-06502 - string buffer too small numerical or value - Long Field

Question

Good morning, I’m performing a query to query data in a Long-type field. I created the following Function to bring the data:

CREATE OR REPLACE FUNCTION "SEARCH_LONG" (r rowid) return varchar2 is
temporary_varchar varchar2(32000);
begin
select pm.ds_evolucao into temporary_varchar from dbamv.pre_med PM where rowid=r;
return temporary_varchar;
end;

But when executing the query

SELECT pre.cd_atendimento,pre.hr_pre_med,pre.cd_prestador 
   from (select a1.cd_atendimento,search_long(a1.rowid) 
                ds_evolucao,a1.HR_PRE_MED,a1.CD_PRESTADOR
           from pre_med a1) pre
  where upper(pre.ds_evolucao) like '%SEPSE%'
       and pre.hr_pre_med > '01/08/2019'
       and cd_atendimento not in 
       (SELECT P.CD_ATENDIMENTO
          FROM ITPRE_MED IT 
          JOIN PRE_MED P ON IT.CD_PRE_MED = P.CD_PRE_MED
         WHERE CD_TIP_PRESC = 74076 )

Is returning me this error message:

ORA-06502: PL/SQL: erro: buffer de string de caracteres pequeno demais numérico ou de valor
ORA-06512: em "DBAMV.SEARCH_LONG", line 5

Answer 1

Replace the line

select pm.ds_evolucao into temporary_varchar

in the role to:

select substr( pm.ds_evolucao, 1, 32000 ) into temporary_varchar

you will see that it works. It turns out that the string size pm.ds_evolucao is larger than the variable temporary_varchar

EDITION

Transforming test LONG in VARCHAR2

DECLARE
  VAR1 LONG;
  VAR2 VARCHAR2(32000);
BEGIN
  SELECT TEXT INTO VAR1 FROM USER_VIEWS WHERE ROWNUM = 1;  
  VAR2 := SUBSTR(VAR1, 1, 32000);
  DBMS_OUTPUT.PUT_LINE(VAR2);
END;

Works smoothly.

Answer 2

Your column ds_evolucao is the type LONG and when you try to convert to VARCHAR2 without specifying the size, the database tries to convert first to CLOB which is larger than the reported size.

To successfully assign you can limit the total size of the result with the function SUBSTR:

SELECT SUBSTR(pm.ds_evolucao, 1, 32000)
  INTO temporary_varchar
  FROM dbamv.pre_med PM
 WHERE rowid = r;