How can I retrieve set by set of records from a table??

[doe]

Deal all..

I have 1000 records in a table.
How can I retrieve 10 records at a time by Select statement? or any other
way? Each time I retrieve, the 10 records must not be previously obtained.

thanks always!

[Angel]

"doe" <doekalay@yahoo.com> wrote:
>
>Deal all..
>
>I have 1000 records in a table.
>How can I retrieve 10 records at a time by Select statement? or any other
>way? Each time I retrieve, the 10 records must not be previously obtained.
>
>thanks always!
You can do a normal select,using a for loop to retrive the first 10 records.
At the end of the for loop, set the pointer to point to the 11th record and
so on...

[doe]

Angel:

The thing is I do not want to bring all the records and
pickup 10 after another in the program.

Whenever I retrieve from the database, I want to get 10 records at
a time.

I think the one you suggested is I need to load all the records from
the dbase and do a for loop with a program. That I do not want.

thanks



"Angel" <rennaisance@wildmail.com> wrote:
>
>"doe" <doekalay@yahoo.com> wrote:
>>
>>Deal all..
>>
>>I have 1000 records in a table.
>>How can I retrieve 10 records at a time by Select statement? or any other
>>way? Each time I retrieve, the 10 records must not be previously obtained.
>>
>>thanks always!
>You can do a normal select,using a for loop to retrive the first 10 records.
>At the end of the for loop, set the pointer to point to the 11th record
and
>so on...
>

[Angel]

Hi Doe,
you can do this
(Assuming ur table name is MyTable and Column name is MyColumn)

select rownum,max(MyColumn) from MyTable
group by rownum
having rownum>=1 and rownum<=10

That will solve ur problem
"doe" <doekalay@yahoo.com> wrote:
>
>Angel:
>
>The thing is I do not want to bring all the records and
>pickup 10 after another in the program.
>
>Whenever I retrieve from the database, I want to get 10 records at
>a time.
>
>I think the one you suggested is I need to load all the records from
>the dbase and do a for loop with a program. That I do not want.
>
>thanks
>
>
>
>"Angel" <rennaisance@wildmail.com> wrote:
>>
>>"doe" <doekalay@yahoo.com> wrote:
>>>
>>>Deal all..
>>>
>>>I have 1000 records in a table.
>>>How can I retrieve 10 records at a time by Select statement? or any other
>>>way? Each time I retrieve, the 10 records must not be previously obtained.
>>>
>>>thanks always!
>>You can do a normal select,using a for loop to retrive the first 10 records.
>>At the end of the for loop, set the pointer to point to the 11th record
>and
>>so on...
>>
>

[doe]

Angel:

You can get the first 10 records by the way you mentioned.
However, I don't think you can get it from rownum 11 to 20 and so on.

thanks

"Angel" <rennaisance@wildmail.com> wrote:
>
>Hi Doe,
>you can do this
>(Assuming ur table name is MyTable and Column name is MyColumn)
>
>select rownum,max(MyColumn) from MyTable
>group by rownum
>having rownum>=1 and rownum<=10
>
>That will solve ur problem
>"doe" <doekalay@yahoo.com> wrote:
>>
>>Angel:
>>
>>The thing is I do not want to bring all the records and
>>pickup 10 after another in the program.
>>
>>Whenever I retrieve from the database, I want to get 10 records at
>>a time.
>>
>>I think the one you suggested is I need to load all the records from
>>the dbase and do a for loop with a program. That I do not want.
>>
>>thanks
>>
>>
>>
>>"Angel" <rennaisance@wildmail.com> wrote:
>>>
>>>"doe" <doekalay@yahoo.com> wrote:
>>>>
>>>>Deal all..
>>>>
>>>>I have 1000 records in a table.
>>>>How can I retrieve 10 records at a time by Select statement? or any other
>>>>way? Each time I retrieve, the 10 records must not be previously obtained.
>>>>
>>>>thanks always!
>>>You can do a normal select,using a for loop to retrive the first 10 records.
>>>At the end of the for loop, set the pointer to point to the 11th record
>>and
>>>so on...
>>>
>>
>

[Angel]

Oops, I forgot to state that the you CAN CHANGE the figures according to your
needs. The '1' and '10' are numeric variable you can play around.
If you couldn't retrive the rownum 11 and 20, it'll means that you don't
have enough records to test the sql. Try adding 50 MORE records and play
around with it..............

"doe" <doekalay@yahoo.com> wrote:
>
>Angel:
>
>You can get the first 10 records by the way you mentioned.
>However, I don't think you can get it from rownum 11 to 20 and so on.
>
>thanks
>
>"Angel" <rennaisance@wildmail.com> wrote:
>>
>>Hi Doe,
>>you can do this
>>(Assuming ur table name is MyTable and Column name is MyColumn)
>>
>>select rownum,max(MyColumn) from MyTable
>>group by rownum
>>having rownum>=1 and rownum<=10
>>
>>That will solve ur problem
>>"doe" <doekalay@yahoo.com> wrote:
>>>
>>>Angel:
>>>
>>>The thing is I do not want to bring all the records and
>>>pickup 10 after another in the program.
>>>
>>>Whenever I retrieve from the database, I want to get 10 records at
>>>a time.
>>>
>>>I think the one you suggested is I need to load all the records from
>>>the dbase and do a for loop with a program. That I do not want.
>>>
>>>thanks
>>>
>>>
>>>
>>>"Angel" <rennaisance@wildmail.com> wrote:
>>>>
>>>>"doe" <doekalay@yahoo.com> wrote:
>>>>>
>>>>>Deal all..
>>>>>
>>>>>I have 1000 records in a table.
>>>>>How can I retrieve 10 records at a time by Select statement? or any
other
>>>>>way? Each time I retrieve, the 10 records must not be previously obtained.
>>>>>
>>>>>thanks always!
>>>>You can do a normal select,using a for loop to retrive the first 10 records.
>>>>At the end of the for loop, set the pointer to point to the 11th record
>>>and
>>>>so on...
>>>>
>>>
>>
>

[doe]

Thanks a lot! Angel.


"Angel" <rennaisance@wildmail.com> wrote:
>
>Oops, I forgot to state that the you CAN CHANGE the figures according to
your
>needs. The '1' and '10' are numeric variable you can play around.
>If you couldn't retrive the rownum 11 and 20, it'll means that you don't
>have enough records to test the sql. Try adding 50 MORE records and play
>around with it..............
>
>"doe" <doekalay@yahoo.com> wrote:
>>
>>Angel:
>>
>>You can get the first 10 records by the way you mentioned.
>>However, I don't think you can get it from rownum 11 to 20 and so on.
>>
>>thanks
>>
>>"Angel" <rennaisance@wildmail.com> wrote:
>>>
>>>Hi Doe,
>>>you can do this
>>>(Assuming ur table name is MyTable and Column name is MyColumn)
>>>
>>>select rownum,max(MyColumn) from MyTable
>>>group by rownum
>>>having rownum>=1 and rownum<=10
>>>
>>>That will solve ur problem
>>>"doe" <doekalay@yahoo.com> wrote:
>>>>
>>>>Angel:
>>>>
>>>>The thing is I do not want to bring all the records and
>>>>pickup 10 after another in the program.
>>>>
>>>>Whenever I retrieve from the database, I want to get 10 records at
>>>>a time.
>>>>
>>>>I think the one you suggested is I need to load all the records from
>>>>the dbase and do a for loop with a program. That I do not want.
>>>>
>>>>thanks
>>>>
>>>>
>>>>
>>>>"Angel" <rennaisance@wildmail.com> wrote:
>>>>>
>>>>>"doe" <doekalay@yahoo.com> wrote:
>>>>>>
>>>>>>Deal all..
>>>>>>
>>>>>>I have 1000 records in a table.
>>>>>>How can I retrieve 10 records at a time by Select statement? or any
>other
>>>>>>way? Each time I retrieve, the 10 records must not be previously obtained.
>>>>>>
>>>>>>thanks always!
>>>>>You can do a normal select,using a for loop to retrive the first 10
records.
>>>>>At the end of the for loop, set the pointer to point to the 11th record
>>>>and
>>>>>so on...
>>>>>
>>>>
>>>
>>
>

