The most common method of writing into a file is via UTL_FILE. However, this package cannot direct the output to your local folder. That means you have to create a logical directory in the db server and get the physical file from there.

But by using DBMS_OUTPUT together with other sql*plus settings and spool mechanism, we can write the output into a local client file. Here’s how I do it.

SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET DEFINE OFF
SET LINESIZE 1000
SET PAGESIZE 0

spool c:\sample.xml
DECLARE
CURSOR c_data IS
SELECT '<ns1:data>The Quick Brown Fox</ns1:data>' content_line
FROM dual;

l_content_line VARCHAR2(1000) := '';
BEGIN
DBMS_OUTPUT.ENABLE;
l_content_line := '<?xml version="1.0" encoding="UTF-8"?>';
DBMS_OUTPUT.PUT_LINE(l_content_line);
l_content_line := '<ns1:SiebelMessage xmlns:ns1="http://www.kwatog.com/xml/TerminateLine">';
DBMS_OUTPUT.PUT_LINE(l_content_line);

FOR c IN c_data
LOOP
l_content_line := c.content_line;
DBMS_OUTPUT.PUT_LINE(l_content_line);
END LOOP;
l_content_line := '</ns1:SiebelMessage>';
DBMS_OUTPUT.PUT_LINE(l_content_line);
END;
/

SPOOL OFF

I’d like to emphasize again that this may be just one of the ways of doing this. I know it works because I used it extensively. I just needed to write it down because of several instansces lately when I was tutoring new staffs.

Comments are closed.

Post Navigation