When executing scripts in SQL Developer, the amount of output we display on the screen is LIMITED.
By design, we only show you 5,000 records from any query, and we limit the amount of output in total for a single script execution to 10,010 rows.
This is controlled here:
I know what you’re thinking…
Why?
A few reasons, but practicality is what it boils down to. Displaying 3M rows of script output in a GUI is not going to be fun for you or for the application. That’s a lot of machine resources to consume, and it’s more than likely to exhaust the memory allocated to the JVM. Plus, are you really going to sit there and scroll through that many rows of output?
Still, we do offer the preferences, and you’re more than welcome to increase them.
What Happens When we Hit the Limit?
Let’s say we want to build a CSV for excel. We have more than 5,000 records. Let’s just do it anyway.
SET sqlformat csv spool c:\objects_data.csv SELECT * FROM all_objects; spool off
Let’s observe the output.
And the ‘Excel’ file?
A Few Tips for Faster Scripts
Write your script to a .SQL file.
Run it this way.
This will allow the script to run faster, or at least allow SQLDev to handle the script execution as fast as possible, instead of trying to advance the script in the editor and page through the results in the open display panel.
If you want it to run even faster, use SQLcl, and use a shell/batch script to run it…and add this line to your script
SET TERMOUT OFF
23 Comments
Hi sir while trying to export table to excel file can we give cell reference of excel file inside spool?
Sorry, I don’t understand your question.
When trying this approach for a fairly straightforward query, I still get
Only 5,000 rows currently supported in a script results
5,000 rows selected
Is there a way to get all results spooled to the file? And also not echo the results to the Script Output tab?
All results are always spooled to the file – the limit is only for what shows in the script output panel.
If you don’t want to see script results but you do want to spool, you need to use the set termout off
Hi, Jeff!
I’ve read all your posts about spooling and finally made what I wanted – separate spooling for several queries on for-loop. But there is one small problem which ruins everything (for non-latin languages): spooling creates csv-file in UTF-8 encoding without BOM-characters, so when I try to open this csv-file via excel by doubleclicking – Excel doesn’t recogninze it as UTF-8 and all non-latin characters are turning into mess.
Is there any way to enable BOM-character? Or change encoding of spooling?
(I use SQL Developer 18.4.0.376)
Simplified example to reproduce case:
—
spool c:\temp\testfile.csv
SET SQLFORMAT DELIMITED ; ” ”
select ‘Cyrillic column name’ as “Кириллическая шапка”, ‘Кириллица’ as “Cyrillic”
from dual;
spool off;
—
I don’t know what I don’t know here…for example, why does VS Code open the file with no problems? Seems like a problem with Excel to me, as the data appears correctly when looking at it in my bash terminal, SQLDev UI, and various text editors.
Hi Jeff,
What is needed to spool a file directly to a sharepoint location? I presume I have to add code to be able to login to sharepoint?
Query used:
column filename new_val filename
select ‘https://[hidden].sharepoint.com/sites/P2P_Reporting_Analyse/test.csv’ filename from dual;
spool &&filename
set sqlformat csv;
select
*
from
dual
;
spool off
result is:
“FILENAME”
“https://[hidden].sharepoint.com/sites/P2P_Reporting_Analyse/test.csv”
SP2-0556: Invalid file name.
“DUMMY”
“X”
not spooling currently
I am using SQL Developer Version 19.2.1.247, build 247.2212
Can’t spool to a http address. Can you map your SharePoint destination to a network drive?
Is there a reason SET TERM OFF is not supported in the worksheet? I’m a developer who has recently become involved in a lot of query tuning tasks. I would prefer to use a SQL Developer Worksheet for these tasks, but the lack of SET TERM OFF probably means that I need to move over to the command line for certain tasks. Why is running a script in a worksheet different than running a script in SQLCL? Maybe I’m just using the wrong tool for the job and I need to adapt to the command line. Or maybe, there’s a better approach in SQL Developer.
To elaborate, here’s the type of task I’m referring to
set termout off
run big query
set termout on
display cursor infomation/stats of query that was just run
Hello friends,
How do i spool an SQL query output in xlsx format?
I tried the below query and file got generated, but when I open the file, it throws error.
set sqlfomat xlsx
spool C:\Users\velusde\SORR-FDM.xlsx
select * from knta_users;
spool off
We don’t support binaries for spool, best bet is to go with CSV instead.
Jeff, at the moment I just see the following running a script with more than 5000 rows returned in SQLcl 18.4:
SEVERE: java.lang.StringBuffer.charAt(Unknown Source)
java.lang.StringIndexOutOfBoundsException: String index out of range: 8
at java.lang.StringBuffer.charAt(Unknown Source)
Hmmm, this is what I just tried
I got 10,000 rows back in a few seconds, also using SET SQLFORMAT ansiconsole.
Hi Jeff,
I am spooling a query which gives as a result more then 300K rows. Please see the comand below
SET sqlformat csv
spool C:\Users\ecm0354c\Downloads\roamers.csv
select * from aeropuerto where substr(MSISDN,1,2) not in (’57’);
spool off
However, when I check the file generated it only has the following information:
SQL> select * from aeropuerto where substr(MSISDN,1,2) not in (’57’)
SQL> spool off
Also, in the scriptoutput section only shows Query Run In:Query results. Does not show task results.
Do you know why? What I am doing wrong?
Thanks
SQLcl or SQL Developer?
And the Version # for what you’re using?
Does it work for ANY query – or is it just not working for this one?
Hi Jeff,
This feature is terrific for running existing sqlplus script and get nicer output.
I came across 2 “issues” and hope to get your input:
1. If I don’t specify the path for the spool file, where is the default location it is saved to?
2. The html spool file produced has black headings by default. How can I change black to a more gentle color?
Thank you!
Vanha
a temp directory, which you can see if you run ‘show sqlpath’ or if you try to open a file in the worksheet – the starting directory
Hey Jeff,
SQL Developer Ver. 4.1.4.21.21
And it does not work for any query.
Thanks for your help
4.1 is 3+ years old, AND the scripting logic changed completely from version 4.1 to version 4.2 – can you upgrade to latest (18.4)?
You’re very welcome!
Hi.
I have a question related to other “output area”. I came from PLSQL Developer and in that product when I run code using functions of HTP package “magically” a new tab for HTP output is shown.
How can I do this magic in SQLDeveloper? Where can I see HTP output?
Regards
View > OWA Output
Thank you!
You are the best.