How to fetch all rows instead of 500 rows with Toad For Oracle ?

[clementratel]

Hi,

When I run a query, it fetches 500 rows.
If I want to fetch more rows I click on the scroll bar.
I don't like it as with big results it freezes Toad.

How to fetch all rows instead of 500 rows with Toad For Oracle ?

a+, =)
-=Clement=-

Configuration :
Toad For Oracle v9.0.1.8
Windows XP Pro SP3

[John Dorlon]

why do you need to see all of the rows at one time?


From: toad@... [mailto:toad@...] On Behalf Of clementratel
Sent: Tuesday, July 15, 2008 10:36 AM
To: toad@...
Subject: [toad] How to fetch all rows instead of 500 rows with Toad For Oracle ?


Hi,

When I run a query, it fetches 500 rows.
If I want to fetch more rows I click on the scroll bar.
I don't like it as with big results it freezes Toad.

How to fetch all rows instead of 500 rows with Toad For Oracle ?

a+, =)
-=Clement=-

Configuration :
Toad For Oracle v9.0.1.8
Windows XP Pro SP3

[Zarzycki, Mike-2]

I use Ctrl-End to scroll to the end of a resultset after Toad presents
the first 500 rows. If I know going bottom will freeze the current tab,
I open up a new one first so I can continue working in another session
while the other one scrolls bottom. I know it's a little bit of a pain,
but Toad does let you cancel the scroll bottom, and it isn't any
different than what you would face if you were running this in SQL*Plus.
 

Mike Z.

 

________________________________

From: toad@... [mailto:toad@...] On Behalf Of
John Dorlon
Sent: Tuesday, July 15, 2008 1:01 PM
To: 'toad@...'
Subject: RE: [toad] How to fetch all rows instead of 500 rows with Toad
For Oracle ?



why do you need to see all of the rows at one time?  

From: toad@... [mailto:toad@...] On Behalf Of
clementratel
Sent: Tuesday, July 15, 2008 10:36 AM
To: toad@...
Subject: [toad] How to fetch all rows instead of 500 rows with Toad For
Oracle ?

Hi,

When I run a query, it fetches 500 rows.
If I want to fetch more rows I click on the scroll bar.
I don't like it as with big results it freezes Toad.

How to fetch all rows instead of 500 rows with Toad For Oracle ?

a+, =)
-=Clement=-

Configuration :
Toad For Oracle v9.0.1.8
Windows XP Pro SP3

 

[Simoneau, Roger]

Before I give the solution, an adage: Careful what you wish for, you just might get it!
 
In 9.6.1.1:
 
View -> Options -> Schema Browser -> Data and Grids -> Limit Grid Fetch
 
Select the option and change the number 1 to as high as you'd like.  Keep an eye on the "fetches (nnn records)".  In 9.6.1.1 it's a multiple of 500 so if you put in 100000000 (100 million), you'll actually have a negative value.
 
The reason for the adage: If you accidentally code something that does a cartesian join against a rather large table you may find Toad consume all your memory and appear to "freeze".  Toad isn't actually "freezing" when you click on the scroll bar.... it's just responding to your request to get everything from the database based on your query.  It can take a while if you're request is for a rather large dataset.
 
Roger S.
 

-----Original Message-----
From: toad@... [mailto:toad@...]On Behalf Of clementratel
Sent: Tuesday, July 15, 2008 9:36 AM
To: toad@...
Subject: [toad] How to fetch all rows instead of 500 rows with Toad For Oracle ?



Hi,

When I run a query, it fetches 500 rows.
If I want to fetch more rows I click on the scroll bar.
I don't like it as with big results it freezes Toad.

How to fetch all rows instead of 500 rows with Toad For Oracle ?

a+, =)
-=Clement=-

Configuration :
Toad For Oracle v9.0.1.8
Windows XP Pro SP3



 


PLEASE NOTE:
This communication, including any attached documentation,
is intended only for the person or entity to which it is addressed,
and may contain confidential, personal and/or privileged information.
Any unauthorized disclosure, copying, or taking action on the contents
is strictly prohibited. If you have received this message in error,
please contact us immediately so we may correct our records.
Please then delete or destroy the original transmission and any subsequent reply.
Thank you.

