• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

How to define ResultSet limit by PL/SQl in Oracle database

 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all,
I am sharing my problem to all of you for need help to resolve it.
I am going to retrieve millions data from a table.Now I want to retrieve data
part by part wise by ResultSet, giving some example like,a table having 1000 of rows now I want to retrieve those data in 10 steps. like in one steps ResultSet limit 0 to 100, and after finish 1 step 2nd ResultSet limit come 101 to 200.
So if you have done like this or faced like it. please reply me.
 
Ranch Hand
Posts: 259
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The SQL to be used is database specific. Can you please mention the database [like MySQL or Oracle] you are using?
 
Marshal
Posts: 27214
87
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Karthi, the title of the post includes the phrase "in Oracle database". True, it would have been better to mention that in the body of the post, but at least it's somewhere.
 
author & internet detective
Posts: 41086
848
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Somenath,
Oracle provides a keyword called "rownum" that allows you to return each piece. This Ask Tom article gives a sample.
 
karthikeyan Chockalingam
Ranch Hand
Posts: 259
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Paul, It was my fault not to notice it in the title. I was concentrating only on the content.
[ June 09, 2008: Message edited by: karthi keyan ]
 
somenath chatterjee
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

Thanks for reply me. I have done with by rownum, its working. I am sending you the query please check it and let me know if any issues.

select * from ( select *, ROWNUM as rnum from table where ROWNUM <= "upperLimit" order by 1 ) where rnum > "lowerLimit"
 
Jeanne Boyarsky
author & internet detective
Posts: 41086
848
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

While this might work in some caises, it is risky in two ways:
1) You are ordering by whichever field is first. If the column order changes, the order by changes. Stating the column explicitly is safer.
2) The rownum <= upperlimit is not correct as it depends on the order by. This is functionally wrong.

See the Ask Tom article again, in particular the section titled "Pagination with ROWNUM". He shows the pattern to use is:
 
Straws are for suckers. Now suck on this tiny ad!
free, earth-friendly heat - a kickstarter for putting coin in your pocket while saving the earth
https://coderanch.com/t/751654/free-earth-friendly-heat-kickstarter
reply
    Bookmark Topic Watch Topic
  • New Topic