防止Oracle SQL Developer在导出时截断CLOB

发布时间：2020-12-12 13:08:37 所属栏目：百科来源：网络整理

导读：我想将包含大型CLOB的查询结果导出到CSV文件.但是,一旦在CSV文件中导出,CLOB就会在大约4K个字符后被截断(即它们过早地以“……”结束).如何防止Oracle SQL Developer在导出时截断CLOB？您可以绕过Oracle SQL Developer进行导出,例如您可以使用Python脚本来

我想将包含大型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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!