String buffer too small 2

[ronanBas]

The following function returns the delimited string

CREATE OR REPLACE FUNCTION GET_DELIMITED_STR
(
    p_cursor sys_refcursor,
    p_del varchar2 := ','
) return varchar2
is
    l_value   varchar2(32767);
    l_result  varchar2(32767);
begin
    loop
        fetch p_cursor into l_value;
        exit when p_cursor%notfound;
        if l_result is not null then
            l_result := l_result || p_del;
        end if;
        l_result := l_result || l_value;
    end loop;
    return l_result;
end get_delimited_str;

Now this function is been called as below

 select get_delimited_str(cursor
                                     (SELECT distinct f.desc
                                        FROM User_Act U,
                                             func_grp    f
                                       WHERE f.func_grp_id = u.func_grp_id
                                         ))
          into v_fnGrp
          from dual;

I get a runtime exception that string buffer too small as
I have v_fnGrp varchar2(1000);
and Function (fnc_get_delimited_str) puts a value into OUT param " l_result varchar2(32767)" that the caller cant cope with it . i.e v_fnGrp.

However the query

SELECT distinct f.desc FROM User_Act U,func_grp f WHERE f.func_grp_id = u.func_grp_id

retuns only 3 rows Reporting , static Data , Report Data.

Since the SELECT distinct f.desc FROM User_Act U,func_grp f WHERE f.func_grp_id = u.func_grp_id returns the delimited string which is not more than 1000 char long.
it hardly makes any sense that Oracle throws error "String buffer too small i.e ORA-06502"
Please comment.

 

[taupirho]

I think you are running into the varchar2(4000) limit for varchar2 database columns. I'm not sure how or why but thats all I can think of ! certainly I can reproducce your error. It works OK when the total length of the returned concatenated string is 4000 or under but I then get your error when it goes above that. Guess its something internal in the way Oracle treats cursors


In order to understand recursion, you must first understand recursion.

 

[carp]

What result do you get from running the query:

select get_delimited_str(cursor
                                     (SELECT distinct f.desc
                                        FROM User_Act U,
                                             func_grp    f
                                       WHERE f.func_grp_id = u.func_grp_id
                                         ))          from dual;

If it is more than 1000 characters, then your problem is how you have defined v_fnGrp.

 

[ronanBas]

Thanks Carp and tauprhio
Problem resolved when I rest the PL/SQL variable size
Thanks