get this error when running multiple rows in one shot ..never had this issue in Toad for DB2
select * from PS_PW_S33_RLBK_LN A where PAYGROUP = 'TX1' and PAGE_NUM = 732 and LINE_NUM = 48 and ERNCD = '001' and ADDL_NBR = 0;
select * from PS_PW_S33_RLBK_LN A where PAYGROUP = 'TR2' and PAGE_NUM = 167 and LINE_NUM = 39 and ERNCD = '002' and ADDL_NBR = 0;
In Toad for Oracle, the Execute/Compile Statement (F9) command runs a single statement, only. When there is no selection it executes the statement at caret. When you make a selection it will send the selected text verbatim. If you selected both of those statements and use F9 then the error is expected.
You can use Execute Script (F5) to run more than one statement at a time. There are some differences between the two execution methods. F5 will retrieve all records and display them. If the result set is large this will use a lot of memory. F9 retrieves records in batches which is better for memory consumption unless you scroll to the last record.
I forgot about a Pin Grid feature John added recently. After using F9 to run a single statement, you can pin its result set and then run another query using F9. This will allow the result sets of both queries to be present at the same time.
thanks ... it works ... also currently it Toad for Oracle only retrieves the 1st 500 and I can't find in the setting where I can set it to retrieve ... any clue how this can be done? I was able to do it in DB2
The default setting is "automatic" and chooses a good size to show results without excessive memory consumption. You can always scroll to the end of the grid and the next batch of results will be retrieved. You can also use the Ctrl+End shortcut to fetch all records. If you want to set a manual limit you can do so in Options here.
I highly recommend you leave that setting on Automatic. As is, Toad chooses a good fetch size (optimized for both speed and memory consumption) for each query, individually, that you run in the Editor. If you set it to manual, you're telling Toad to do the same thing for every query.
And there is no setting to fetch all rows immediately anyway (but, as Michael pointed out, you can do that with CTRL+END)
More rows are fetched in batches of 500 (or whatever Toad decides the optimal fetch size is) as needed (that is, as you scroll down)
And..."Manual" is broken in Toad version 24.2. It continues to behave as automatic even if you change the setting to manual. This is fixed in beta.
