SQL: How to get substring from function output in sql?

Since you're applying a substr, presumably the value being returned by your function is greater than 4000 characters. If that's the case then you will get this error from SQL, you can't avoid it. It's trying to assign the long string value to an (implicit) SQL-level varchar2, which of course cannot be more than 4000 characters either, before passing that to the substr function.

You will have to add the substr to the return from your function, or if it is sometimes called from somewhere that can handle the long values, you can have a wrapper function that only returns the first 4000 characters - so you can have the appropriate value as needed.

To demonstrate with a dummy function to (inefficiently!) create a large string:

create or replace function myfunction(strlen number) return varchar2 is
  str varchar2(32767) := 'X';
begin
  while length(str) < least(strlen, 32767) loop
    str := str || 'X';
  end loop;
  return str;
end myfunction;
/

This is fine because the function's output doesn't exceed the SQL varchar2 size:

select length(substr(myfunction(4000),0,4000)) from dual;

LENGTH(SUBSTR(MYFUNCTION(4000),0,4000))
---------------------------------------
                                   4000 

But this gets your error because the function's output is too long:

select length(substr(myfunction(4001),0,4000)) from dual;

SQL Error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "STACKOVERFLOW.MYFUNCTION", line 7
06502. 00000 -  "PL/SQL: numeric or value error%s"

With a simple wrapper function as a workaround:

create or replace function trunc_myfunction(strlen number) return varchar2 is
begin
  return substr(myfunction(strlen), 0, 4000);
end;
/

select length(substr(trunc_myfunction(4000),0,4000)) from dual;

LENGTH(SUBSTR(TRUNC_MYFUNCTION(4000),0,4000))
---------------------------------------------
                                         4000 

select length(substr(trunc_myfunction(4001),0,4000)) from dual;

LENGTH(SUBSTR(TRUNC_MYFUNCTION(4001),0,4000))
---------------------------------------------
                                         4000 

select length(substr(trunc_myfunction(32767),0,4000)) from dual;

LENGTH(SUBSTR(TRUNC_MYFUNCTION(32767),0,4000))
----------------------------------------------
                                          4000 

Most probably you are trying to store more characters than is allowed in one of the variables that you use in your function. See: PL/SQL: numeric or value error: character string buffer too small %ROWTYPE

Simplest example:

DECLARE
  v_varchar2_test VARCHAR2(5);
BEGIN
  v_varchar2_test := '123456';
END;

And the error is, like in your case, ORA-06502.

Make sure that the value returned by the function is of the correct type; I have just tried it like this:

create or replace 
FUNCTION MONTH
(DATA IN DATE)
RETURN VARCHAR IS
BEGIN
  RETURN TO_CHAR(DATA, 'MM');
END;

and the call:

SELECT substr(MONTH(SYSDATE),0,1) FROM DUAL;

and it worked.