1

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.

0

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 <YOUR SELECT>
begin   
  for c in c1 loop 
    null;
  end loop;  
end;
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.