Re: How to move cursor to skip couple of record.

The 'solution' i had was more of a theoretical idea as opposed to a solution.
I tried it myself and got the results you did. Nothing!

Another suggested way is to create a temp table based on your query and have a unique sequence number allocated to each line.

SQL> desc emp

 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 MGR                                      NUMBER(4)
 HIREDATE                                 DATE
 SAL                                      NUMBER(7,2)
 COMM                                     NUMBER(7,2)
 DEPTNO                                   NUMBER(2)

SQL> create table temp_emp
  2 (seq number,
  3 empno number,
  4 ename varchar2(10));

Table created.

SQL> create sequence seqno start with 1;

Sequence created.

SQL> insert into temp_emp
  2 select seqno.nextval, empno, ename from emp;

14 rows created.

SQL> select * from temp_emp
  2 where seq > 1 and seq < 6;

      SEQ EMPNO ENAME
--------- --------- ----------

        2      7499 ALLEN
        3      7521 WARD
        4      7566 JONES
        5      7654 MARTIN

SQL> select * from temp_emp
  2 where seq >= 6 and seq < 12;

      SEQ EMPNO ENAME
--------- --------- ----------

        6      7698 BLAKE
        7      7782 CLARK
        8      7788 SCOTT
        9      7839 KING
       10      7844 TURNER
       11      7876 ADAMS

6 rows selected.

SQL> In your program, keep a running count of v_counter, you can then have a cursor say
  cursor c1(v_counter in number) is
  select * form temp_emp
  where seq >= v_counter and seq <= v_counter + 10;

Or something similar if using the set-block-property.

Hope this givs you a headstart on what you are doing, let us know of the outcome.

Mark

pablo1999_at_my-dejanews.com wrote in message <7hi0ku$nif$1_at_nnrp1.deja.com>...
>I have the same question and your answer didn't
>help me because:
>
>1) When v_counter <> 1 I get an empty resultset.
>2) What if my query has an "order by somefield"?
> Even with v_counter = 1, it doesn't returns
> the first 10 rows of the whole query.
>
>Thanks in advance.
>Please also replay to pablo_at_dirpat.com.ar
>
>Oracle's SQL Reference says about 1) :
>
>"Note that conditions testing for ROWNUM values
>greater than a positive integer are always false.
>For example, this query returns no rows:
>
>SELECT * FROM emp WHERE ROWNUM > 1;"
>
>In article <373a89fb.0_at_145.227.194.253>,
> "Mark Gumbs" <mgumbs--AT--hotmail.com> wrote:
>> You may have to keep a counter in your
>application on what records you want
>>
>> e.g in your application, counter = 20
>>
>> cursor c_data(v_counter in number) is
>> select empno, ename
>> from emp
>> where rownum >= v_counter
>> and rownum < v_counter + 10;
>>
>> You then call the cursor in your application
>with a parameter of counter
>> .....for records in c_data(counter)...
>>
>> HTH
>>
>> Mark
>>
>> --------------------------
>>
>> Peizhong Wu wrote in message
><3739DB52.D382C508_at_umich.edu>...
>> >What I am trying to do is to implement a web
>application. Say, client can
>> get a
>> >
>> >page of 10 rows, then he can press the
>"Previous 10" or "Next 10" button to
>> get
>> >
>> >more.
>> >
>> >I do not know how people do this.
>> >
>> >Thanks
>> >
>> >Jerry Gitomer wrote:
>> >
>> >> Hi,
>> >>
>> >> You can, but you shouldn't. Oracle does
>not store data in a
>> predictable
>> >> sequence. If you are inserting a new row
>into a table it may be inserted
>> >> anywhere. As a result you have no way of
>knowing what the 10th row in
>> the
>> >> table contains. If you have already
>retrieved the 10th row in the table
>> and
>> >> you know you will want to access again just
>save the unique key or the
>> rowid
>> >> of the row and use one or the other when you
>want to access that same row
>> in
>> >> the future.
>> >>
>> >> regards
>> >>
>> >> Jerry Gitomer
>> >>
>> >> Peizhong Wu wrote in message
><3739C2C5.31D7B844_at_umich.edu>...
>> >> >HI,
>> >> >
>> >> >How can I move cursor to #10 record without
>fetch the first 9 records?
>> >> >
>> >> >Thanks
>> >> >
>> >> >
>> >
>>
>>
>
>
>
>--== Sent via Deja.com http://www.deja.com/ ==--
>---Share what you know. Learn what you don't.---
Received on Mon May 17 1999 - 03:45:54 CDT