SQL> create or replace function get_emps_in_dept( p_deptno in number )

  2    return clob
  3  is
  4    l_ctx dbms_xmlquery.ctxType;
  5    l_sql varchar2(100);
  6    l_xml clob;

  7  begin
  8    l_sql := 'select * from emp where deptno = :deptno';
  9    dbms_lob.createtemporary( l_xml, true, dbms_lob.session );
 10    l_ctx := dbms_xmlquery.newContext( l_sql );
 11
 12    dbms_xmlquery.setbindvalue( l_ctx, 'deptno', p_deptno );

 13
 14    dbms_xmlquery.setRaiseNoRowsException( l_ctx,false );
 15
 16    — get the xml fm the context
 17    l_xml := dbms_xmlquery.getXML( l_ctx );
 18
 19    dbms_xmlquery.closeContext(l_ctx);

 20    return l_xml;
 21  exception
 22    when others then
 23      dbms_xmlquery.closeContext(l_ctx);
 24      raise;
 25  end get_emps_in_dept;
 26  /
Function created.