SQL & PL/SQL

Hi,

Ideally you should not try to get the multiple row result as how you are trying to.

You cannot get the desired result as per the way you are trying to get it.

we cannot provide parameter values like you have providing. for that you need to if you are trying to get the resule for  total 4 object id then you need to declare 4 parameter in function and you need to use IN clauase in where condition like shown below:-

create or replace

FUNCTION concatenate_list (p1_in VARCHAR2,p2_in VARCHAR2,p3_in VARCHAR2,p4_in VARCHAR2)

  RETURN  VARCHAR2

IS

  l_return  VARCHAR2(32767);

BEGIN

    SELECT ven_mod || ': '|| names into l_return from tobject where object_id in (p1_in,p2_in,p3_in,p4_in);

  RETURN LTRIM(l_return);

END;

Regards,
Chanchal Wankhade.

Hi,

    Please try this..

CREATE OR REPLACE FUNCTION APPS.concatenate_list (p_cursor IN  SYS_REFCURSOR)

  RETURN  VARCHAR2

IS

  l_return  VARCHAR2(32767);

  l_temp    VARCHAR2(32767);

BEGIN

  LOOP

    FETCH p_cursor

    INTO  l_temp;

    EXIT WHEN p_cursor%NOTFOUND;

    l_return := l_return || ',' || l_temp;

  END LOOP;

  RETURN LTRIM(l_return, ',');

END;

source from

ORACLE-BASE - String Aggregation Techniques

select substr(str,instr(str,',',1,level)+1,instr(str,',',1,level+1)-instr(str,',',1,level)-1) from (select ','||'11,12,13,14'||',' str from dual )

connect by level<= length(str)-length(replace(str,',',''))-1

thanks for reply.

I have to pass the object_id values dynamically, I dont know the list of values passing to this function (might be 1 or 10 or 100).

suppose I have select statement like

SELECT LISTAGG(level, ', ') WITHIN GROUP( ORDER BY level)  FROM dual CONNECT BY LEVEL <= 200;

its returning results, but when I change the value to 2000 then its giving me the error. (ORA-01489: result of string concatenation is too long)

Is there any other way to handle this. (I dont want to use wm_concat, as it is not oracle documented)

Hi,

Following workaround may meet your requirement:

create or replace FUNCTION concatenate_list (p_in VARCHAR2)

  RETURN  clob

IS

  l_return  VARCHAR2(32767);

  v_query varchar2(4000);

  temp_str varchar2(1000);

  cur_strlist sys_refcursor;

BEGIN

  v_query := 'SELECT  ven_mod || '': '' || names  as concat_str from tobject  where  object_id  in (' ||

              p_in || ')';

  open cur_strlist for v_query;

  loop

    fetch cur_strlist into temp_str;

    exit when cur_strlist%notfound;

    l_return := l_return || '~' || temp_str;

  end loop;

  close cur_strlist;

  RETURN substr(l_return,2);

END;

Try this...

SELECT XMLAGG(XMLELEMENT(e,DECODE(LEVEL,1,'',',')||LEVEL).EXTRACT('//text()')) FROM dual CONNECT BY LEVEL <= 2000;