Toad usually only shows the first 500 records. For some reason the query I just ran is trying to show more than the first 500. Any idea why? How do I make toad return to its default behavior of showing only the first 500?
How many is it selecting? All of them, or just a little more, like 1000? If 1000, then it's just Toad adjusting to the record size (Small records fetch more than 500, large records less). This is expected behavior. It would fetch fewer than 500 if you had a bunch of VARCHAR(4000) columns, for example..
If it's fetching all records, did you hit F5 instead of F9? F5 fetches all.
All of them. I clicked the button on my toolbar.
Hover your mouse over the button you clicked. If it has F5 for a shortcut, that button fetches all. Look for the one with the F9 shortcut. That one fetches 500 at a time. Get in the habit of using the F9 button. The only time you want to use the F5 one is when you are running a script with lots of SQL, and you want to run all of them.
See this post for details on the differences between the two.
Also, F5 outputs to the Script Output tab, but F9 outputs to the data grid.
Cheers,
Russ
I definitely did the f9 one. I had a data grid. I was watching the data grip populate.
Maybe you did a CTRL+END? That's the only way I know of to fetch all the rows after a select with F9.
Or maybe (less likely) you went to options -> Oracle -> General, set "OCI Array Buffer Size" to manual, and put in some large number for "Size".
If you can get a screen shot of all the rows coming in, I can maybe tell you more.
If Auto Trace is enabled before running the query with F9 it will fetch all records, too. Auto Trace is available on the Editor’s right-click menu.
Ah! Good call, Mike. That's probably it.
Is the end button anywhere else on the keyboard besides top right? I was thinking it was somewhere close to the ctrl button. I do not see it anywhere else on my keyboard.
I just checked this. It is set to automatic.
options -> Oracle -> General, set "OCI Array Buffer Size"
Can you provide a screenshot of where auto trace is? I do not see it.
For me END is in the top right, but keyboards are not all identical.
Auto-Trace is here. The tab may say "Auto Trace" and not "Trace" before you click on it.
If the tab is not visible, you can make it visible by right-clicking here and then checking Trace/Auto Trace
Auto trace is not enabled.
Pivot Grid mode will also cause all rows to be fetched.
So will a filter.
The filter should clear when you run another query though.
If none of these suggestions solve the problem, you can email me your user files folder and I will try it with your settings. Details on where that folder is, as well as my email address are in this post.
-John
Everything looks good. I have never experienced it before except on that one table. Thank you.
Not sure how or why it happened. It just randomly happened on just one table. I just put this at the end of my query to make it stop.
AND ROWNUM <= 1000