Varchar2 size as return code or output parameter ?
472720Mar 20 2006 — edited Mar 20 2006Hi everybody,
As far as i know , the limit size of a varchar2 is (4000) but it seems to be limited in my stored procedure to under then 255 caracteres. Does anyone have an ideas of what happen ?
When i execute the proc below, i get the first 200 caracteres when i change the value in the select SUBSTR(extract(msg_text,'/').getStringVal(),1,200) into chaine to select SUBSTR(extract(msg_text,'/').getStringVal(),1,255) into chaine , in my proc, i get an error message :
The following error has occurred:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5
the msg_text colonne is a XMLTYPE column.
----------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE function read_message(p_id_message IN NUMBER) RETURN VARCHAR2 AS
BEGIN
declare
chaine varchar2(4000);
begin
chaine := '';
dbms_output.enable(1000000);
select SUBSTR(extract(msg_text,'/').getStringVal(),1,200) into chaine
from message
where id_message= p_id_message ;
return chaine;
end;
END;
/