Oracle SPOOL Not Returning Same Number of Rows

I have a simple SQL query which returns 21 rows of data when I run it in Toad.

However, when I SPOOL the results to a CSV, I always get less than 21 rows of data, e.g. 16 rows one run, then 10 rows the next run.

-- Spool Settings
SET COLSEP ,
SET PAGESIZE 0
SET LINESIZE 1000
SET FEEDBACK OFF
SET HEADING OFF

SPOOL "R:\Level A\Level B\Level C\Level D\spool.csv"

SELECT column_1, 
       column_2, 
       column_3, 
       column_4, 
       NVL(column_5, 'NULL'), 
       NVL(column_6, 'NULL'),
       NVL(column_7, 'NULL'),
       column_8
FROM my_table
ORDER BY column_1, column_2, column_3 DESC;

SPOOL OFF

I'm using COLSEP to get a comma between each column and have used an NVL on the nullable fields to ensure they're always populated with something, but it didn't solve the problem.

I have tried changing the query to a direct SELECT * FROM, but still get the same issue.

I have also tried removing the COLSEP and spooling to different file types - but still didn't solve the issue.

Solution

UnCOMMITted data is only visible within the session that created it (and will ROLLBACK at the end of the session if it has not been COMMITted). If you can't see the data from another session (i.e. in SQL*Plus, if that is what you are using to SPOOL) then make sure you have issued a COMMIT command in the SQL client where you INSERTed the data (i.e. Toad).

Note: even if you connect as the same user, this will create a separate session and you will not be able to see the uncommitted data in the other session.

From the COMMIT documentation:

Until you commit a transaction:

You can see any changes you have made during the transaction by querying the modified tables, but other users cannot see the changes. After you commit the transaction, the changes are visible to other users' statements that execute after the commit.

You can roll back (undo) any changes made during the transaction with the ROLLBACK statement (see ROLLBACK).