使用Oracle SQL Developer将CLOB导出到文本文件

问题描述

我正在使用Oracle SQL Developer并尝试将表导出到CSV文件.其中一些字段是CLOB字段，并且在许多情况下，在导出时条目会被截断.我正在寻找一种方法来解决问题，因为我的最终目标是在这里不使用Oracle(我收到了一个Oracle转储，该转储已加载到oracle数据库中，但是我正在使用另一种格式的数据，因此请通过CSV作为中介).

如果对此有多种解决方案，考虑到这对我来说是一次性程序，那么我不介意将更多的"hack-ish"类型的解决方案用于涉及更多的正确做"的解决方案.

解决方案

如果您可以访问数据库框中的文件系统，则可以执行以下操作:

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

我从该站点复制并粘贴了> . >

您也可能会找到以下有关UTL_FILE的先前问题有用.它解决了导出到CSV的问题.但是，我不了解UTL_FILE如何处理CLOB的想法或经验.

I am using Oracle SQL Developer and trying to export a table to a CSV file. Some of the fields are CLOB fields, and in many cases the entries are truncated when the export happens. I'm looking for a way to get the whole thing out, as my end goal is to not use Oracle here (I received an Oracle dump - which was loaded into an oracle db, but am using the data in another format so going via CSV as an intermediary).

If there are multiple solutions to this, given that it is a one time procedure for me, I don't mind the more hack-ish type solutions to more involved "do it right" solutions.

解决方案

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.

这篇关于使用Oracle SQL Developer将CLOB导出到文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！