Friday, October 24, 2008

Oracle PL/SQL | To returns single line query output as an XML

create or replace procedure QueryToXML(
-- Date: October 23, 2008
-- Programmer: Ranjeeth Shetty
-- Parameter: 1. pvar_rowTag varchar2
-- To Set the Row Tag for XML Created
-- 2. pvar_SQLQuery varchar2
-- To Send Single row Query
-- 3. pclb_resultXML Clob
-- Output variable that returns clob XML

pvar_rowTag in varchar2 := 'RowDetail',
pvar_SQLQuery in varchar2,
pclb_resultXML out clob
) as
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
BEGIN
ctx := dbms_xmlgen.newContext( pvar_SQLQuery );
dbms_xmlgen.setRowTag(ctx, pvar_rowTag );
xml := dbms_xmlgen.getXML(ctx);
dbms_xmlgen.closeContext(ctx);
pclb_resultXML := xml;
END;

/

No comments: