OraFAQ Forum: SQL & PL/SQL » limit the returned size of query

Home » SQL & PL/SQL » SQL & PL/SQL » limit the returned size of query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

limit the returned size of query [message #252998]

Sat, 21 July 2007 06:18

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!

Report message to a moderator

Re: limit the returned size of query [message #253002 is a reply to message #252998]

Sat, 21 July 2007 07:28

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: limit the returned size of query [message #253024 is a reply to message #252998]

Sat, 21 July 2007 10:15

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 13:14:08 CST 2024