[Boris Milrud]

Angel,

The solution you provided is inefficient, but most important it is wrong.
Here is why: in your SQL statement you don't specify an order in which database
retrieves rows while you assume that rows' sequence stays the same. Oracle
cannot guarantee that without ORDER BY clause it will retrieve rows in the
same sequence, which means that every time you execute your SQL you may get
rows in different order and you don't get next 10 rows.

In addition, it does full table scan anyway first, then it does SORT GROUP
BY, then filtering. And it will do the same steps every SQL execution! If
you have 1000 rows in the table, that may not be an issue, however I tested
it on the table with 100,000 rows and it took 12 - 14 seconds on average
to retrieve just 10 (!) rows.

I think, the best way to do is via combination of 3 PL/SQL procedures and
cursor as a part of the package.

First two procedures, OpenCursor and CloseCursor, will take care of opening
and closing of the cursor.
Third procedure RetrieveRows will fetch N (10) rows at a time using BULK
COLLECT clause with LIMIT keyword, assuming you are running Oracle 8i database.
So, every time you call it you get next N rows.

I think that's an accurate and efficient solution. I created a package and
tested it: for the same 100,000 rows table it took 0.2 sec. to open the cursor
and steady 0.15 sec. to fetch every batch of next 10 rows.

Hope it helps.
Boris Milrud.



"Angel" <rennaisance@wildmail.com> wrote:
>
>Hi Doe,
>you can do this
>(Assuming ur table name is MyTable and Column name is MyColumn)
>
>select rownum,max(MyColumn) from MyTable
>group by rownum
>having rownum>=1 and rownum<=10
>
>That will solve ur problem
>"doe" <doekalay@yahoo.com> wrote:
>>
>>Angel:
>>
>>The thing is I do not want to bring all the records and
>>pickup 10 after another in the program.
>>
>>Whenever I retrieve from the database, I want to get 10 records at
>>a time.
>>
>>I think the one you suggested is I need to load all the records from
>>the dbase and do a for loop with a program. That I do not want.
>>
>>thanks
>>
>>
>>
>>"Angel" <rennaisance@wildmail.com> wrote:
>>>
>>>"doe" <doekalay@yahoo.com> wrote:
>>>>
>>>>Deal all..
>>>>
>>>>I have 1000 records in a table.
>>>>How can I retrieve 10 records at a time by Select statement? or any other
>>>>way? Each time I retrieve, the 10 records must not be previously obtained.
>>>>
>>>>thanks always!
>>>You can do a normal select,using a for loop to retrive the first 10 records.
>>>At the end of the for loop, set the pointer to point to the 11th record
>>and
>>>so on...
>>>
>>
>

[doe]

Boris:

I have been trying to implement your idea and got struck with Cursor. The
example code I am using is as follows:. The problem is I am getting only
the first 10 records even if I run second or moreTH times. What I want to
achieve is each time I run, I want a different 10 records. I think I need
to set the cursor somehow. I would appreciate if you can throw some light
here again.

thanks always
doe


1 DECLARE
2 TYPE nameTab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
3 CURSOR c1 IS SELECT name from s_org_ext where row_id between '00'
and '10';
4 empname nameTab;
5 rows NATURAL :=10;
6 BEGIN
7 OPEN c1;
8 FETCH c1 BULK COLLECT INTO empname LIMIT rows;
9 CLOSE c1;
20* END;



"Boris Milrud" <milrud@hotmail.com> wrote:
>
>Angel,
>
>The solution you provided is inefficient, but most important it is wrong.
>Here is why: in your SQL statement you don't specify an order in which database
>retrieves rows while you assume that rows' sequence stays the same. Oracle
>cannot guarantee that without ORDER BY clause it will retrieve rows in the
>same sequence, which means that every time you execute your SQL you may
get
>rows in different order and you don't get next 10 rows.
>
>In addition, it does full table scan anyway first, then it does SORT GROUP
>BY, then filtering. And it will do the same steps every SQL execution! If
>you have 1000 rows in the table, that may not be an issue, however I tested
>it on the table with 100,000 rows and it took 12 - 14 seconds on average
>to retrieve just 10 (!) rows.
>
>I think, the best way to do is via combination of 3 PL/SQL procedures and
>cursor as a part of the package.
>
>First two procedures, OpenCursor and CloseCursor, will take care of opening
>and closing of the cursor.
>Third procedure RetrieveRows will fetch N (10) rows at a time using BULK
>COLLECT clause with LIMIT keyword, assuming you are running Oracle 8i database.
>So, every time you call it you get next N rows.
>
>I think that's an accurate and efficient solution. I created a package and
>tested it: for the same 100,000 rows table it took 0.2 sec. to open the
cursor
>and steady 0.15 sec. to fetch every batch of next 10 rows.
>
>Hope it helps.
>Boris Milrud.
>
>
>
>"Angel" <rennaisance@wildmail.com> wrote:
>>
>>Hi Doe,
>>you can do this
>>(Assuming ur table name is MyTable and Column name is MyColumn)
>>
>>select rownum,max(MyColumn) from MyTable
>>group by rownum
>>having rownum>=1 and rownum<=10
>>
>>That will solve ur problem
>>"doe" <doekalay@yahoo.com> wrote:
>>>
>>>Angel:
>>>
>>>The thing is I do not want to bring all the records and
>>>pickup 10 after another in the program.
>>>
>>>Whenever I retrieve from the database, I want to get 10 records at
>>>a time.
>>>
>>>I think the one you suggested is I need to load all the records from
>>>the dbase and do a for loop with a program. That I do not want.
>>>
>>>thanks
>>>
>>>
>>>
>>>"Angel" <rennaisance@wildmail.com> wrote:
>>>>
>>>>"doe" <doekalay@yahoo.com> wrote:
>>>>>
>>>>>Deal all..
>>>>>
>>>>>I have 1000 records in a table.
>>>>>How can I retrieve 10 records at a time by Select statement? or any
other
>>>>>way? Each time I retrieve, the 10 records must not be previously obtained.
>>>>>
>>>>>thanks always!
>>>>You can do a normal select,using a for loop to retrive the first 10 records.
>>>>At the end of the for loop, set the pointer to point to the 11th record
>>>and
>>>>so on...
>>>>
>>>
>>
>

[Boris Milrud]

Doe,

The source of your problem is that you did not set up your procedure properly:
you need to use the package! Let me repeat the solution's description I put
in my first email:

"I think, the best way to do is via combination of 3 PL/SQL procedures and
cursor as a part of the package. First two procedures, OpenCursor and CloseCursor,
will take care of opening and closing of the cursor. Third procedure RetrieveRows
will fetch N (10) rows at a time using BULK COLLECT clause with LIMIT keyword,
assuming you are running Oracle 8i database. So, every time you call it you
get next N rows."

