function return datatype varchar2 > 4000 byte at runtime

[tumtrah]

The following function should return a characterstring
up to 5000 bytes long. But it works only up to 4000 bytes.

create or replace function alfa_func(alfa_length number)
return varchar2
as
-- in PL/SQL the max length datatype varchar2 is 32,767 alfa_string varchar2(5000);
j integer;
begin
alfa_string := '';
for j in 1..alfa_length loop
alfa_string := alfa_string||'A';
end loop;
return alfa_string;
end alfa_func;

When I call the function like
select alfa_func(4000) from dual;
I get 4000 "A"
but the call
select alfa_func(4001) from dual;
returns ORA-06502

what's wrong ?
how can a function return a characterstring > 4000 byte ?

Thank You

 

[spenglerr]

Hello tumtrah,

a VARCHAR2 has a limit of 4000 characters. So you will never be able to return more than 4000 characters returning a VARCHAR2 you will have to return a CLOB not a VARCHAR2