declare
queryCtx DBMS_XMLquery.ctxType;
result CLOB;
begin
-- set up the query context...!
queryCtx :=
DBMS_XMLQuery.newContext('select * from emp');
-- get the result..!
result :=
DBMS_XMLQuery.getXML(queryCtx);
-- Now you can use the result to put
it in tables/send as messages..
printClobOut(result);
DBMS_XMLQuery.closeContext(queryCtx);
-- you must close the query handle..
end;
/
The printClobOut procedure is :
/CREATE OR REPLACE
PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is
xmlstr varchar2(32767);
line varchar2(2000);
begin
xmlstr := dbms_lob.SUBSTR(result,32767);
loop
exit when xmlstr is null;
line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line('| '||line);
xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
end loop;
end;
/