Let me go further now and give you the sample code for this:

******************************************************
create or replace package NRows as
type tName is record (OrgName s_org_ext.name%type);

cursor csrNames return tName;

procedure OpenCursor;

procedure CloseCursor;

procedure RetrieveRows(pRows pls_integer := 10);

end NRows;
/

create or replace package body NRows as
type tOrgName is
table of s_org_ext.name%type
index by binary_integer;

vOrgName tOrgName;

cursor csrNames return tName is
select name
from s_org_ext;

procedure OpenCursor is
begin
if not csrNames%isopen
then
open csrNames;
dbms_output.put_line('Opening the cursor.');
end if;
end OpenCursor;

procedure CloseCursor is
begin
if csrNames%isopen
then
close csrNames;
dbms_output.put_line('Closing the cursor.');
end if;
end CloseCursor;

procedure RetrieveRows (pRows pls_integer := 10) is
begin

/* Open the cursor */
if not csrNames%isopen
then
OpenCursor;
end if;

fetch csrNames bulk collect into vOrgName limit pRows;

if (vOrgName.count > 0)
then
for nIndex in 1..vOrgName.count
loop
dbms_output.put_line(vOrgName(nIndex));
end loop;
end if;


/* End of the cursor: close it. */
if csrNames%notfound
then
CloseCursor;
end if;

end RetrieveRows;

end NRows;
/
******************************************************

Run (and, possibly, debug) the code above to create NRows package. Now, you
could use it to get next N rows,i.e.:

begin
/* Retrieve first 10 rows */
Nrows.RetrieveRows(10);
end;
/

begin
/* Retrieve next 20 rows */
Nrows.RetrieveRows(20);
end;
/

begin
/* Retrieve next 10 rows */
Nrows.RetrieveRows;
end;
/

Let me know if it works for you. I tested with my table and it did.

Boris.


"doe" <doekalay@yahoo.com> wrote:
>
>Boris:
>
>I have been trying to implement your idea and got struck with Cursor. The
>example code I am using is as follows:. The problem is I am getting only
>the first 10 records even if I run second or moreTH times. What I want to
>achieve is each time I run, I want a different 10 records. I think I need
>to set the cursor somehow. I would appreciate if you can throw some light
>here again.
>
>thanks always
>doe
>
>
> 1 DECLARE
> 2 TYPE nameTab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
> 3 CURSOR c1 IS SELECT name from s_org_ext where row_id between '00'
>and '10';
> 4 empname nameTab;
> 5 rows NATURAL :=10;
> 6 BEGIN
> 7 OPEN c1;
> 8 FETCH c1 BULK COLLECT INTO empname LIMIT rows;
> 9 CLOSE c1;
> 20* END;
>
>
>
>"Boris Milrud" <milrud@hotmail.com> wrote:
>>
>>Angel,
>>
>>The solution you provided is inefficient, but most important it is wrong.
>>Here is why: in your SQL statement you don't specify an order in which
database
>>retrieves rows while you assume that rows' sequence stays the same. Oracle
>>cannot guarantee that without ORDER BY clause it will retrieve rows in
the
>>same sequence, which means that every time you execute your SQL you may
>get
>>rows in different order and you don't get next 10 rows.
>>
>>In addition, it does full table scan anyway first, then it does SORT GROUP
>>BY, then filtering. And it will do the same steps every SQL execution!
If
>>you have 1000 rows in the table, that may not be an issue, however I tested
>>it on the table with 100,000 rows and it took 12 - 14 seconds on average
>>to retrieve just 10 (!) rows.
>>
>>I think, the best way to do is via combination of 3 PL/SQL procedures and
>>cursor as a part of the package.
>>
>>First two procedures, OpenCursor and CloseCursor, will take care of opening
>>and closing of the cursor.
>>Third procedure RetrieveRows will fetch N (10) rows at a time using BULK
>>COLLECT clause with LIMIT keyword, assuming you are running Oracle 8i database.
>>So, every time you call it you get next N rows.
>>
>>I think that's an accurate and efficient solution. I created a package
and
>>tested it: for the same 100,000 rows table it took 0.2 sec. to open the
>cursor
>>and steady 0.15 sec. to fetch every batch of next 10 rows.
>>
>>Hope it helps.
>>Boris Milrud.
>>
>>
>>
>>"Angel" <rennaisance@wildmail.com> wrote:
>>>
>>>Hi Doe,
>>>you can do this
>>>(Assuming ur table name is MyTable and Column name is MyColumn)
>>>
>>>select rownum,max(MyColumn) from MyTable
>>>group by rownum
>>>having rownum>=1 and rownum<=10
>>>
>>>That will solve ur problem
>>>"doe" <doekalay@yahoo.com> wrote:
>>>>
>>>>Angel:
>>>>
>>>>The thing is I do not want to bring all the records and
>>>>pickup 10 after another in the program.
>>>>
>>>>Whenever I retrieve from the database, I want to get 10 records at
>>>>a time.
>>>>
>>>>I think the one you suggested is I need to load all the records from
>>>>the dbase and do a for loop with a program. That I do not want.
>>>>
>>>>thanks
>>>>
>>>>
>>>>
>>>>"Angel" <rennaisance@wildmail.com> wrote:
>>>>>
>>>>>"doe" <doekalay@yahoo.com> wrote:
>>>>>>
>>>>>>Deal all..
>>>>>>
>>>>>>I have 1000 records in a table.
>>>>>>How can I retrieve 10 records at a time by Select statement? or any
>other
>>>>>>way? Each time I retrieve, the 10 records must not be previously obtained.
>>>>>>
>>>>>>thanks always!
>>>>>You can do a normal select,using a for loop to retrive the first 10
records.
>>>>>At the end of the for loop, set the pointer to point to the 11th record
>>>>and
>>>>>so on...
>>>>>
>>>>
>>>
>>
>

[NJMrBig]

Boris,

This conversation thread has sparked my curiosity. Will this solution work
on a per user basis? Scenario: user 1 gets first 10 rows, then gets next
10 rows, user 2 logs in gets 10 rows-which 10 is it?

-Big

