1

I have a variable xml_ which is a CLOB data type variable I use to store some XML data using a PL/SQL procedure dynamically. I need to write this CLOB data into a file in my machine. The database server is not my machine. Is there any way to do this? I am using Oracle 11g.

I am using the procedure given as an answer in another question, but it uses UTL_FILE package , and as I don't have direct access to the database server machine, I would like to know how to write the file into a directory in my local machine instead.

Edit: I am creating the xml dynamically. I tried to store the 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.

Edit2: I got this resolved via Oracle SQL Developer. The CLOB data was over 2 GB, Oracle SQL Developer showed me the column data, hence I was able to export it into a file in my client machine itself.

Uthpala Dl
  • 45
  • 8
  • If you want to write to the client file system, you'd need a program running on the client (i.e. not PL/SQL). `SQL*Plus` via the `spool` command can write to a file on the client. You can save a CLOB to a file from SQL Developer. Pretty much any other IDE should be able to do the same thing. – Justin Cave Feb 05 '21 at 08:25
  • I am creating the xml dynamically. I tried to store the data in a table with a CLOB column, but it shows 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. Can you please tell me how I can use SQL plus to write my variable data/column data to a file? I am a newbie to Oracle. – Uthpala Dl Feb 05 '21 at 09:28
  • @UthpalaDl The PL/SQL Developer `` sometimes only happens in specific tabs of the Large Data Editor. Try clicking on the Text tab. – Jon Heller Feb 06 '21 at 05:43
  • @JonHeller Hey Jon, I finally was able to see the CLOB column data using Oracle SQL Developer. I tried another few options as well like using SQL Plus tool which also worked, even though it took a long time to write the data. :) Thank you for your input! – Uthpala Dl Feb 12 '21 at 11:09

0 Answers0