AnsweredAssumed Answered

Can I use own made Oracle functions/procedures with NSQL?

Question asked by Dani on Jul 27, 2009
Latest reply on Jul 27, 2009 by Dave
Hi,  I'm trying to create a portlet who contains a list of projects with their respective sponsors. We've got a multivalued attribute for sponsor because we've got projects with many sponsors (none, 1,2,...5, 7...). To concatenate these names in a unique string i use a own defined function in Oracle: CREATE OR REPLACE FUNCTION 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;
The query with this function works fine for my needs:  select b.code, b.name, concatenate_list(CURSOR(SELECT c.full_name from clarity.srm_resources c where c.id=a.value) ) as sponsor
from clarity.odf_multi_valued_lookups a, clarity.inv_investments b
where a.object='project'
and a.pk_id=b.id
order by a.pk_id  The problem that I have is when i try to include the SQL to the NSQL window I get the following error: "NPT-0103: Error when trying to execute the query. Native message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "CONCATENATE_LIST": invalid identifier".My NSQL is:  SELECT @SELECT:DIM:USER_DEF:IMPLIED:proyecto:main.code:ProjectCode@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:proyecto:main.name:ProjectName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:proyecto:main.sponsor:ProjectSponsor@
FROM

(
select b.code, b.name, concatenate_list(CURSOR(SELECT c.full_name from clarity.srm_resources c where c.id=a.value) ) as sponsor
from clarity.odf_multi_valued_lookups a, clarity.inv_investments b
where a.object='project'
and a.pk_id=b.id
order by a.pk_id

)main

WHERE @FILTER@  Is there any solution for this?  Thanks,

Outcomes