"Boris Milrud" <milrud@hotmail.com> wrote:
>
>Doe,
>
>The source of your problem is that you did not set up your procedure properly:
>you need to use the package! Let me repeat the solution's description I
put
>in my first email:
>
>"I think, the best way to do is via combination of 3 PL/SQL procedures and
>cursor as a part of the package. First two procedures, OpenCursor and CloseCursor,
>will take care of opening and closing of the cursor. Third procedure RetrieveRows
>will fetch N (10) rows at a time using BULK COLLECT clause with LIMIT keyword,
>assuming you are running Oracle 8i database. So, every time you call it
you
>get next N rows."
>
>Let me go further now and give you the sample code for this:
>
>******************************************************
>create or replace package NRows as
> type tName is record (OrgName s_org_ext.name%type);
>
> cursor csrNames return tName;
>
> procedure OpenCursor;
>
> procedure CloseCursor;
>
> procedure RetrieveRows(pRows pls_integer := 10);
>
>end NRows;
>/
>
>create or replace package body NRows as
> type tOrgName is
> table of s_org_ext.name%type
> index by binary_integer;
>
> vOrgName tOrgName;
>
> cursor csrNames return tName is
> select name
> from s_org_ext;
>
> procedure OpenCursor is
> begin
> if not csrNames%isopen
> then
> open csrNames;
> dbms_output.put_line('Opening the cursor.');
> end if;
> end OpenCursor;
>
> procedure CloseCursor is
> begin
> if csrNames%isopen
> then
> close csrNames;
> dbms_output.put_line('Closing the cursor.');
> end if;
> end CloseCursor;
>
> procedure RetrieveRows (pRows pls_integer := 10) is
> begin
>
> /* Open the cursor */
> if not csrNames%isopen
> then
> OpenCursor;
> end if;
>
> fetch csrNames bulk collect into vOrgName limit pRows;
>
> if (vOrgName.count > 0)
> then
> for nIndex in 1..vOrgName.count
> loop
> dbms_output.put_line(vOrgName(nIndex));
> end loop;
> end if;
>
>
> /* End of the cursor: close it. */
> if csrNames%notfound
> then
> CloseCursor;
> end if;
>
> end RetrieveRows;
>
>end NRows;
>/
>******************************************************
>
>Run (and, possibly, debug) the code above to create NRows package. Now,
you
>could use it to get next N rows,i.e.:
>
>begin
> /* Retrieve first 10 rows */
> Nrows.RetrieveRows(10);
>end;
>/
>
>begin
> /* Retrieve next 20 rows */
> Nrows.RetrieveRows(20);
>end;
>/
>
>begin
> /* Retrieve next 10 rows */
> Nrows.RetrieveRows;
>end;
>/
>
>Let me know if it works for you. I tested with my table and it did.
>
>Boris.
>
>
>"doe" <doekalay@yahoo.com> wrote:
>>
>>Boris:
>>
>>I have been trying to implement your idea and got struck with Cursor. The
>>example code I am using is as follows:. The problem is I am getting only
>>the first 10 records even if I run second or moreTH times. What I want
to
>>achieve is each time I run, I want a different 10 records. I think I need
>>to set the cursor somehow. I would appreciate if you can throw some light
>>here again.
>>
>>thanks always
>>doe
>>
>>
>> 1 DECLARE
>> 2 TYPE nameTab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
>> 3 CURSOR c1 IS SELECT name from s_org_ext where row_id between '00'
>>and '10';
>> 4 empname nameTab;
>> 5 rows NATURAL :=10;
>> 6 BEGIN
>> 7 OPEN c1;
>> 8 FETCH c1 BULK COLLECT INTO empname LIMIT rows;
>> 9 CLOSE c1;
>> 20* END;
>>
>>
>>
>>"Boris Milrud" <milrud@hotmail.com> wrote:
>>>
>>>Angel,
>>>
>>>The solution you provided is inefficient, but most important it is wrong.
>>>Here is why: in your SQL statement you don't specify an order in which
>database
>>>retrieves rows while you assume that rows' sequence stays the same. Oracle
>>>cannot guarantee that without ORDER BY clause it will retrieve rows in
>the
>>>same sequence, which means that every time you execute your SQL you may
>>get
>>>rows in different order and you don't get next 10 rows.
>>>
>>>In addition, it does full table scan anyway first, then it does SORT GROUP
>>>BY, then filtering. And it will do the same steps every SQL execution!
>If
>>>you have 1000 rows in the table, that may not be an issue, however I tested
>>>it on the table with 100,000 rows and it took 12 - 14 seconds on average
>>>to retrieve just 10 (!) rows.
>>>
>>>I think, the best way to do is via combination of 3 PL/SQL procedures
and
>>>cursor as a part of the package.
>>>
>>>First two procedures, OpenCursor and CloseCursor, will take care of opening
>>>and closing of the cursor.
>>>Third procedure RetrieveRows will fetch N (10) rows at a time using BULK
>>>COLLECT clause with LIMIT keyword, assuming you are running Oracle 8i
database.
>>>So, every time you call it you get next N rows.
>>>
>>>I think that's an accurate and efficient solution. I created a package
>and
>>>tested it: for the same 100,000 rows table it took 0.2 sec. to open the
>>cursor
>>>and steady 0.15 sec. to fetch every batch of next 10 rows.
>>>
>>>Hope it helps.
>>>Boris Milrud.
>>>
>>>
>>>
>>>"Angel" <rennaisance@wildmail.com> wrote:
>>>>
>>>>Hi Doe,
>>>>you can do this
>>>>(Assuming ur table name is MyTable and Column name is MyColumn)
>>>>
>>>>select rownum,max(MyColumn) from MyTable
>>>>group by rownum
>>>>having rownum>=1 and rownum<=10
>>>>
>>>>That will solve ur problem
>>>>"doe" <doekalay@yahoo.com> wrote:
>>>>>
>>>>>Angel:
>>>>>
>>>>>The thing is I do not want to bring all the records and
>>>>>pickup 10 after another in the program.
>>>>>
>>>>>Whenever I retrieve from the database, I want to get 10 records at
>>>>>a time.
>>>>>
>>>>>I think the one you suggested is I need to load all the records from
>>>>>the dbase and do a for loop with a program. That I do not want.
>>>>>
>>>>>thanks
>>>>>
>>>>>
>>>>>
>>>>>"Angel" <rennaisance@wildmail.com> wrote:
>>>>>>
>>>>>>"doe" <doekalay@yahoo.com> wrote:
>>>>>>>
>>>>>>>Deal all..
>>>>>>>
>>>>>>>I have 1000 records in a table.
>>>>>>>How can I retrieve 10 records at a time by Select statement? or any
>>other
>>>>>>>way? Each time I retrieve, the 10 records must not be previously obtained.
>>>>>>>
>>>>>>>thanks always!
>>>>>>You can do a normal select,using a for loop to retrive the first 10
>records.
>>>>>>At the end of the for loop, set the pointer to point to the 11th record
>>>>>and
>>>>>>so on...
>>>>>>
>>>>>
>>>>
>>>
>>
>

[NJMrBig]

NJMrBig,

Yes, it will. Each user is isolated and has it's own cursor.

So, in your scenario: user 1 gets first 10 rows (1 - 10), then gets next
10 rows (11 - 20), user 2 logs in and gets first 10 rows (1 - 10), user 1
gets next 10 rows (21 - 30), user 2 gets next 10 rows (11 - 20), etc.

Boris.


