我想将包含大型CLOB的查询结果导出到CSV文件.但是,一旦在CSV文件中导出,CLOB就会在大约4K个字符后被截断(即它们过早地以“……”结束).如何防止Oracle sql Developer在导出时截断CLOB?
您可以绕过Oracle sql Developer进行导出,例如您可以使用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 = oracle_db.get_cursor() sql_filename = 'your_query.sql' # Write your query there cursor = oracle_db.execute_sql_file(sql_filename,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