Oracle PL/SQL Generates XML Output for supplied SQL.

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;

/