"NJMrBig" <NJMrBig@aol.com> wrote:
>
>Boris,
>
>This conversation thread has sparked my curiosity. Will this solution work
>on a per user basis? Scenario: user 1 gets first 10 rows, then gets next
>10 rows, user 2 logs in gets 10 rows-which 10 is it?
>
>-Big
>
>"Boris Milrud" <milrud@hotmail.com> wrote:
>>
>>Doe,
>>
>>The source of your problem is that you did not set up your procedure properly:
>>you need to use the package! Let me repeat the solution's description I
>put
>>in my first email:
>>
>>"I think, the best way to do is via combination of 3 PL/SQL procedures
and
>>cursor as a part of the package. First two procedures, OpenCursor and CloseCursor,
>>will take care of opening and closing of the cursor. Third procedure RetrieveRows
>>will fetch N (10) rows at a time using BULK COLLECT clause with LIMIT keyword,
>>assuming you are running Oracle 8i database. So, every time you call it
>you
>>get next N rows."
>>
>>Let me go further now and give you the sample code for this:
>>
>>******************************************************
>>create or replace package NRows as
>> type tName is record (OrgName s_org_ext.name%type);
>>
>> cursor csrNames return tName;
>>
>> procedure OpenCursor;
>>
>> procedure CloseCursor;
>>
>> procedure RetrieveRows(pRows pls_integer := 10);
>>
>>end NRows;
>>/
>>
>>create or replace package body NRows as
>> type tOrgName is
>> table of s_org_ext.name%type
>> index by binary_integer;
>>
>> vOrgName tOrgName;
>>
>> cursor csrNames return tName is
>> select name
>> from s_org_ext;
>>
>> procedure OpenCursor is
>> begin
>> if not csrNames%isopen
>> then
>> open csrNames;
>> dbms_output.put_line('Opening the cursor.');
>> end if;
>> end OpenCursor;
>>
>> procedure CloseCursor is
>> begin
>> if csrNames%isopen
>> then
>> close csrNames;
>> dbms_output.put_line('Closing the cursor.');
>> end if;
>> end CloseCursor;
>>
>> procedure RetrieveRows (pRows pls_integer := 10) is
>> begin
>>
>> /* Open the cursor */
>> if not csrNames%isopen
>> then
>> OpenCursor;
>> end if;
>>
>> fetch csrNames bulk collect into vOrgName limit pRows;
>>
>> if (vOrgName.count > 0)
>> then
>> for nIndex in 1..vOrgName.count
>> loop
>> dbms_output.put_line(vOrgName(nIndex));
>> end loop;
>> end if;
>>
>>
>> /* End of the cursor: close it. */
>> if csrNames%notfound
>> then
>> CloseCursor;
>> end if;
>>
>> end RetrieveRows;
>>
>>end NRows;
>>/
>>******************************************************
>>
>>Run (and, possibly, debug) the code above to create NRows package. Now,
>you
>>could use it to get next N rows,i.e.:
>>
>>begin
>> /* Retrieve first 10 rows */
>> Nrows.RetrieveRows(10);
>>end;
>>/
>>
>>begin
>> /* Retrieve next 20 rows */
>> Nrows.RetrieveRows(20);
>>end;
>>/
>>
>>begin
>> /* Retrieve next 10 rows */
>> Nrows.RetrieveRows;
>>end;
>>/
>>
>>Let me know if it works for you. I tested with my table and it did.
>>
>>Boris.
>>
>>
>>"doe" <doekalay@yahoo.com> wrote:
>>>
>>>Boris:
>>>
>>>I have been trying to implement your idea and got struck with Cursor.
The
>>>example code I am using is as follows:. The problem is I am getting only
>>>the first 10 records even if I run second or moreTH times. What I want
>to
>>>achieve is each time I run, I want a different 10 records. I think I need
>>>to set the cursor somehow. I would appreciate if you can throw some light
>>>here again.
>>>
>>>thanks always
>>>doe
>>>
>>>
>>> 1 DECLARE
>>> 2 TYPE nameTab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
>>> 3 CURSOR c1 IS SELECT name from s_org_ext where row_id between '00'
>>>and '10';
>>> 4 empname nameTab;
>>> 5 rows NATURAL :=10;
>>> 6 BEGIN
>>> 7 OPEN c1;
>>> 8 FETCH c1 BULK COLLECT INTO empname LIMIT rows;
>>> 9 CLOSE c1;
>>> 20* END;
>>>
>>>
>>>
>>>"Boris Milrud" <milrud@hotmail.com> wrote:
>>>>
>>>>Angel,
>>>>
>>>>The solution you provided is inefficient, but most important it is wrong.
>>>>Here is why: in your SQL statement you don't specify an order in which
>>database
>>>>retrieves rows while you assume that rows' sequence stays the same. Oracle
>>>>cannot guarantee that without ORDER BY clause it will retrieve rows in
>>the
>>>>same sequence, which means that every time you execute your SQL you may
>>>get
>>>>rows in different order and you don't get next 10 rows.
>>>>
>>>>In addition, it does full table scan anyway first, then it does SORT
GROUP
>>>>BY, then filtering. And it will do the same steps every SQL execution!
>>If
>>>>you have 1000 rows in the table, that may not be an issue, however I
tested
>>>>it on the table with 100,000 rows and it took 12 - 14 seconds on average
>>>>to retrieve just 10 (!) rows.
>>>>
>>>>I think, the best way to do is via combination of 3 PL/SQL procedures
>and
>>>>cursor as a part of the package.
>>>>
>>>>First two procedures, OpenCursor and CloseCursor, will take care of opening
>>>>and closing of the cursor.
>>>>Third procedure RetrieveRows will fetch N (10) rows at a time using BULK
>>>>COLLECT clause with LIMIT keyword, assuming you are running Oracle 8i
>database.
>>>>So, every time you call it you get next N rows.
>>>>
>>>>I think that's an accurate and efficient solution. I created a package
>>and
>>>>tested it: for the same 100,000 rows table it took 0.2 sec. to open the
>>>cursor
>>>>and steady 0.15 sec. to fetch every batch of next 10 rows.
>>>>
>>>>Hope it helps.
>>>>Boris Milrud.
>>>>
>>>>
>>>>
>>>>"Angel" <rennaisance@wildmail.com> wrote:
>>>>>
>>>>>Hi Doe,
>>>>>you can do this
>>>>>(Assuming ur table name is MyTable and Column name is MyColumn)
>>>>>
>>>>>select rownum,max(MyColumn) from MyTable
>>>>>group by rownum
>>>>>having rownum>=1 and rownum<=10
>>>>>
>>>>>That will solve ur problem
>>>>>"doe" <doekalay@yahoo.com> wrote:
>>>>>>
>>>>>>Angel:
>>>>>>
>>>>>>The thing is I do not want to bring all the records and
>>>>>>pickup 10 after another in the program.
>>>>>>
>>>>>>Whenever I retrieve from the database, I want to get 10 records at

>>>>>>a time.
>>>>>>
>>>>>>I think the one you suggested is I need to load all the records from
>>>>>>the dbase and do a for loop with a program. That I do not want.
>>>>>>
>>>>>>thanks
>>>>>>
>>>>>>
>>>>>>
>>>>>>"Angel" <rennaisance@wildmail.com> wrote:
>>>>>>>
>>>>>>>"doe" <doekalay@yahoo.com> wrote:
>>>>>>>>
>>>>>>>>Deal all..
>>>>>>>>
>>>>>>>>I have 1000 records in a table.
>>>>>>>>How can I retrieve 10 records at a time by Select statement? or any
>>>other
>>>>>>>>way? Each time I retrieve, the 10 records must not be previously
obtained.
>>>>>>>>
>>>>>>>>thanks always!
>>>>>>>You can do a normal select,using a for loop to retrive the first 10
>>records.
>>>>>>>At the end of the for loop, set the pointer to point to the 11th record
>>>>>>and
>>>>>>>so on...
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>

[Boris Milrud]

Sorry, I put NJMrBig's name instead of mine.

Boris.

