<%@ page language="PL/SQL" %> <%@ plsql procedure="sqltoxml" %> <%@ plsql parameter="p_sqlString" Type="VARCHAR2" default="NULL"%> <%! /** Overview : This page will create a procedure to receive a sql string and displays the result of the query in XML form. This procedure makes use of the PL/SQL API which is available in SQLToXML utility. Output of the XML can be printed to a HTP buffer by creating a function that uses the HTP package calls. Modification History: Parameter: p_sqlString - Receives SqlString to output the result in XML form Person Date Comments ------------------------------------------------------------- Savitha Rajeev 22-AUG-2000 XMLHotelInfo Sample **/ /** Variable declarations **/ l_xmlString CLOB; l_amount INTEGER := 4000; l_position INTEGER := 1; l_charString VARCHAR2(4000); l_inclDTD NUMBER := 0; -- %> <% BEGIN IF UPPER('N') = 'Y' THEN l_inclDTD := 1; END IF; /** Get XML output for the passed SQL string **/ l_xmlString := xmlgen.getXML(p_sqlString,l_inclDTD); dbms_lob.open(l_xmlString,DBMS_LOB.LOB_READONLY); LOOP dbms_lob.read(l_xmlString,l_amount,l_position,l_charString); /** Print the XML output **/ htp.prn(l_charString); l_position := l_position + l_amount; END LOOP; EXCEPTION WHEN no_data_found THEN dbms_lob.close(l_xmlString); dbms_lob.freetemporary(l_xmlString); END; %>