[Jeff Smith-7]

Just run your select statement in the Editor with F5, you'll get all your results and you won' t have to muck with the OCI settings for normal queries.

From: toad@... [mailto:toad@...] On Behalf Of Simoneau, Roger
Sent: Tuesday, July 15, 2008 1:12 PM
To: toad@...
Subject: RE: [toad] How to fetch all rows instead of 500 rows with Toad For Oracle ?

Before I give the solution, an adage: Careful what you wish for, you just might get it!

In 9.6.1.1:

View -> Options -> Schema Browser -> Data and Grids -> Limit Grid Fetch

Select the option and change the number 1 to as high as you'd like.  Keep an eye on the "fetches (nnn records)".  In 9.6.1.1 it's a multiple of 500 so if you put in 100000000 (100 million), you'll actually have a negative value.

The reason for the adage: If you accidentally code something that does a cartesian join against a rather large table you may find Toad consume all your memory and appear to "freeze".  Toad isn't actually "freezing" when you click on the scroll bar.... it's just responding to your request to get everything from the database based on your query.  It can take a while if you're request is for a rather large dataset.

Roger S.

-----Original Message-----
From: toad@... [mailto:toad@...]On Behalf Of clementratel
Sent: Tuesday, July 15, 2008 9:36 AM
To: toad@...
Subject: [toad] How to fetch all rows instead of 500 rows with Toad For Oracle ?

Hi,

When I run a query, it fetches 500 rows.
If I want to fetch more rows I click on the scroll bar.
I don't like it as with big results it freezes Toad.

How to fetch all rows instead of 500 rows with Toad For Oracle ?

a+, =)
-=Clement=-

Configuration :
Toad For Oracle v9.0.1.8
Windows XP Pro SP3

PLEASE NOTE:

This communication, including any attached documentation,

is intended only for the person or entity to which it is addressed,

and may contain confidential, personal and/or privileged information.

Any unauthorized disclosure, copying, or taking action on the contents

is strictly prohibited. If you have received this message in error,

please contact us immediately so we may correct our records.

Please then delete or destroy the original transmission and any subsequent reply.

Thank you.



 
 

[clementratel]

Hi,

I need to see all of the rows at one time because I need to check the
times it takes to get them. I use SQL Server Integration Services to
extract all of the rows from an oracle table and it does not manage to
do it, and returns an oracle error message. I would like to see this
error message in toad instead of SSIS as I am much more confident in
Toad's messages.

a+,=)
-=Clement=-

Configuration : Toad For Oracle v9.0.1.8


--- In toad@..., John Dorlon <john.dorlon@...> wrote:
>
> why do you need to see all of the rows at one time?
>
>
> From: toad@... [mailto:toad@...] On Behalf
Of clementratel
> Sent: Tuesday, July 15, 2008 10:36 AM
> To: toad@...
> Subject: [toad] How to fetch all rows instead of 500 rows with Toad
For Oracle ?

>
>
> Hi,
>
> When I run a query, it fetches 500 rows.
> If I want to fetch more rows I click on the scroll bar.
> I don't like it as with big results it freezes Toad.
>
> How to fetch all rows instead of 500 rows with Toad For Oracle ?
>
> a+, =)
> -=Clement=-
>
> Configuration :
> Toad For Oracle v9.0.1.8
> Windows XP Pro SP3
>

[clementratel]

Hi,

Thank you, it's exactly what I was searching for.

a+, =)
-=Clement=-

Configuration :
Toad For Oracle v9.0.1.8
Windows XP SP3 Pro
Firefox v3.0
Intel(R) Core(TM) 2 CPU T5500 @1,66GHz
3,00 GB RAM

--- In toad@..., Jeff Smith <jeff.smith@...> wrote:
>
> Just run your select statement in the Editor with F5, you'll get all
your results and you won' t have to muck with the OCI settings for
normal queries.
>
> From: toad@... [mailto:toad@...] On Behalf
Of Simoneau, Roger
> Sent: Tuesday, July 15, 2008 1:12 PM
> To: toad@...
> Subject: RE: [toad] How to fetch all rows instead of 500 rows with
Toad For Oracle ?
>
> Before I give the solution, an adage: Careful what you wish for, you
just might get it!
>
> In 9.6.1.1:
>
> View -> Options -> Schema Browser -> Data and Grids -> Limit Grid Fetch
>
> Select the option and change the number 1 to as high as you'd like.
 Keep an eye on the "fetches (nnn records)".  In 9.6.1.1 it's a