"NJMrBig" <milrud@hotmail.com> wrote:
>
>NJMrBig,
>
>Yes, it will. Each user is isolated and has it's own cursor.
>
>So, in your scenario: user 1 gets first 10 rows (1 - 10), then gets next
>10 rows (11 - 20), user 2 logs in and gets first 10 rows (1 - 10), user
1
>gets next 10 rows (21 - 30), user 2 gets next 10 rows (11 - 20), etc.
>
>Boris.
>
>
>"NJMrBig" <NJMrBig@aol.com> wrote:
>>
>>Boris,
>>
>>This conversation thread has sparked my curiosity. Will this solution work
>>on a per user basis? Scenario: user 1 gets first 10 rows, then gets next
>>10 rows, user 2 logs in gets 10 rows-which 10 is it?
>>
>>-Big
>>
>>"Boris Milrud" <milrud@hotmail.com> wrote:
>>>
>>>Doe,
>>>
>>>The source of your problem is that you did not set up your procedure properly:
>>>you need to use the package! Let me repeat the solution's description
I
>>put
>>>in my first email:
>>>
>>>"I think, the best way to do is via combination of 3 PL/SQL procedures
>and
>>>cursor as a part of the package. First two procedures, OpenCursor and
CloseCursor,
>>>will take care of opening and closing of the cursor. Third procedure RetrieveRows
>>>will fetch N (10) rows at a time using BULK COLLECT clause with LIMIT
keyword,
>>>assuming you are running Oracle 8i database. So, every time you call it
>>you
>>>get next N rows."
>>>
>>>Let me go further now and give you the sample code for this:
>>>
>>>******************************************************
>>>create or replace package NRows as
>>> type tName is record (OrgName s_org_ext.name%type);
>>>
>>> cursor csrNames return tName;
>>>
>>> procedure OpenCursor;
>>>
>>> procedure CloseCursor;
>>>
>>> procedure RetrieveRows(pRows pls_integer := 10);
>>>
>>>end NRows;
>>>/
>>>
>>>create or replace package body NRows as
>>> type tOrgName is
>>> table of s_org_ext.name%type
>>> index by binary_integer;
>>>
>>> vOrgName tOrgName;
>>>
>>> cursor csrNames return tName is
>>> select name
>>> from s_org_ext;
>>>
>>> procedure OpenCursor is
>>> begin
>>> if not csrNames%isopen
>>> then
>>> open csrNames;
>>> dbms_output.put_line('Opening the cursor.');
>>> end if;
>>> end OpenCursor;
>>>
>>> procedure CloseCursor is
>>> begin
>>> if csrNames%isopen
>>> then
>>> close csrNames;
>>> dbms_output.put_line('Closing the cursor.');
>>> end if;
>>> end CloseCursor;
>>>
>>> procedure RetrieveRows (pRows pls_integer := 10) is
>>> begin
>>>
>>> /* Open the cursor */
>>> if not csrNames%isopen
>>> then
>>> OpenCursor;
>>> end if;
>>>
>>> fetch csrNames bulk collect into vOrgName limit pRows;
>>>
>>> if (vOrgName.count > 0)
>>> then
>>> for nIndex in 1..vOrgName.count
>>> loop
>>> dbms_output.put_line(vOrgName(nIndex));
>>> end loop;
>>> end if;
>>>
>>>
>>> /* End of the cursor: close it. */
>>> if csrNames%notfound
>>> then
>>> CloseCursor;
>>> end if;
>>>
>>> end RetrieveRows;
>>>
>>>end NRows;
>>>/
>>>******************************************************
>>>
>>>Run (and, possibly, debug) the code above to create NRows package. Now,
>>you
>>>could use it to get next N rows,i.e.:
>>>
>>>begin
>>> /* Retrieve first 10 rows */
>>> Nrows.RetrieveRows(10);
>>>end;
>>>/
>>>
>>>begin
>>> /* Retrieve next 20 rows */
>>> Nrows.RetrieveRows(20);
>>>end;
>>>/
>>>
>>>begin
>>> /* Retrieve next 10 rows */
>>> Nrows.RetrieveRows;
>>>end;
>>>/
>>>
>>>Let me know if it works for you. I tested with my table and it did.
>>>
>>>Boris.
>>>
>>>
>>>"doe" <doekalay@yahoo.com> wrote:
>>>>
>>>>Boris:
>>>>
>>>>I have been trying to implement your idea and got struck with Cursor.
>The
>>>>example code I am using is as follows:. The problem is I am getting only
>>>>the first 10 records even if I run second or moreTH times. What I want
>>to
>>>>achieve is each time I run, I want a different 10 records. I think I
need
>>>>to set the cursor somehow. I would appreciate if you can throw some light
>>>>here again.
>>>>
>>>>thanks always
>>>>doe
>>>>
>>>>
>>>> 1 DECLARE
>>>> 2 TYPE nameTab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
>>>> 3 CURSOR c1 IS SELECT name from s_org_ext where row_id between '00'
>>>>and '10';
>>>> 4 empname nameTab;
>>>> 5 rows NATURAL :=10;
>>>> 6 BEGIN
>>>> 7 OPEN c1;
>>>> 8 FETCH c1 BULK COLLECT INTO empname LIMIT rows;
>>>> 9 CLOSE c1;
>>>> 20* END;
>>>>
>>>>
>>>>
>>>>"Boris Milrud" <milrud@hotmail.com> wrote:
>>>>>
>>>>>Angel,
>>>>>
>>>>>The solution you provided is inefficient, but most important it is wrong.
>>>>>Here is why: in your SQL statement you don't specify an order in which
>>>database
>>>>>retrieves rows while you assume that rows' sequence stays the same.
Oracle
>>>>>cannot guarantee that without ORDER BY clause it will retrieve rows
in
>>>the
>>>>>same sequence, which means that every time you execute your SQL you
may
>>>>get
>>>>>rows in different order and you don't get next 10 rows.
>>>>>
>>>>>In addition, it does full table scan anyway first, then it does SORT
>GROUP
>>>>>BY, then filtering. And it will do the same steps every SQL execution!
>>>If
>>>>>you have 1000 rows in the table, that may not be an issue, however I
>tested
>>>>>it on the table with 100,000 rows and it took 12 - 14 seconds on average
>>>>>to retrieve just 10 (!) rows.
>>>>>
>>>>>I think, the best way to do is via combination of 3 PL/SQL procedures
>>and
>>>>>cursor as a part of the package.
>>>>>
>>>>>First two procedures, OpenCursor and CloseCursor, will take care of
opening
>>>>>and closing of the cursor.
>>>>>Third procedure RetrieveRows will fetch N (10) rows at a time using
BULK
>>>>>COLLECT clause with LIMIT keyword, assuming you are running Oracle 8i
>>database.
>>>>>So, every time you call it you get next N rows.
>>>>>
>>>>>I think that's an accurate and efficient solution. I created a package
>>>and
>>>>>tested it: for the same 100,000 rows table it took 0.2 sec. to open
the
>>>>cursor
>>>>>and steady 0.15 sec. to fetch every batch of next 10 rows.
>>>>>
>>>>>Hope it helps.
>>>>>Boris Milrud.
>>>>>
>>>>>
>>>>>
>>>>>"Angel" <rennaisance@wildmail.com> wrote:
>>>>>>
>>>>>>Hi Doe,
>>>>>>you can do this
>>>>>>(Assuming ur table name is MyTable and Column name is MyColumn)
>>>>>>
>>>>>>select rownum,max(MyColumn) from MyTable
>>>>>>group by rownum
>>>>>>having rownum>=1 and rownum<=10
>>>>>>
>>>>>>That will solve ur problem
>>>>>>"doe" <doekalay@yahoo.com> wrote:
>>>>>>>
>>>>>>>Angel:
>>>>>>>
>>>>>>>The thing is I do not want to bring all the records and
>>>>>>>pickup 10 after another in the program.
>>>>>>>
>>>>>>>Whenever I retrieve from the database, I want to get 10 records at
>
>>>>>>>a time.
>>>>>>>
>>>>>>>I think the one you suggested is I need to load all the records from
>>>>>>>the dbase and do a for loop with a program. That I do not want.
>>>>>>>
>>>>>>>thanks
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>"Angel" <rennaisance@wildmail.com> wrote:
>>>>>>>>
>>>>>>>>"doe" <doekalay@yahoo.com> wrote:
>>>>>>>>>
>>>>>>>>>Deal all..
>>>>>>>>>
>>>>>>>>>I have 1000 records in a table.
>>>>>>>>>How can I retrieve 10 records at a time by Select statement? or
any
>>>>other
>>>>>>>>>way? Each time I retrieve, the 10 records must not be previously
>obtained.
>>>>>>>>>
>>>>>>>>>thanks always!
>>>>>>>>You can do a normal select,using a for loop to retrive the first
10
>>>records.
>>>>>>>>At the end of the for loop, set the pointer to point to the 11th
record
>>>>>>>and
>>>>>>>>so on...
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>

