使用 Oracle SQL Developer 将 CLOB 导出到文本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1434443/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exporting a CLOB to a text file using Oracle SQL Developer
提问by geoffjentry
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).
我正在使用 Oracle SQL Developer 并尝试将表导出到 CSV 文件。某些字段是 CLOB 字段，并且在许多情况下，在导出时条目会被截断。我正在寻找一种方法来解决整个问题，因为我的最终目标是不在这里使用 Oracle（我收到了一个 Oracle 转储 - 它已加载到一个 oracle 数据库中，但我正在使用另一种格式的数据，因此通过CSV 作为中介）。
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.
如果对此有多种解决方案，考虑到它对我来说是一次性程序，我不介意使用更多 hack-ish 类型的解决方案来解决更多涉及的“做对了”的解决方案。
回答by JD Long
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_FILEuseful. It addresses exporting to CSV. I have no idea or experience with how UTL_FILE handles CLOBs, however.
您可能还会发现上一个有关 UTL_FILE 的问题很有用。它解决了导出到 CSV 的问题。但是，我对 UTL_FILE 如何处理 CLOB 没有任何想法或经验。
回答by Franck Dernoncourt
You can use a Python script to take care of the export, the CLOBs won't get truncated:
您可以使用 Python 脚本来处理导出，CLOB 不会被截断：
from __future__ import print_function
from __future__ import division
import time
import cx_Oracle
def get_cursor():
'''
Get a cursor to the database
'''
# https://stackoverflow.com/questions/24149138/cx-oracle-doesnt-connect-when-using-sid-instead-of-service-name-on-connection-s
# http://www.oracle.com/technetwork/articles/dsl/prez-python-queries-101587.html
ip = '' # E.g. '127.0.0.1'
port = '' # e.g. '3306'
sid = ''
dsnStr = cx_Oracle.makedsn(ip, port, sid)
username = '' # E.g. 'FRANCK'
password = '' # E.g. '123456'
db = cx_Oracle.connect(user=username, password=password, dsn=dsnStr)
cursor = db.cursor()
return cursor
def read_sql(filename):
'''
Read an SQL file and return it as a string
'''
file = open(filename, 'r')
return ' '.join(file.readlines()).replace(';', '')
def execute_sql_file(filename, cursor, verbose = False, display_query = False):
'''
Execute an SQL file and return the results
'''
sql = read_sql(filename)
if display_query: print(sql)
start = time.time()
if verbose: print('SQL query started... ', end='')
cursor.execute(sql)
if verbose:
end = time.time()
print('SQL query done. (took {0} seconds)'.format(end - start))
return cursor
def main():
'''
This is the main function
'''
# Demo:
cursor = get_cursor()
sql_filename = 'your_query.sql' # Write your query there
cursor = execute_sql_file(sql_filename, cursor, True)
result_filename = 'result.csv' # Will export your query result there
result_file = open(result_filename, 'w')
delimiter = ','
for row in cursor:
for count, column in enumerate(row):
if count > 0: result_file.write(delimiter)
result_file.write(str(column))
result_file.write('\n')
result_file.close()
if __name__ == "__main__":
main()
#cProfile.run('main()') # if you want to do some profiling
FYI: Help installing cx_Oracle
仅供参考：帮助安装 cx_Oracle
回答by Abecee
As Oracle SQL Developer is explicitly mentioned and "hack-ish type solutions" were encouraged (and in case somebody is still in need…):
正如 Oracle SQL Developer 被明确提到的那样，并且鼓励使用“hack-ish 类型的解决方案”（以防有人仍然需要……）：
If a single statement is executed from a SQL Worksheet (in Oracle SQL Developer), the result is displayed as a table. After clicking the result table, use either keyboard shortcuts or the Editmenu to first mark the whole table and then to copy its content. Proceed to your text editor of choice. Paste. Save to a file. Hopefully done. ;-)
如果从 SQL 工作表（在 Oracle SQL Developer 中）执行单个语句，结果将显示为表格。单击结果表后，使用键盘快捷键或“编辑”菜单首先标记整个表，然后复制其内容。继续使用您选择的文本编辑器。粘贴。保存到文件。希望完成。;-)
Works even for CLOBs exceeding 4000 characters. Whether or not it actually helps, will very much depend on the CLOBs' actual content. Sometimes some SQL pre-processing might get you there…
甚至适用于超过 4000 个字符的 CLOB。它是否真的有帮助，将在很大程度上取决于 CLOB 的实际内容。有时一些 SQL 预处理可能会让你到达那里......
Alternatively try in the result table's local menu Export…. Going from there through the Excel 95-2003format option might work (better than CSV or text/TSV).
或者尝试在结果表中的本地菜单导出...。从那里通过Excel 95-2003格式选项可能会起作用（比 CSV 或文本/TSV 更好）。
Please comment, if and as this requires adjustment / further detail.
请评论，如果这需要调整/进一步的细节。
回答by Neil Kodner
assuming by an Oracle dump you meant a .dmp (either from export or expdp), you're looking at a binary file. You'll need to import the dumpfile into an Oracle database and then export the data to plain text using UTL_FILE or other means.
假设通过 Oracle 转储您的意思是 .dmp（来自导出或 expdp），您正在查看一个二进制文件。您需要将转储文件导入 Oracle 数据库，然后使用 UTL_FILE 或其他方式将数据导出为纯文本。
