Home » SQL & PL/SQL » SQL & PL/SQL » limit the returned size of query
limit the returned size of query [message #252998] Sat, 21 July 2007 06:18 Go to next message
ntttuyen
Messages: 2
Registered: September 2006
Junior Member
Dear!
I am new to oracle. I really appreciate if having responds from you.
I am developing an application using Java for programing and Oracle for storing data. User can query the data using my application.

And to reserve the resource, the size of result set (number of rows) must be smaller than a specified number. If larger, an exception will be returned instead of the actual result.

I know that class Statement in Java provides an operation setMaxRows(int number). However, this operation just silently dropped the exceeded rows without notification. I want to ask if there is any way in Oracle to check the size of query before actually execute it.

I tried with select count(*) to get the size first, but in this way we need an additional scan of database. Therefore it is not efficient.

Is there any mechanism that I can configure the database to get an announcement when the size of a query is larger than expected? Please help! Thank you very much!
Re: limit the returned size of query [message #253002 is a reply to message #252998] Sat, 21 July 2007 07:28 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
... the size of result set (number of rows) must be smaller than a specified number

This is easily done using the ROWNUM pseudocolumn. For example, if a "specified number" = 5, you'd write a query as
SELECT empno, ename
FROM emp
WHERE rownum < 5;
Try it and see how it works. Just make sure you use "less than <" operator (or, eventually, <=); "equal =" or "greater than >" will result in an empty result set (i.e. you won't fetch any records).
Re: limit the returned size of query [message #253024 is a reply to message #252998] Sat, 21 July 2007 10:15 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't know how many rows will return a statement without first executing it and fetching all its rows.
But you can retrieve your rows array per array to limit the resource consumption (method setDefaultRowPrefetch or setRowPrefetch ) and stop after the number of fetches you need.

Regards
Michel

Previous Topic: utl_file
Next Topic: The following error is also happening on mirror db only: ORA-12705 or ORA-12702 errors
Goto Forum:
  


Current Time: Sun Dec 11 06:30:25 CST 2016

Total time taken to generate the page: 0.04051 seconds