Cannot export SELECT result as expected I noticed this issue when exporting the Oracle SQL execution result from Intellij. 

The database version is: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production.

My SQL is

SELECT TableA.seq||'-'||TableB.id doc_id
FROM schemaA.TableA
JOIN schemaB.TableB
JOIN schemaA.TableC
JOIN schemaA.TableD ON ...


The result of SELECT COUNT(doc_id) is 137272, however, the exported CSV file has less than 137272 (sometimes 137258, not fixed) lines. 

I checked the result and to my surprise found that there are duplicated doc_id which was not expected. 

But if I add ORDER BY doc_id to the SQL statement and re-export the result, everything becomes fine. 

I have also used SQL*PLUS to export to CSV, but encountered a similar situation. 

I thought it might be caused by paging, so I wrote a Java program to get the data. No luck, the output file also has less than 137272 lines, and with some duplicated lines. 

If I change the FetchSize to 1000, sometimes the result is OK. If the fetchsize is 100, it is always wrong. 

try (Connection conn = DriverManager.getConnection(JDBC_CONNECTION,
        JDBC_USER, JDBC_PASSWORD)) {
    try (Statement statement = conn.createStatement(); FileWriter fw = new FileWriter("out.txt")) {
        statement.setFetchSize(100);

        try (ResultSet rs = statement.executeQuery(sql)) {
            while (rs.next()) {
                fw.write(rs.getString(1));
                fw.write("\n");
            }
        } catch (SQLException ex) {
            // not shown in this code snippet
        }
    }
}


I have asked here, and someone suggested to me to ask in stackexchange, so I ask again here.
Can you try PL/SQL? With FOR loops, Oracle performs implicit bulk collects of 100 rows at a time, as per this link.

Make sure you are doing the order by in your select, as you said this gave you the correct results.  I also recommend you to clean your duplicates.

SET SERVEROUTPUT ON
declare 
  cursor c1 is
    select &lt;YOUR SELECT&gt;
begin   
  for c in c1 loop 
    null;
  end loop;  
end;
/