SPOOL COMMAND NOT WORKING

Question

I am using the following code to save a csv file. The file is created in the location. However, no data is getting exported. The code is as below.

Drop table Table_Name;
CREATE TABLE Table_Name as 
SELECT 
    Wo_Id,Sum(Time_taken2) as TimeTotal
    from Parent_Table 
    where MAPdata='Mapping Name'
    AND TASKTYPE='Clean and Completed'
group by WO_ID
Order by TimeTotal;
spool "Path\table_name.csv"
SELECT  /*csv*/* from Table_Name;
spool off;
Drop table Table_Name;

What could be the issue. I am using oracle sql developer 4.X version.

File is created in the location and sql script mate. Its only data is not getting exported. — SandyX, Mar 17 '16 at 07:04
Did you try [link](http://stackoverflow.com/questions/26007399/spool-returns-empty-files-when-trying-to-export-from-sql-developer) — dcieslak, Mar 17 '16 at 07:39
Are you sure your table actually contains any records? Do you see anything in the worksheet's script output window when you run it? Do you get any results from running the CTAS query on its own? The filters looks quite specific, but of course we can't see your parent table to see if any matching records exist. — Alex Poole, Mar 17 '16 at 08:47
Yes, There are records when I am running the query. It comes to script output window. This is why I am also perplexed. Not sure if this happens to anyone. As the data is confidential, I cant give the exact column details. However, if the data is coming on the script output window, the spool command should work right. Currently the sheet exported contains two lines "SQL> SELECT /*csv*/* from Table_name; SQL> spool off — SandyX, Mar 17 '16 at 08:57
Works fine for me in 4.1.3. Are you running the whole things as a single script, or selecting each statement and running individually (as statements, or as scripts)? I can see an empty file if I do that; but then running the 'spool off' gets 'not spooling currently'. — Alex Poole, Mar 17 '16 at 09:05
What's your exact SQL Developer version number? As I said it works for me in 4.1.3.20; its feasible an earlier version had a bug, but then upgrading would be your best bet. Also, are you seeing the query statement in the spool file, or is it completely empty? — Alex Poole, Mar 17 '16 at 09:42
4.1.3.20, Buld Main-20.78. Yes, they are empty except the codes mentioned in the previous comments. — SandyX, Mar 17 '16 at 11:07
I missed those in the comment, night be better in the question. The only way I can see to get just those two lines is if I select all the code and use run statement (control-enter) instead of run script (F5). But then the query result would be in the Query Result window, and you said you see it in the Script Output window. — Alex Poole, Mar 17 '16 at 11:24
You have nailed it. This is what I have neglected. My Bad!...You are a star, I truly appreciate your help — SandyX, Mar 17 '16 at 12:09

score 0 · Answer 1 · answered Mar 17 '16 at 11:37

If you have the following lines in your worksheet:

spool "Path\table_name.csv"
SELECT  /*csv*/* from Table_Name;
spool off;

and you select those lines and do 'Run Statement' (control-enter), then the two spool commands work but the output from the select only goes into the Query Result windows; the Script Output window just shows

>Query Run In:Query Result

Your spool file ends up with just:

SQL> SELECT /*csv*/* from Table_name;
SQL> spool off

If you do 'Run Script' (F5) instead, then the output does not go to the Query Result window, and instead is shown in the Script Output window; and is also written to the spool file.

Run Statement initially only does a single fetch of data (as explained here) so it wouldn't make sense to include them in the spool - you would only see the first fetch/page of data. But it's also fetching and displaying the data in a different context to the spool commands.

SPOOL COMMAND NOT WORKING

1 Answers1