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
CURSOR c_data IS
SELECT '<ns1:data>The Quick Brown Fox</ns1:data>' content_line
l_content_line VARCHAR2(1000) := '';
l_content_line := '<?xml version="1.0" encoding="UTF-8"?>';
l_content_line := '<ns1:SiebelMessage xmlns:ns1="http://www.kwatog.com/xml/TerminateLine">';
FOR c IN c_data
l_content_line := c.content_line;
l_content_line := '</ns1:SiebelMessage>';
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.