multiple of 500 so if you put in 100000000 (100 million), you'll
actually have a negative value.
>
> The reason for the adage: If you accidentally code something that
does a cartesian join against a rather large table you may find Toad
consume all your memory and appear to "freeze".  Toad isn't actually
"freezing" when you click on the scroll bar.... it's just responding
to your request to get everything from the database based on your
query.  It can take a while if you're request is for a rather large
dataset.
>
> Roger S.
>
> -----Original Message-----
> From: toad@... [mailto:toad@...]On Behalf Of
clementratel
> Sent: Tuesday, July 15, 2008 9:36 AM
> To: toad@...
> Subject: [toad] How to fetch all rows instead of 500 rows with Toad
For Oracle ?

>
> Hi,
>
> When I run a query, it fetches 500 rows.
> If I want to fetch more rows I click on the scroll bar.
> I don't like it as with big results it freezes Toad.
>
> How to fetch all rows instead of 500 rows with Toad For Oracle ?
>
> a+, =)
> -=Clement=-
>
> Configuration :
> Toad For Oracle v9.0.1.8
> Windows XP Pro SP3
>
> PLEASE NOTE:
>
> This communication, including any attached documentation,
>
> is intended only for the person or entity to which it is addressed,
>
> and may contain confidential, personal and/or privileged information.
>
> Any unauthorized disclosure, copying, or taking action on the contents
>
> is strictly prohibited. If you have received this message in error,
>
> please contact us immediately so we may correct our records.
>
> Please then delete or destroy the original transmission and any

subsequent reply.
>
> Thank you.
>

[John Dorlon]

ok, but keep in mind, the time for Toad to fetch all of the rows may vary from the time it takes SSIS to fetch all the rows.  Fetch time can vary a lot based on OCI Array Buffer Size, and how the client program (Toad vs SSIS) is processing the rows as they come in.



From: toad@... [mailto:toad@...] On Behalf Of clementratel
Sent: Wednesday, July 16, 2008 2:38 AM
To: toad@...
Subject: [toad] Re: How to fetch all rows instead of 500 rows with Toad For Oracle ?


Hi,

I need to see all of the rows at one time because I need to check the
times it takes to get them. I use SQL Server Integration Services to
extract all of the rows from an oracle table and it does not manage to
do it, and returns an oracle error message. I would like to see this
error message in toad instead of SSIS as I am much more confident in
Toad's messages.

a+,=)
-=Clement=-

Configuration : Toad For Oracle v9.0.1.8

--- In toad@...<mailto:toad%40yahoogroups.com>, John Dorlon <john.dorlon@...> wrote:
>
> why do you need to see all of the rows at one time?
>
>
> From: toad@...<mailto:toad%40yahoogroups.com> [mailto:toad@...<mailto:toad%40yahoogroups.com>] On Behalf
Of clementratel
> Sent: Tuesday, July 15, 2008 10:36 AM
> To: toad@...<mailto:toad%40yahoogroups.com>
> Subject: [toad] How to fetch all rows instead of 500 rows with Toad
For Oracle ?

>
>
> Hi,
>
> When I run a query, it fetches 500 rows.
> If I want to fetch more rows I click on the scroll bar.
> I don't like it as with big results it freezes Toad.
>
> How to fetch all rows instead of 500 rows with Toad For Oracle ?
>
> a+, =)
> -=Clement=-
>
> Configuration :
> Toad For Oracle v9.0.1.8
> Windows XP Pro SP3
>

[westn]

Why not just run timed queries using SQL*Plus? That should give you an
baseline estimate of how long the query takes.

 

i.e.

 

set timing on

select * from table where field1=value1;

set timing off

 

 

Nate

 

 

________________________________

From: toad@... [mailto:toad@...] On Behalf Of
clementratel
Sent: Wednesday, July 16, 2008 3:38 AM
To: toad@...
Subject: [toad] Re: How to fetch all rows instead of 500 rows with Toad
For Oracle ?

 

