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?

Oestrin

@sstan Thanks, the question you pointed to does it require to use Oracle SQL Developer for the export, unlike mine. – Oestrin

Thanks for the comment. Agreed. – Statocyst

I don't think you can use SQL Developer to export >4k clobs. Jeff Smith 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. – Feeling

I'm rather tempted to suggest moving the Python script to an answer as a potential work around. ;) – Londalondon

@Londalondon sure, done – Oestrin

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
    '''
    # https://mcmap.net/q/383152/-cx_oracle-doesn-39-t-connect-when-using-sid-instead-of-service-name-on-connection-string
    # 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

Oestrin

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

Ketch

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 – Masqat