Not sure, should work, the last bug regarding LONG column queries getting a closed stream was fixed for 3.2:
Bug 13834339 - REGRESSION: SQL ERROR: STREAM HAS ALREADY BEEN CLOSED
Can you provide specific information about your environment?
SQL Developer Team
Additional tips for running a SQL query which includes a column of data_type 'LONG'
0. Results may depend on your JDBC driver version
1. Run Statement (Ctrl+Enter) seems to work consistently better than Run Script (F5)
2. Try the query without other columns -- include only the <LONG_column_name>
3. If other columns must be included and are of data_type 'DATE', try wrapping in TO_CHAR
5. Try the query with a predicate to exclude null values, e.g., where <LONG_column_name> is not null
The sample query I tested with:
where data_default is not null;
The common theme in most of these tips: improve the chance of processing a stream that reads the LONG column part of the result set, then closes it, before reading other columns in the result set. Limiting both the number of rows and columns in the result set generally helps SQL Developer do that.
I would like to know if there's a possibility to query it successfully using 'SELECT *'. SQL Dev 3.2.2 does that successfully.
Thanks for clarifying. In the notes for the referenced bug above, the developer never claimed the fix covered all cases. The test case I provided, however, does work in 3.2.2 for 'SELECT *'. So, while the 'tips' may provide a partial workaround, I have logged a new bug against 4.0:
Bug 17621277 - REGRESSION: SQL ERROR: STREAM HAS ALREADY BEEN CLOSED: LONG COLS
Hopefully the behavior can be improved to at least match that of 3.2.2.
The developer investigating bug 17621277 has proposed the following workaround.
Add the following line in sqldeveloper.conf file:
According to the Oracle documentation
THIS IS A THIN ONLY PROPERTY. IT SHOULD NOT BE USED WITH ANY OTHER DRIVERS.
If set to "true", the performance when retrieving data in a 'SELECT' will be improved but the default behavior for handling LONG columns will be changed to fetch multiple rows (prefetch size). It means that enough memory will be allocated to read this data. So if you want to use this property, make sure that the LONG columns you are retrieving are not too big or you may run out of memory.
So, in a SQL Developer context, this means you may not check off Tools > Preferences > Database > Advanced > Use OCI/Thick driver. If memory utilization becomes an issue, you can try
1, Increasing the AddVMOption -Xmx size
2. Decreasing Tools > Preferences > Database > Advanced > Sql Array Fetch Size
Hope this helps,