How to write a CLOB to an OS file in Oracle without using UTL_FILE package?

User_T3DXH

I have a variable xml_ which is a CLOB data type variable I use to store some XML data (dynamically created) using a PL/SQL procedure. I need to write this CLOB data into a file in my machine. The database server is not my machine.As I don't have direct access to the database server machine, so I would like to know how to write the file into a directory in my local machine without the usage of UTL_FILE package.

Is there any way to do this? I am using Oracle 11g.

I tried to store the XML data in a table with a CLOB column, but it shows <value error> when I try to see the data using PLSQL Developer. I am thinking it is because the size of the xml is too large to open from the PLSQL editor or there is something wrong with the created data. Hence I am trying to write the data to a file to check if there are some invalid characters etc.

Mike Kutz

TL/DR - you can't do what you requested from PL/SQ being ran on the Server

Servers (eg Oracle RDBMS) are NOT ALLOWED to save data on the Client machine.

In order to save data on the Client machine

• Client uses software to connect to Server
• Client software makes a request for data to the Server
• Server responds with the requested data
• Client does something with it like saving it on the Client's hard drive.

If this is a one-off task: Use SQL Developer to acquire the clob value and then "save" the data in a new Worksheet. Notepad could also work.

If this is a repeatable task:

• Create a function that returns the CLOB of the XML
• Wrap the function with ORDS
• Use a script to make the REST call and save the data on the Client computer.

If ORDS is not available, you'll need to write your own software to preform the task at hand. Java, Node.JS, Python, FORTRAN, C/C++, .Net, language-of-the-month.

My $0.02

MK

Paulzip

Just use SQL * Plus, select the data and spool it to a file

e.g.

set linesize 500
set longchunksize 500
set trimspool on
set trimout on
set wrap off
set pagesize 1000
set echo on
set tab off
set colsep '|'
set sqlprompt ''
set sqlnumber off
set define off
spool C:\Users\Paulzip\Documents\my_sql_output.txt

select ...
from ....
/