Oracle

SET SERVEROUTPUT ON

DECLARE
ctxh dbms_xmlgen.ctxhandle;
result CLOB;

xmlinput XMLTYPE;
xslt XMLTYPE;
xmlfinal XMLTYPE;

sqlstmt varchar2(5024) := 'select 12345 as "usernr", ''myrolename'' as "role" from dual';

BEGIN
ctxh:= dbms_xmlgen.newcontext(sqlstmt);
DBMS_XMLGEN.setRowSetTag(ctxh, 'myresult');
DBMS_XMLGEN.setRowTag(ctxh, 'mytag');

result := dbms_xmlgen.getxml(ctxh);
xmlinput := XMLTYPE.CREATEXML(result);

xslt := XMLTYPE.CREATEXML('

');

SELECT XMLTRANSFORM(xmlinput, xslt)
INTO xmlfinal
FROM dual;

dbms_xmlgen.closecontext(ctxh);
DBMS_OUTPUT.PUT_LINE(xmlfinal.getstringval() );
END;

select level,  LPAD(' ', 2 * level - 1) ||  orgunitname,
orgid, orgunits.parent_orgid
from orgunits
start with orgid = '50077170' -- this is the root of your conversation
connect by prior orgid=parent_orgid

create or replace function GET_ROLEFORUSER(in_roleid IN int, in_condition IN varchar2)

return TBL_ROLE2CTX PIPELINED AS

query varchar(2000);
TYPE DynCursor IS REF CURSOR;
result_cursor DynCursor;
res_id int;
res_roleid int;
res_ctxid int;
res_dateend date;
res_lastname varchar(100);
res_firstname varchar(100);

BEGIN

query := 'SELECT distinct r.id, r.roleid, r.ctxid, r.dateend, u.vorname as firstname, u.name as lastname
from role r
inner join user u
on u.usernr = r.usernr
where rc.roleid = :in_roleid and ' || in_condition;

OPEN result_cursor FOR query using in_roleid;

LOOP
FETCH result_cursor into res_id, res_roleid, res_ctxid, res_dateend, res_lastname, res_firstname;
exit when result_cursor%NOTFOUND;
PIPE ROW( REC_ROLE2CTX( res_id, res_roleid, res_ctxid, res_dateend, res_lastname,res_firstname) );
END LOOP;

close result_cursor;
return;

END GET_ROLEFORUSER;