Dear,
We have a screen with a little more than a thousand records and when this is selected it brings all the records to the screen, leaving it immense.
I want to pay it for every 100 records she go to another page.
Does anyone know how?
I hope noticas
Pagination
-
- Moderador
- Posts: 53
- Joined: Mon, 12 Jul 2004 11:24 am
- Location: Salvador
Atenciosamente,
Eduardo Santana
"Viva o dia de hoje como se fosse amanhã, por isso, não perca as oportunidades...."
Eduardo Santana
"Viva o dia de hoje como se fosse amanhã, por isso, não perca as oportunidades...."
- dr_gori
- Moderador
- Posts: 5024
- Joined: Mon, 03 May 2004 3:08 pm
- Location: Portland, OR USA
- Contact:
Thomas F. G
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
Well, it's a way I do not know if it's the best ...
See:
This example will pick up the page 3 , breaking from 10 on 10.
See:
select b.campo
from
(
select rownum seq, a.*
from
(select 'numero '||rownum campo --> este é o SQL que você pode mduar.
from all_tables
) a
order by a.campo
) b
where b.seq>= ( 3 * 10 ) - 10
and b.seq< 3 * 10
/
Thomas Kyte gave the following pagination tip: http://asktom.oracle.com/pls/ask/f?p=49 ... 7412348064
Example:
select *
from ( select a.*, rownum rnum
from ( SEU_SQL_VAI_AQUI -- com order by ) a
where rownum <= MAX_ROWS )
where rnum >= MIN_ROWS
/
select *
from ( select a.*, rownum rnum
from
(select 'numero '||rownum campo
from all_tables
) a
where rownum <= 30 )
where rnum >= 10
-
- Rank: Estagiário Pleno
- Posts: 4
- Joined: Mon, 09 Aug 2010 3:14 pm
- Location: rio de janeiro - RJ
I have a pagination, but it's inside a cursor and does not work at all with the tip above.
The procedure script with error and package referenced by it below.
Now the package code:
Can anyone help?
The procedure script with error and package referenced by it below.
create or replace
PROCEDURE SUPPSEARCH (
v_cursor IN OUT PKG_PORTAL.RCT1 ,
v_dateNow IN VARCHAR2,
v_country IN VARCHAR2,
v_city IN VARCHAR2,
v_praticeField IN NUMBER,
v_title IN VARCHAR2,
v_currentPage IN INTEGER ,
v_pageSize IN INTEGER ,
v_status IN VARCHAR2
)
AS
BEGIN
OPEN v_cursor FOR
SELECT *
FROM ( SELECT topn.*, ROWNUM rnum FROM (
SELECT
S.SUPPID,S.PRAREAID,S.SUPPDATENOW,S.SUPPSTATUS,suppDateNow,P.counNamePT AS suppCountry,PR.prareaTitle
FROM
Supplier S
inner join
country p
on p.counId = s.counId
inner join
pracarea pr
on s.prareaId = pr.prareaId
where
TO_CHAR( s.suppDateNow, 'DD/MM/YYYY' ) = NVL( v_dateNow, TO_CHAR( s.suppDateNow, 'DD/MM/YYYY' ) )
and
S.counId = NVL( v_country, S.counId )
and
S.suppCity LIKE '%' || v_city || '%'
and
S.prareaId = NVL( v_praticeField, S.prareaId )
and
S.suppStatus = NVL(v_status,s.suppStatus)
order by S.SUPPID;
) topn where ROWNUM <= ( ( ( v_currentPage + 1 ) * v_pageSize ) ) ) where rnum >= ( v_currentPage * v_pageSize );
END;
Now the package code:
create or replace PACKAGE PKG_PORTAL
AS
TYPE RCT1 IS REF CURSOR ;
TRANCOUNT INTEGER := 0 ;
IDENTITY INTEGER ;
END ;
-
- Rank: Oracle Guru
- Posts: 448
- Joined: Tue, 16 Jun 2009 3:07 pm
- Location: Parobé - RS
Sérgio Luiz Bonemberger Junior
Programador Junior
Parobé RS
[]s
Programador Junior
Parobé RS
[]s
What is the error appearing ??
Note: Whenever you post code use the Code DAE tag is easier for everyone to read
Ex:
Note: Whenever you post code use the Code DAE tag is easier for everyone to read
Ex:
teste
In a mailing list a user did this same question a few days, follows the chiappa response:
The basic technique is something like:
]
in http://asktom.oracle.com/pls/apex/f?p=1 ... 0346816567 and discuss some performance points, but for only 350k lines in a production hardware you must have qualquer issue ...
99]] [] S
Chiappa
-
- Rank: Estagiário Pleno
- Posts: 4
- Joined: Mon, 09 Aug 2010 3:14 pm
- Location: rio de janeiro - RJ
The error it presents is as follows:
Error(22,1): PL/SQL: SQL Statement ignored
Error(23,22): PL/SQL: ORA-00918: column ambiguously defined
-
- Rank: Estagiário Pleno
- Posts: 4
- Joined: Mon, 09 Aug 2010 3:14 pm
- Location: rio de janeiro - RJ
The error is in this line
SELECT *
FROM ( SELECT topn.*, ROWNUM rnum FROM (
SELECT
Error(18,1): PL/SQL: SQL Statement ignored
Error(19,22): PL/SQL: ORA-00918: column ambiguously defined
-
- Rank: Oracle Guru
- Posts: 448
- Joined: Tue, 16 Jun 2009 3:07 pm
- Location: Parobé - RS
Sérgio Luiz Bonemberger Junior
Programador Junior
Parobé RS
[]s
Programador Junior
Parobé RS
[]s
This error means that Oracle does not know which table he must fetch the value.
You must define in which table you want to search the rownum.
I believe this should be solved:
Remembering that in this example I seek the Topn Rownum
You must define in which table you want to search the rownum.
I believe this should be solved:
SELECT *
FROM ( SELECT topn.*, topn.ROWNUM rnum FROM (
SELECT
-
- Rank: Estagiário Pleno
- Posts: 4
- Joined: Mon, 09 Aug 2010 3:14 pm
- Location: rio de janeiro - RJ
Thank you very much.
In the most internal query there was the same column twice at Query.
I had suppressed this when I posted the code because they are soooo columns.
So I was not in the code.
But your post made me think and check.
In the most internal query there was the same column twice at Query.
I had suppressed this when I posted the code because they are soooo columns.
So I was not in the code.
But your post made me think and check.
-
- Information
-
Who is online
Users browsing this forum: No registered users and 8 guests