Hi,

I need to see all of the rows at one time because I need to check the
times it takes to get them. I use SQL Server Integration Services to
extract all of the rows from an oracle table and it does not manage to
do it, and returns an oracle error message. I would like to see this
error message in toad instead of SSIS as I am much more confident in
Toad's messages.

a+,=)
-=Clement=-

Configuration : Toad For Oracle v9.0.1.8

--- In toad@... <mailto:toad%40yahoogroups.com> , John
Dorlon <john.dorlon@...> wrote:
>
> why do you need to see all of the rows at one time?
>
>
> From: toad@... <mailto:toad%40yahoogroups.com>
[mailto:toad@... <mailto:toad%40yahoogroups.com> ] On Behalf
Of clementratel
> Sent: Tuesday, July 15, 2008 10:36 AM
> To: toad@... <mailto:toad%40yahoogroups.com>
> Subject: [toad] How to fetch all rows instead of 500 rows with Toad
For Oracle ?

>
>
> Hi,
>
> When I run a query, it fetches 500 rows.
> If I want to fetch more rows I click on the scroll bar.
> I don't like it as with big results it freezes Toad.
>
> How to fetch all rows instead of 500 rows with Toad For Oracle ?
>
> a+, =)
> -=Clement=-
>
> Configuration :
> Toad For Oracle v9.0.1.8
> Windows XP Pro SP3
>

 




This electronic message contains information from American Eagle
Outfitters, Inc. that may be privileged and/or confidential. If you are
not the intended recipient, any disclosure, copy, distribution or use of
the contents of this message is prohibited. If you have received this
email in error, please contact the sender immediately and destroy this
message. Thank you.

[Bert Scalzo]

Add a set term off before and set term off after to eliminate pc scroll and paint time

From: toad@... [mailto:toad@...] On Behalf Of westn@...
Sent: Wednesday, July 16, 2008 8:38 AM
To: toad@...
Subject: RE: [toad] Re: How to fetch all rows instead of 500 rows with Toad For Oracle ?

Why not just run timed queries using SQL*Plus? That should give you an baseline estimate of how long the query takes...

i.e.

set timing on
select * from table where field1=value1;
set timing off


Nate


________________________________
From: toad@... [mailto:toad@...] On Behalf Of clementratel
Sent: Wednesday, July 16, 2008 3:38 AM
To: toad@...
Subject: [toad] Re: How to fetch all rows instead of 500 rows with Toad For Oracle ?


Hi,

I need to see all of the rows at one time because I need to check the
times it takes to get them. I use SQL Server Integration Services to
extract all of the rows from an oracle table and it does not manage to
do it, and returns an oracle error message. I would like to see this
error message in toad instead of SSIS as I am much more confident in
Toad's messages.

a+,=)
-=Clement=-

Configuration : Toad For Oracle v9.0.1.8

--- In toad@...<mailto:toad%40yahoogroups.com>, John Dorlon <john.dorlon@...> wrote:
>
> why do you need to see all of the rows at one time?
>
>
> From: toad@...<mailto:toad%40yahoogroups.com> [mailto:toad@...<mailto:toad%40yahoogroups.com>] On Behalf
Of clementratel
> Sent: Tuesday, July 15, 2008 10:36 AM
> To: toad@...<mailto:toad%40yahoogroups.com>
> Subject: [toad] How to fetch all rows instead of 500 rows with Toad
For Oracle ?

>
>
> Hi,
>
> When I run a query, it fetches 500 rows.
> If I want to fetch more rows I click on the scroll bar.
> I don't like it as with big results it freezes Toad.
>
> How to fetch all rows instead of 500 rows with Toad For Oracle ?
>
> a+, =)
> -=Clement=-
>
> Configuration :
> Toad For Oracle v9.0.1.8
> Windows XP Pro SP3
>

This electronic message contains information from American Eagle Outfitters, Inc. that may be privileged and/or confidential. If you are not the intended recipient, any disclosure, copy, distribution or use of the contents of this message is prohibited. If you have received this email in error, please contact the sender immediately and destroy this message. Thank you.

