Exporting a CLOB to a text file using Oracle SQL Developer

by

if you have access to the file system on your database box you could do something like this:

CREATE OR REPLACE DIRECTORY documents AS 'C:\';
SET SERVEROUTPUT ON
DECLARE
  l_file    UTL_FILE.FILE_TYPE;
  l_clob    CLOB;
  l_buffer  VARCHAR2(32767);
  l_amount  BINARY_INTEGER := 32767;
  l_pos     INTEGER := 1;
BEGIN
  SELECT col1
  INTO   l_clob
  FROM   tab1
  WHERE  rownum = 1;

  l_file := UTL_FILE.fopen('DOCUMENTS', 'Sample2.txt', 'w', 32767);

  LOOP
    DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer);
    UTL_FILE.put(l_file, l_buffer);
    l_pos := l_pos + l_amount;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(SQLERRM);
    UTL_FILE.fclose(l_file);
END;
/

Which I copied and pasted from this site.

You may also find this previous question about UTL_FILE useful. It addresses exporting to CSV. I have no idea or experience with how UTL_FILE handles CLOBs, however.

More Related Contents:

  1. How to compare clob columns of single table in oracle? [closed]
  2. SQL function PIVOT with 2 column
  3. How to load a large number of strings to match with oracle database?
  4. How can I find which tables reference a given table in Oracle SQL Developer?
  5. Oracle SQL escape character (for a ‘&’)
  6. How to query a CLOB column in Oracle
  7. How to extract week number in sql
  8. How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?
  9. Search All Fields In All Tables For A Specific Value (Oracle)
  10. Oracle: how to UPSERT (update or insert into a table?)
  11. Get top results for each group (in Oracle)
  12. Paging with Oracle
  13. Oracle Differences between NVL and Coalesce
  14. ORA-12560: TNS:protocol adaptor error
  15. What exactly do quotation marks around the table name do?
  16. Calculating difference between two timestamps in Oracle in milliseconds
  17. How can I get column names from a table in Oracle?
  18. How to store only time; not date and time?
  19. How does GROUP BY work?
  20. Oracle equivalent of Postgres’ DISTINCT ON?
  21. How to get the last row of an Oracle table
  22. What is the difference between ‘YYYY’ and ‘RRRR’ in Oracle SQL
  23. Oracle – How to create a materialized view with FAST REFRESH and JOINS
  24. How to populate calendar table in Oracle?
  25. Oracle 11g – Check constraint with RegEx
  26. Oracle- Split string comma delimited (string contains spaces and consecutive commas)
  27. The network adapter could not establish the connection – Oracle 11g
  28. How do I list all the columns in a table?
  29. Oracle: SQL query to find all the triggers belonging to the tables?
  30. oracle SQL how to remove time from date

Leave a Comment