Showing posts from November, 2017

Print CLOB data using Oracle PL/SQL

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