Forum Stats

  • 3,734,273 Users
  • 2,246,935 Discussions
  • 7,857,216 Comments

Discussions

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

User_T3DXH
User_T3DXH Member Posts: 1
edited Feb 5, 2021 8:35PM in SQL & PL/SQL

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.

Tagged:

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 5,588 Silver Crown

    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
    Paulzip Member Posts: 8,322 Blue Diamond
    edited Feb 6, 2021 5:29PM

    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 ....
    /
    


Sign In or Register to comment.