[John Dorlon]

that's what I'm talking about with processing time....sql plus puts all the data on the screen as text.  It takes a long time to do that.   Toad will pull the rows into a grid much faster than sql plus will scroll them on the screen, and I have no idea how long SSIS it takes SSIS to do whatever it does with them.

From: toad@... [mailto:toad@...] On Behalf Of westn@...
Sent: Wednesday, July 16, 2008 8:38 AM
To: toad@...
Subject: RE: [toad] Re: How to fetch all rows instead of 500 rows with Toad For Oracle ?

Why not just run timed queries using SQL*Plus? That should give you an baseline estimate of how long the query takes...

i.e.

set timing on
select * from table where field1=value1;
set timing off


Nate


________________________________
From: toad@... [mailto:toad@...] On Behalf Of clementratel
Sent: Wednesday, July 16, 2008 3:38 AM
To: toad@...
Subject: [toad] Re: How to fetch all rows instead of 500 rows with Toad For Oracle ?


Hi,

I need to see all of the rows at one time because I need to check the
times it takes to get them. I use SQL Server Integration Services to
extract all of the rows from an oracle table and it does not manage to
do it, and returns an oracle error message. I would like to see this
error message in toad instead of SSIS as I am much more confident in
Toad's messages.

a+,=)
-=Clement=-

Configuration : Toad For Oracle v9.0.1.8

--- In toad@...<mailto:toad%40yahoogroups.com>, John Dorlon <john.dorlon@...> wrote:
>
> why do you need to see all of the rows at one time?
>
>
> From: toad@...<mailto:toad%40yahoogroups.com> [mailto:toad@...<mailto:toad%40yahoogroups.com>] On Behalf
Of clementratel
> Sent: Tuesday, July 15, 2008 10:36 AM
> To: toad@...<mailto:toad%40yahoogroups.com>
> Subject: [toad] How to fetch all rows instead of 500 rows with Toad
For Oracle ?

>
>
> Hi,
>
> When I run a query, it fetches 500 rows.
> If I want to fetch more rows I click on the scroll bar.
> I don't like it as with big results it freezes Toad.
>
> How to fetch all rows instead of 500 rows with Toad For Oracle ?
>
> a+, =)
> -=Clement=-
>
> Configuration :
> Toad For Oracle v9.0.1.8
> Windows XP Pro SP3
>

This electronic message contains information from American Eagle Outfitters, Inc. that may be privileged and/or confidential. If you are not the intended recipient, any disclosure, copy, distribution or use of the contents of this message is prohibited. If you have received this email in error, please contact the sender immediately and destroy this message. Thank you.

[John Dorlon]

ah-hah!

From: toad@... [mailto:toad@...] On Behalf Of Bert Scalzo
Sent: Wednesday, July 16, 2008 8:41 AM
To: toad@...
Subject: RE: [toad] Re: How to fetch all rows instead of 500 rows with Toad For Oracle ?

Add a set term off before and set term off after to eliminate pc scroll and paint time

From: toad@... [mailto:toad@...] On Behalf Of westn@...
Sent: Wednesday, July 16, 2008 8:38 AM
To: toad@...
Subject: RE: [toad] Re: How to fetch all rows instead of 500 rows with Toad For Oracle ?

Why not just run timed queries using SQL*Plus? That should give you an baseline estimate of how long the query takes...

i.e.

set timing on
select * from table where field1=value1;
set timing off


Nate


________________________________
From: toad@... [mailto:toad@...] On Behalf Of clementratel
Sent: Wednesday, July 16, 2008 3:38 AM
To: toad@...
Subject: [toad] Re: How to fetch all rows instead of 500 rows with Toad For Oracle ?


Hi,

I need to see all of the rows at one time because I need to check the
times it takes to get them. I use SQL Server Integration Services to
extract all of the rows from an oracle table and it does not manage to
do it, and returns an oracle error message. I would like to see this
error message in toad instead of SSIS as I am much more confident in
Toad's messages.

a+,=)
-=Clement=-

Configuration : Toad For Oracle v9.0.1.8

