limit and oracle version

[Cvarak]

hi marco!

nice seeing you answer the questions posted! it seems like i have two, too:
1) i already looked for a kind of *limit* function in a select on the forum, but i'm not sure if i can use the aw.
well, i am making a web interface for some oracle tables, and i would find it handy to "flip" pages for found results or just browsing, showing rows 1 to 30 on first page, 31 to 60 on the second etc. is there an intelligent or at least effcient way to do it with TOraclQuery? i'm not so happy with fetching the rows in a loop till wanted counter reached..

2) how can i get the version of the running oracle client?

nice greets,
cvarak

 

[Marco Kalter]

i'm not so happy with fetching the rows in a loop till wanted counter reached..

I assume you want to know how many records a query will return, without actually fetching all of them. You could do a select count(*) from ().

2) how can i get the version of the running oracle client?

After connecting to the database, or after calling InotOCI, the global OCIDLL variable contains the full path to the Oracle Client library. You can obtain the file version from this DLL (don't ask me how). Both InitOCI and OCIDLL are located in the OracleCI unit.

 

[Cvarak]

Originally posted by Marco Kalter:

i'm not so happy with fetching the rows in a loop till wanted counter reached..

I assume you want to know how many records a query will return, without actually fetching all of them. You could do a select count(*) from ().

hi marco!
thanks for a right way to get the version, i'll check it out.
ad 1) yep i must get count(*) before dealing with a select, yet my question was:
let's say i have , and getting the count(*) results with count=50,000.
so, i don't want to show all 50,000 rows, but only
row 32000 to row 32050.

this is usually called LIMIT command and would be used (in a db capable of) like:
or similar -you get the point.
so how to do it without "skipping" first 32000-1 rows (by fetcihng) in a loop, but
"jumping" to wanted row, or at least to do skipping without "real" fetching - into the variables.
is it possible?

nice greets,
cvarak

 

[Marco Kalter]

You could use a scrollable query (TOracleQuery.Scrollable = True), but this requires Oracle 9.2 or later. For a normal query you can only fetch the Next record, but a scollable query allows you to navigate to any absolute (First, Last, MoveTo) or relative (Prior, MoveBy) position in the result set.

 

[al0]

i'm not so happy with fetching the rows in a loop till wanted counter reached..

I assume you want to know how many records a query will return, without actually fetching all of them. You could do a select count(*) from ().
[/QUOTE]

For Oracle prior to 9.2 - no way, Oracle does not support such feature, for 9.2/10 you may use scrollable cursors (as Marco has mentioned) but their implementation is buggy in all Oracle versions that I

 

[walter.egger]

You could use an analytic function if you really want to do this.

Query would look like this:

select * from
(select emp.*, row_number() over (partition by '' order by empno) rn -- your order by
from emp -- your from clause
where empno>500) -- your where statement
where
rn between 5 and 10 -- rows you want to fetch

walter

 

[shegna]

How about a Delphi Client dataset? You can set Packetrecord = 30. Then call GetNextPacket to get the next 30 records. A thought