CODEWITHSUNDEEP
oracle-databasedynamic-sql
Lucy82
Lucy82

Reputation: 693

Pagination in Oracle with dynamic query

I have a query that returns large amount of data. When users search for data in larger date ranges It can come up to 5 millions records, and when that happens app freezes for certain amount of time.

So I decided to do a pagination of query result, but I'm trying to do that without any DB changes. Let me explain:

I have a stored procedure for retrieving all data, with dynamic SQL. Input parameters are 3 :

In example, my procedure for calling query looks like this:

first_parameter || ' FROM (...here is my query where all possible fields are selected with joins, db links etc.... ) WHERE ' || second_parameter

So, I can modify start and end of query, and now I would like to modify It to enable pagination too, for let's say max 1.000 records at a time.

I have tried to modify It in C# by adding a rownum in first_parameter along with selected fields

(Select rownum, field1,field2 etc...)

, and using

rownum < 1000 in a WHERE clause

. This actually works, but for a pagination I would need also to tell what row for start and end, like

WHERE rownum > 1000 AND rownum < 2000

, but this doesn't work.

How can I do this in Oracle 11g, where OFFSET is not available, can somebody show me ? I would prefer a solution without changing query inside of parameters If possible, since I can send everything I want into both parameters in question from C# in code (Varchar values ofcourse).

P.S. : I have never done a pagination so forgive me If I wrote some silly things ;)

Upvotes: 1

Views: 1742

Answers (2)

Adam vonNieda
Adam vonNieda

Reputation: 1745

For true pagination, you should look into Oracle REST Data Services (ORDS) - It's free, easy to set up, and has pagination built in.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142958

One option is to calculate row number for each row, e.g.

with ycq as
  -- your current query goes here
  (select col1, 
          col2, ..., 
       row_number() over (order by col1) rn         --> this is new ...
   from ...
   where ...
  )
select y.col1, 
       y.col2,
       ...
from ycq y
where y.rn between :lower_value and :upper_value    --> ... and is used here
order by y.some_col

Upvotes: 1

Related Questions