Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to move cursor to skip couple of record.

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

From: Pablo <pablo1999_at_my-dejanews.com>
Date: Tue, 18 May 1999 21:28:48 GMT
Message-ID: <7hsm2g$qn9$1@nnrp1.deja.com>


Hi, here's a description of the problem I had, and the solution I found with your help. Thank you very much.
Any comment is wellcome.

Note: as said in another message of this thread,

      it was better for me to ask for the first N rows
      such that some_field >= given_value
      than to ask for rows with position between M and M+N.

Given
 table centro.rubros:
 codigo number /* code */
 nombre varchar2(50) /* name */

I'd like to get the 10 first records of:  select * from centro.rubros where nombre >= nom order by nombre;

Because
 select * from centro.rubros where nombre >= nom and rownum <= 10  order by nombre;
doesn't give me what I want,

I use:

  table centro.temp_rub:
  qry_id number /* query id */   codigo number
  nombre varchar2(50)

  sequence centro.temp_id /* query id generator */

  First of all I call centro.rango_rubros.getid   Then I call centro.rango_rubros.get as many times as I need,
  Finally I call centro.rango_rubros.deltmp

create or replace package centro.rango_rubros as
 cursor c is select codigo, nombre from temp_rub;  type type_cur is ref cursor return c%rowtype;  procedure get( qid in number, nomb in varchar2, rub_cursor in out type_cur );
 procedure getid(qid out number);
 procedure deltmp(qid in number);
end;

create or replace package body centro.rango_rubros as
 procedure getid( qid out number ) /* call when session begins */  is
 begin
  SELECT temp_id.nextval INTO qid FROM DUAL;  end;

 procedure deltmp(qid in number) /* call when session ends */  is
 begin
  DELETE temp_rub WHERE qry_id = qid;
 end;

 procedure get(qid in number, nomb in varchar2, rub_cursor in out type_cur )
 is
  CURSOR c1 (n in varchar2) IS
    SELECT codigo, nombre
    FROM rubros
    WHERE (nombre >= n) OR (n is null)
    ORDER BY nombre;
  i number;
  c number; /* code */
  n varchar2(50); /* name */
 begin
   DELETE temp_rub WHERE qry_id = qid;
   OPEN c1(nomb);
   i := 1;
   LOOP
    FETCH c1 INTO c, n;
    EXIT WHEN (c1%NOTFOUND) or (c1%NOTFOUND=NULL);     INSERT INTO temp_rub (qry_id, codigo, nombre) VALUES ( qid, c, n );

    EXIT WHEN i=10; /* no more than 10 rows */     i := i + 1;
   END LOOP;
   CLOSE c1;
   OPEN rub_cursor FOR
    SELECT codigo, nombre
    FROM temp_rub
    WHERE qry_id = qid
    ORDER BY nombre;
 end;
end;



In article <373fd512.0_at_145.227.194.253>,   "Mark Gumbs" <mgumbs--AT--hotmail.com> wrote:
> 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.---
>
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Tue May 18 1999 - 16:28:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US