SQL: How to get the substring from the SQL function output?

advertisements

IN one of My Application. We have one query, where actully we are calling a function and function returns o/p as String. Any one have idea how to get substring from function returned o/p?

I am using like this

select substr(myfunction(),0,4000) from dual.

I am getting below issue.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

Please help me . Thanks in Advance.


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