Prevent Oracle SQL Developer from truncating CLOBs on export

Question

I want to export a query result that contains large CLOBs to a CSV file. However, once exported in the CSV fields, CLOBs are truncated after around 4K characters (i.e. they'll prematurely end with "…"). How to prevent Oracle SQL Developer from truncating CLOBs on export?

Answer 1

You could bypass Oracle SQL Developer for the export, e.g. you could use use a Python script to take care of the export so that the CLOBs won't get truncated:

from __future__ import print_function
from __future__ import division

import time
import cx_Oracle

def get_cursor():
    '''
    Get a cursor to the database
    '''
    # http://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, 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

Answer 2

I'm using using SQL Developer Version 4.1.3.20 and have the same issue. The only thing that worked for me was selecting XML as the export format. Doing this, I was able to export a ~135,000 character JSON string with no truncation.

The second problem, however, is immediately after exporting I attempted to import data and SQL Developer said it could not open the file due to error "null".