Objective : To print clob data. Solution: Step 1: Create plsql procedure to print the clob. CREATE OR REPLACE PROCEDURE print_clob_to_output (p_clob IN CLOB) IS l_offset INT := 1; BEGIN dbms_output.put_line('Print CLOB'); loop exit when l_offset > dbms_lob.getlength(p_clob); dbms_output.put_line( dbms_lob.substr( p_clob, 255, l_offset ) ); l_offset := l_offset + 255; END LOOP; END print_clob_to_output; This query will print the complete clob data. Step 2: Call the procedure. DECLARE l_xml CLOB; BEGIN SELECT dbms_xmlgen.getxml('select * from emp') xml INTO l_xml FROM dual; print_clob_to_output (l_xml); END; Output: Related Posts: Generating XML data from relational data using Oracle SQL/XML
by Karkuvelraja Thangamariappan ♠ (@tkarkuvelraja)