--- In toad@...<mailto:toad%40yahoogroups.com>, John Dorlon <john.dorlon@...> wrote:
>
> why do you need to see all of the rows at one time?
>
>
> From: toad@...<mailto:toad%40yahoogroups.com> [mailto:toad@...<mailto:toad%40yahoogroups.com>] On Behalf
Of clementratel
> Sent: Tuesday, July 15, 2008 10:36 AM
> To: toad@...<mailto:toad%40yahoogroups.com>
> Subject: [toad] How to fetch all rows instead of 500 rows with Toad
For Oracle ?

>
>
> Hi,
>
> When I run a query, it fetches 500 rows.
> If I want to fetch more rows I click on the scroll bar.
> I don't like it as with big results it freezes Toad.
>
> How to fetch all rows instead of 500 rows with Toad For Oracle ?
>
> a+, =)
> -=Clement=-
>
> Configuration :
> Toad For Oracle v9.0.1.8
> Windows XP Pro SP3
>

This electronic message contains information from American Eagle Outfitters, Inc. that may be privileged and/or confidential. If you are not the intended recipient, any disclosure, copy, distribution or use of the contents of this message is prohibited. If you have received this email in error, please contact the sender immediately and destroy this message. Thank you.

[Jeff Smith-7]

Do you really want the retrieval time?  You can also query for the amount of time it takes for oracle to prepare the data set for retrieval.  Everything after that can be affected by such things as your TNS settings and network stuff as others have pointed out.

From: toad@... [mailto:toad@...] On Behalf Of John Dorlon
Sent: Wednesday, July 16, 2008 9:42 AM
To: 'toad@...'
Subject: RE: [toad] Re: How to fetch all rows instead of 500 rows with Toad For Oracle ?

that's what I'm talking about with processing time....sql plus puts all the data on the screen as text.  It takes a long time to do that.   Toad will pull the rows into a grid much faster than sql plus will scroll them on the screen, and I have no idea how long SSIS it takes SSIS to do whatever it does with them.

From: toad@... [mailto:toad@...] On Behalf Of westn@...
Sent: Wednesday, July 16, 2008 8:38 AM
To: toad@...
Subject: RE: [toad] Re: How to fetch all rows instead of 500 rows with Toad For Oracle ?

Why not just run timed queries using SQL*Plus? That should give you an baseline estimate of how long the query takes...

i.e.

set timing on
select * from table where field1=value1;
set timing off


Nate


________________________________
From: toad@... [mailto:toad@...] On Behalf Of clementratel
Sent: Wednesday, July 16, 2008 3:38 AM
To: toad@...
Subject: [toad] Re: How to fetch all rows instead of 500 rows with Toad For Oracle ?


Hi,

I need to see all of the rows at one time because I need to check the
times it takes to get them. I use SQL Server Integration Services to
extract all of the rows from an oracle table and it does not manage to
do it, and returns an oracle error message. I would like to see this
error message in toad instead of SSIS as I am much more confident in
Toad's messages.

a+,=)
-=Clement=-

Configuration : Toad For Oracle v9.0.1.8

--- In toad@...<mailto:toad%40yahoogroups.com>, John Dorlon <john.dorlon@...> wrote:
>
> why do you need to see all of the rows at one time?
>
>
> From: toad@...<mailto:toad%40yahoogroups.com> [mailto:toad@...<mailto:toad%40yahoogroups.com>] On Behalf
Of clementratel
> Sent: Tuesday, July 15, 2008 10:36 AM
> To: toad@...<mailto:toad%40yahoogroups.com>
> Subject: [toad] How to fetch all rows instead of 500 rows with Toad
For Oracle ?

>
>
> Hi,
>
> When I run a query, it fetches 500 rows.
> If I want to fetch more rows I click on the scroll bar.
> I don't like it as with big results it freezes Toad.
>
> How to fetch all rows instead of 500 rows with Toad For Oracle ?
>
> a+, =)
> -=Clement=-
>
> Configuration :
> Toad For Oracle v9.0.1.8
> Windows XP Pro SP3
>

This electronic message contains information from American Eagle Outfitters, Inc. that may be privileged and/or confidential. If you are not the intended recipient, any disclosure, copy, distribution or use of the contents of this message is prohibited. If you have received this email in error, please contact the sender immediately and destroy this message. Thank you.