[NJMrBig]

run the following scripts;

create type ARRAY_VARCHAR2_4000 is table of varchar2(4000)


create or replace PROCEDURE PROC_GET_ROWS_01
(
PO_VA_EMP_ID OUT ARRAY_VARCHAR2_4000
,PO_VA_EMP_NAME OUT ARRAY_VARCHAR2_4000
,PO_VA_TAB_INFORMATION OUT ARRAY_VARCHAR2_4000
,PO_NM_ERROR_NUM OUT NUMBER
,PO_VR_ERROR_MSG OUT VARCHAR2
)
AS

CURSOR CUR_ROWS
IS SELECT * from emp order by emp_id;



LV_NM_TAB_COUNT NUMBER := 0; -- Total number of tabs
LV_NM_TAB_REC_COUNT NUMBER := 0; -- count to keep track of
how many records are populated
LV_NM_RECORDS_COUNT NUMBER := 0; -- Total number of records
LV_NM_SELECTED_TAB NUMBER := 0; -- selected for which details
will be populated
LV_NM_SCREEN_TAB_SIZE NUMBER := 10; --tab size
LV_NM_ACTUAL_RECORDS_IN_TAB NUMBER := 0; -- ACTUAL RECORDS IN TAB
LV_EX_OTHERS EXCEPTION;


BEGIN

LV_NM_SCREEN_TAB_SIZE := 10; -- number records to be displayed each time



PO_VA_EMP_ID := ARRAY_VARCHAR2_4000();
PO_VA_EMP_NAME := ARRAY_VARCHAR2_4000();
PO_VA_TAB_INFORMATION OUT := ARRAY_VARCHAR2_4000();

PO_NM_ERROR_NUM := 0;
PO_VR_ERROR_MSG := 'Success';

FOR I IN CUR_ROWS
LOOP
LV_NM_TAB_REC_COUNT := LV_NM_TAB_REC_COUNT + 1;
LV_NM_RECORDS_COUNT := LV_NM_RECORDS_COUNT + 1;


IF(LV_NM_TAB_REC_COUNT = 1) THEN
LV_NM_TAB_COUNT := LV_NM_TAB_COUNT + 1;
IF(LV_NM_TAB_COUNT <= NVL(PI_NM_REVISION_TAB , 1)) THEN
PO_VA_EMP_ID .DELETE;
PO_VA_EMP_NAME .DELETE;
LV_NM_SELECTED_TAB := LV_NM_TAB_COUNT;
END IF;
END IF;


IF(LV_NM_TAB_COUNT = NVL(PI_NM_TAB, 1)) THEN
PO_VA_EMP_ID .EXTEND;
PO_VA_EMP_NAME .EXTEND;
PO_VA_EMP_ID (LV_NM_TAB_REC_COUNT) := I.EMP_ID;
PO_VA_EMP_NAME (LV_NM_TAB_REC_COUNT) := I.EMP_NAME;
IF(LV_NM_TAB_REC_COUNT >= LV_NM_SCREEN_TAB_SIZE) THEN
LV_NM_TAB_REC_COUNT := 0;
END IF;
END LOOP;



IF(LV_NM_RECORDS_COUNT = 0) THEN
PO_NM_ERROR_NUM := 2;
PO_VR_ERROR_MSG := 'NO DATA';
RAISE LV_EX_OTHERS;
ELSE
PO_VA_TAB_INFORMATION.EXTEND;
PO_VA_TAB_INFORMATION(1) := TO_CHAR(LV_NM_TAB_COUNT) ; --Number
of tabs
PO_VA_TAB_INFORMATION.EXTEND;
PO_VA_TAB_INFORMATION(2) := TO_CHAR(LV_NM_SELECTED_TAB) ; --Selected
Tab
PO_VA_TAB_INFORMATION.EXTEND;
PO_VA_TAB_INFORMATION(3) := TO_CHAR(LV_NM_RECORDS_COUNT) ; --total number
of records
PO_VA_TAB_INFORMATION.EXTEND;
PO_VA_TAB_INFORMATION(4) := TO_CHAR(LV_NM_SCREEN_TAB_SIZE) ; --Tab size
PO_VA_TAB_INFORMATION.EXTEND;
PO_VA_TAB_INFORMATION(5) := PO_VA_SPL_ID.COUNT ; --Number
of actual Records in tab
END IF;

EXCEPTION
WHEN OTHERS THEN
IF CUR_ROWS%ISOPEN THEN
CLOSE CUR_ROWS;
END IF;

IF(PO_NM_ERROR_NUM = 0) THEN
PO_NM_ERROR_NUM := 01;
PO_VR_ERROR_MSG := 'Error in PROC_GET_ROWS_01 - ' || SQLERRM ;
END IF;


END PROC_GET_ROWS_01;


ex:

suppose there are 1000 records in table :

10 -- number of records to be displayed.
1000/ 10 = 100 (number of tabs )

1tab (1-10)
2tab(11-20)
3tab(21-30)

[Pablo E]

Hi, But what if I'm working on a web environment (sessionless environment)
because we connect, fetch, close connection each time somebody is getting
something. your answer won't work.

figure I have paging on a list (I mean, the user can see: Page 1 of 10) and
the user wants to see page #4, each page has e.g. 10 records.

the cursor won't exist each time I request info from the database.

how to do in this case?

