Dear,
Listtag function is giving an error, might be column contain more value then defined in list tag. However, i tried to make dynamic function similar to listtag but still that one is also giving an error.
ORA-01489: result of string concatenation is too long
CREATE OR REPLACE function getlistagg(p_where varchar2,p_col_name varchar2,p_table varchar2) RETURN varchar2 IS
TYPE cur_typ IS REF CURSOR;
c cur_typ;
query_str VARCHAR2(200);
inv_num NUMBER;
v_col_val VARCHAR2(100);
v_listtag VARCHAR2(4000);
BEGIN
query_str := 'SELECT '||p_col_name||' FROM '||p_table||' where '||p_where;
OPEN c FOR query_str;
LOOP
FETCH c INTO v_col_val;
EXIT WHEN c%NOTFOUND;
v_listtag := v_listtag||v_col_val;
END LOOP;
CLOSE c;
return v_listtag;
END;
select getlistagg(' where deptno = 10','ename','ename') from dual