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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 17

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

  1. #1
    doe Guest

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


    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!

  2. #2
    Angel Guest

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


    "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...


  3. #3
    doe Guest

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


    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...
    >



  4. #4
    Angel Guest

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


    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...
    >>

    >



  5. #5
    doe Guest

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


    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...
    >>>

    >>

    >



  6. #6
    Angel Guest

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


    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...
    >>>>
    >>>

    >>

    >



  7. #7
    doe Guest

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


    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...
    >>>>>
    >>>>
    >>>

    >>

    >



  8. #8
    Boris Milrud Guest

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


    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...
    >>>

    >>

    >



  9. #9
    doe Guest

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


    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...
    >>>>
    >>>

    >>

    >



  10. #10
    Boris Milrud Guest

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


    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...
    >>>>>
    >>>>
    >>>

    >>

    >



  11. #11
    NJMrBig Guest

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


    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...
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  12. #12
    NJMrBig Guest

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


    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...
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  13. #13
    Boris Milrud Guest

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


    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...
    >>>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



  14. #14
    NJMrBig Guest

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


    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)

  15. #15
    Pablo E Guest

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


    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...
    >>>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>
    >>>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center