"NJMrBig" <milrud@hotmail.com> wrote:
>
>NJMrBig,
>
>Yes, it will. Each user is isolated and has it's own cursor.
>
>So, in your scenario: user 1 gets first 10 rows (1 - 10), then gets next
>10 rows (11 - 20), user 2 logs in and gets first 10 rows (1 - 10), user
1
>gets next 10 rows (21 - 30), user 2 gets next 10 rows (11 - 20), etc.
>
>Boris.
>
>
>"NJMrBig" <NJMrBig@aol.com> wrote:
>>
>>Boris,
>>
>>This conversation thread has sparked my curiosity. Will this solution work
>>on a per user basis? Scenario: user 1 gets first 10 rows, then gets next
>>10 rows, user 2 logs in gets 10 rows-which 10 is it?
>>
>>-Big
>>
>>"Boris Milrud" <milrud@hotmail.com> wrote:
>>>
>>>Doe,
>>>
>>>The source of your problem is that you did not set up your procedure properly:
>>>you need to use the package! Let me repeat the solution's description
I
>>put
>>>in my first email:
>>>
>>>"I think, the best way to do is via combination of 3 PL/SQL procedures
>and
>>>cursor as a part of the package. First two procedures, OpenCursor and
CloseCursor,
>>>will take care of opening and closing of the cursor. Third procedure RetrieveRows
>>>will fetch N (10) rows at a time using BULK COLLECT clause with LIMIT
keyword,
>>>assuming you are running Oracle 8i database. So, every time you call it
>>you
>>>get next N rows."
>>>
>>>Let me go further now and give you the sample code for this:
>>>
>>>******************************************************
>>>create or replace package NRows as
>>> type tName is record (OrgName s_org_ext.name%type);
>>>
>>> cursor csrNames return tName;
>>>
>>> procedure OpenCursor;
>>>
>>> procedure CloseCursor;
>>>
>>> procedure RetrieveRows(pRows pls_integer := 10);
>>>
>>>end NRows;
>>>/
>>>
>>>create or replace package body NRows as
>>> type tOrgName is
>>> table of s_org_ext.name%type
>>> index by binary_integer;
>>>
>>> vOrgName tOrgName;
>>>
>>> cursor csrNames return tName is
>>> select name
>>> from s_org_ext;
>>>
>>> procedure OpenCursor is
>>> begin
>>> if not csrNames%isopen
>>> then
>>> open csrNames;
>>> dbms_output.put_line('Opening the cursor.');
>>> end if;
>>> end OpenCursor;
>>>
>>> procedure CloseCursor is
>>> begin
>>> if csrNames%isopen
>>> then
>>> close csrNames;
>>> dbms_output.put_line('Closing the cursor.');
>>> end if;
>>> end CloseCursor;
>>>
>>> procedure RetrieveRows (pRows pls_integer := 10) is
>>> begin
>>>
>>> /* Open the cursor */
>>> if not csrNames%isopen
>>> then
>>> OpenCursor;
>>> end if;
>>>
>>> fetch csrNames bulk collect into vOrgName limit pRows;
>>>
>>> if (vOrgName.count > 0)
>>> then
>>> for nIndex in 1..vOrgName.count
>>> loop
>>> dbms_output.put_line(vOrgName(nIndex));
>>> end loop;
>>> end if;
>>>
>>>
>>> /* End of the cursor: close it. */
>>> if csrNames%notfound
>>> then
>>> CloseCursor;
>>> end if;
>>>
>>> end RetrieveRows;
>>>
>>>end NRows;
>>>/
>>>******************************************************
>>>
>>>Run (and, possibly, debug) the code above to create NRows package. Now,
>>you
>>>could use it to get next N rows,i.e.:
>>>
>>>begin
>>> /* Retrieve first 10 rows */
>>> Nrows.RetrieveRows(10);
>>>end;
>>>/
>>>
>>>begin
>>> /* Retrieve next 20 rows */
>>> Nrows.RetrieveRows(20);
>>>end;
>>>/
>>>
>>>begin
>>> /* Retrieve next 10 rows */
>>> Nrows.RetrieveRows;
>>>end;
>>>/
>>>
>>>Let me know if it works for you. I tested with my table and it did.
>>>
>>>Boris.
>>>
>>>
>>>"doe" <doekalay@yahoo.com> wrote:
>>>>
>>>>Boris:
>>>>
>>>>I have been trying to implement your idea and got struck with Cursor.
>The
>>>>example code I am using is as follows:. The problem is I am getting only
>>>>the first 10 records even if I run second or moreTH times. What I want
>>to
>>>>achieve is each time I run, I want a different 10 records. I think I
need
>>>>to set the cursor somehow. I would appreciate if you can throw some light
>>>>here again.
>>>>
>>>>thanks always
>>>>doe
>>>>
>>>>
>>>> 1 DECLARE
>>>> 2 TYPE nameTab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
>>>> 3 CURSOR c1 IS SELECT name from s_org_ext where row_id between '00'
>>>>and '10';
>>>> 4 empname nameTab;
>>>> 5 rows NATURAL :=10;
>>>> 6 BEGIN
>>>> 7 OPEN c1;
>>>> 8 FETCH c1 BULK COLLECT INTO empname LIMIT rows;
>>>> 9 CLOSE c1;
>>>> 20* END;
>>>>
>>>>
>>>>
>>>>"Boris Milrud" <milrud@hotmail.com> wrote:
>>>>>
>>>>>Angel,
>>>>>
>>>>>The solution you provided is inefficient, but most important it is wrong.
>>>>>Here is why: in your SQL statement you don't specify an order in which
>>>database
>>>>>retrieves rows while you assume that rows' sequence stays the same.
Oracle
>>>>>cannot guarantee that without ORDER BY clause it will retrieve rows
in
>>>the
>>>>>same sequence, which means that every time you execute your SQL you
may
>>>>get
>>>>>rows in different order and you don't get next 10 rows.
>>>>>
>>>>>In addition, it does full table scan anyway first, then it does SORT
>GROUP
>>>>>BY, then filtering. And it will do the same steps every SQL execution!
>>>If
>>>>>you have 1000 rows in the table, that may not be an issue, however I
>tested
>>>>>it on the table with 100,000 rows and it took 12 - 14 seconds on average
>>>>>to retrieve just 10 (!) rows.
>>>>>
>>>>>I think, the best way to do is via combination of 3 PL/SQL procedures
>>and
>>>>>cursor as a part of the package.
>>>>>
>>>>>First two procedures, OpenCursor and CloseCursor, will take care of
opening
>>>>>and closing of the cursor.
>>>>>Third procedure RetrieveRows will fetch N (10) rows at a time using
BULK
>>>>>COLLECT clause with LIMIT keyword, assuming you are running Oracle 8i
>>database.
>>>>>So, every time you call it you get next N rows.
>>>>>
>>>>>I think that's an accurate and efficient solution. I created a package
>>>and
>>>>>tested it: for the same 100,000 rows table it took 0.2 sec. to open
the
>>>>cursor
>>>>>and steady 0.15 sec. to fetch every batch of next 10 rows.
>>>>>
>>>>>Hope it helps.
>>>>>Boris Milrud.
>>>>>
>>>>>
>>>>>
>>>>>"Angel" <rennaisance@wildmail.com> wrote:
>>>>>>
>>>>>>Hi Doe,
>>>>>>you can do this
>>>>>>(Assuming ur table name is MyTable and Column name is MyColumn)
>>>>>>
>>>>>>select rownum,max(MyColumn) from MyTable
>>>>>>group by rownum
>>>>>>having rownum>=1 and rownum<=10
>>>>>>
>>>>>>That will solve ur problem
>>>>>>"doe" <doekalay@yahoo.com> wrote:
>>>>>>>
>>>>>>>Angel:
>>>>>>>
>>>>>>>The thing is I do not want to bring all the records and
>>>>>>>pickup 10 after another in the program.
>>>>>>>
>>>>>>>Whenever I retrieve from the database, I want to get 10 records at
>
>>>>>>>a time.
>>>>>>>
>>>>>>>I think the one you suggested is I need to load all the records from
>>>>>>>the dbase and do a for loop with a program. That I do not want.
>>>>>>>
>>>>>>>thanks
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>"Angel" <rennaisance@wildmail.com> wrote:
>>>>>>>>
>>>>>>>>"doe" <doekalay@yahoo.com> wrote:
>>>>>>>>>
>>>>>>>>>Deal all..
>>>>>>>>>
>>>>>>>>>I have 1000 records in a table.
>>>>>>>>>How can I retrieve 10 records at a time by Select statement? or
any
>>>>other
>>>>>>>>>way? Each time I retrieve, the 10 records must not be previously
>obtained.
>>>>>>>>>
>>>>>>>>>thanks always!
>>>>>>>>You can do a normal select,using a for loop to retrive the first
10
>>>records.
>>>>>>>>At the end of the for loop, set the pointer to point to the 11th
record
>>>>>>>and
>>>>>>>>so on...
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>