Following PL/SQL Generates XML Output for supplied SQL.
CREATE OR REPLACE PACKAGE rac_gen_xml
AS
FUNCTION gen_xml_for_sql (
p_sql_string IN VARCHAR2,
p_row_tag IN VARCHAR2 DEFAULT NULL,
p_row_set_tag IN VARCHAR2 DEFAULT NULL
)
RETURN CLOB;
PROCEDURE print_clob_out (l_result IN CLOB);
PROCEDURE convert_clob_to_file (
p_clob IN CLOB,
p_dir IN VARCHAR2,
p_file IN VARCHAR2
);
END rac_gen_xml;
/
CREATE OR REPLACE PACKAGE BODY rac_gen_xml
AS
FUNCTION gen_xml_for_sql (
p_sql_string IN VARCHAR2,
p_row_tag IN VARCHAR2 DEFAULT NULL,
p_row_set_tag IN VARCHAR2 DEFAULT NULL
)
RETURN CLOB
AS
l_queryctx DBMS_XMLQUERY.ctxtype;
l_result CLOB;
BEGIN
-- set up the query context
l_queryctx := DBMS_XMLQUERY.newcontext (p_sql_string);
IF p_row_tag IS NOT NULL
THEN
DBMS_XMLQUERY.setrowtag (l_queryctx, p_row_tag);
END IF;
IF p_row_set_tag IS NOT NULL
THEN
DBMS_XMLQUERY.setrowsettag (l_queryctx, p_row_set_tag);
END IF;
l_result := DBMS_XMLQUERY.getxml (l_queryctx);
DBMS_XMLQUERY.closecontext (l_queryctx);
RETURN l_result;
END gen_xml_for_sql;
PROCEDURE print_clob_out (l_result IN CLOB)
IS
l_xmlstr VARCHAR2 (32767);
l_line VARCHAR2 (2000);
BEGIN
l_xmlstr := DBMS_LOB.SUBSTR (l_result, 32767);
LOOP
EXIT WHEN l_xmlstr IS NULL;
l_line := SUBSTR (l_xmlstr, 1, INSTR (l_xmlstr, CHR (10)) - 1);
DBMS_OUTPUT.put_line (l_line);
l_xmlstr := SUBSTR (l_xmlstr, INSTR (l_xmlstr, CHR (10)) + 1);
END LOOP;
END print_clob_out;
PROCEDURE convert_clob_to_file (
p_clob IN CLOB,
p_dir IN VARCHAR2,
p_file IN VARCHAR2
)
IS
l_start NUMBER := 1;
l_bytelen NUMBER := 32000;
l_len NUMBER;
l_my_vr VARCHAR2 (32000);
l_num NUMBER;
l_output UTL_FILE.file_type;
BEGIN
-- define output directory
l_output := UTL_FILE.fopen (p_dir, p_file, 'w', 32760);
l_start := 1;
l_bytelen := 32000;
-- get length of blob
l_len := DBMS_LOB.getlength (p_clob);
-- save blob length
l_num := l_len;
-- if small enough for a single write
IF l_len < 32760
THEN
DBMS_LOB.READ (p_clob, l_len, l_start, l_my_vr);
UTL_FILE.put (l_output, l_my_vr);
UTL_FILE.fflush (l_output);
ELSE -- write in pieces
l_start := 1;
WHILE l_start < l_len AND l_bytelen > 0
LOOP
DBMS_LOB.READ (p_clob, l_bytelen, l_start, l_my_vr);
UTL_FILE.put (l_output, l_my_vr);
UTL_FILE.fflush (l_output);
-- set the start position for the next cut
l_start := l_start + l_bytelen;
-- set the end position if less than 32000 bytes
l_num := l_num - l_bytelen;
IF l_num < 32000
THEN
l_bytelen := l_num;
END IF;
END LOOP;
END IF;
UTL_FILE.fclose (l_output);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END convert_clob_to_file;
END rac_gen_xml;
/
CREATE OR REPLACE PACKAGE rac_gen_xml
AS
FUNCTION gen_xml_for_sql (
p_sql_string IN VARCHAR2,
p_row_tag IN VARCHAR2 DEFAULT NULL,
p_row_set_tag IN VARCHAR2 DEFAULT NULL
)
RETURN CLOB;
PROCEDURE print_clob_out (l_result IN CLOB);
PROCEDURE convert_clob_to_file (
p_clob IN CLOB,
p_dir IN VARCHAR2,
p_file IN VARCHAR2
);
END rac_gen_xml;
/
CREATE OR REPLACE PACKAGE BODY rac_gen_xml
AS
FUNCTION gen_xml_for_sql (
p_sql_string IN VARCHAR2,
p_row_tag IN VARCHAR2 DEFAULT NULL,
p_row_set_tag IN VARCHAR2 DEFAULT NULL
)
RETURN CLOB
AS
l_queryctx DBMS_XMLQUERY.ctxtype;
l_result CLOB;
BEGIN
-- set up the query context
l_queryctx := DBMS_XMLQUERY.newcontext (p_sql_string);
IF p_row_tag IS NOT NULL
THEN
DBMS_XMLQUERY.setrowtag (l_queryctx, p_row_tag);
END IF;
IF p_row_set_tag IS NOT NULL
THEN
DBMS_XMLQUERY.setrowsettag (l_queryctx, p_row_set_tag);
END IF;
l_result := DBMS_XMLQUERY.getxml (l_queryctx);
DBMS_XMLQUERY.closecontext (l_queryctx);
RETURN l_result;
END gen_xml_for_sql;
PROCEDURE print_clob_out (l_result IN CLOB)
IS
l_xmlstr VARCHAR2 (32767);
l_line VARCHAR2 (2000);
BEGIN
l_xmlstr := DBMS_LOB.SUBSTR (l_result, 32767);
LOOP
EXIT WHEN l_xmlstr IS NULL;
l_line := SUBSTR (l_xmlstr, 1, INSTR (l_xmlstr, CHR (10)) - 1);
DBMS_OUTPUT.put_line (l_line);
l_xmlstr := SUBSTR (l_xmlstr, INSTR (l_xmlstr, CHR (10)) + 1);
END LOOP;
END print_clob_out;
PROCEDURE convert_clob_to_file (
p_clob IN CLOB,
p_dir IN VARCHAR2,
p_file IN VARCHAR2
)
IS
l_start NUMBER := 1;
l_bytelen NUMBER := 32000;
l_len NUMBER;
l_my_vr VARCHAR2 (32000);
l_num NUMBER;
l_output UTL_FILE.file_type;
BEGIN
-- define output directory
l_output := UTL_FILE.fopen (p_dir, p_file, 'w', 32760);
l_start := 1;
l_bytelen := 32000;
-- get length of blob
l_len := DBMS_LOB.getlength (p_clob);
-- save blob length
l_num := l_len;
-- if small enough for a single write
IF l_len < 32760
THEN
DBMS_LOB.READ (p_clob, l_len, l_start, l_my_vr);
UTL_FILE.put (l_output, l_my_vr);
UTL_FILE.fflush (l_output);
ELSE -- write in pieces
l_start := 1;
WHILE l_start < l_len AND l_bytelen > 0
LOOP
DBMS_LOB.READ (p_clob, l_bytelen, l_start, l_my_vr);
UTL_FILE.put (l_output, l_my_vr);
UTL_FILE.fflush (l_output);
-- set the start position for the next cut
l_start := l_start + l_bytelen;
-- set the end position if less than 32000 bytes
l_num := l_num - l_bytelen;
IF l_num < 32000
THEN
l_bytelen := l_num;
END IF;
END LOOP;
END IF;
UTL_FILE.fclose (l_output);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END convert_clob_to_file;
END rac_gen_xml;
/