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?

@sstan Thanks, the question you pointed to does it require to use Oracle SQL Developer for the export, unlike mine. — Franck Dernoncourt, Jul 26 '15 at 20:14
I don't think you can use SQL Developer to export >4k clobs. Jeff Smith http://stackoverflow.com/users/1156452/thatjeffsmith is the product manager for SQL Developer so he will be able to provide the definitive answer. Nice Python script BTW. — Ian Carpenter, Jul 27 '15 at 08:58
I'm rather tempted to suggest moving the Python script to an answer as a potential work around. ;) — jpmc26, Sep 11 '17 at 17:15

score 2 · Answer 1 · answered Sep 13 '17 at 01:23

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

score 1 · Answer 2 · answered Oct 14 '16 at 00:38

1

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".

answered Oct 14 '16 at 00:38

Eric Majerus

1,099
1
12
23

1

this works for me too when setting ```set long 100000; set longchunksize 100000;``` but i cannot import them to another table it just says "null" and breaks – Dennis Ich May 04 '17 at 14:03

Prevent Oracle SQL Developer from truncating CLOBs on export

2 Answers2