Paging in the Oracle database with help of SQL Queries
With Different programming help, we need to pass the parameter to fetch the no of rows and starting point in oracle 12c sql queries.
In Oracle 12c, For Paging Query developed is little easy.
We can use the OFFSET and FETCH keyword to defined the Paging with help of SQL Queries
SELECT * FROM tables
ORDER BY column_id
OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY;
Example
--For First 10 rows
SELECT employee_id,first_name,last_name FROM hr.employees
ORDER BY employee_id
OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold
104 Bruce Ernst
105 David Austin
106 Valli Pataballa
107 Diana Lorentz
108 Nancy Greenberg
109 Daniel Faviet
110 John Chen
--For next 10 ROWS, you need to change offset to 10
SELECT employee_id,first_name,last_name FROM hr.employees
ORDER BY employee_id
OFFSET 11 ROWS FETCH NEXT 10 ROWS ONLY;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
111 Ismael Sciarra
112 Jose Manuel Urman
113 Luis Popp
114 Den Raphaely
115 Alexander Khoo
116 Shelli Baida
117 Sigal Tobias
118 Guy Himuro
119 Karen Colmenares
120 Matthew Weiss
Paging with help of Row_Number
With help of different languages, you have to pass the parameter n and m
Before 12c, we can use row_number for using paging functionality by taking one unique column in order by clause.
select * from (
select col_id,col_name,row_number() over (order by col_id) n
from table_name)
where rn between :n and :m order by rn;
Example:
SELECT * FROM
(select employee_id,first_name,last_name,row_number() over (order by employee_id) rn
from hr.employees) where rn between 1 and 10 order by rn;
EMPLOYEE_ID FIRST_NAME LAST_NAME RN
----------- -------------------- ------------------------- ----------
100 Steven King 1
101 Neena Kochhar 2
102 Lex De Haan 3
103 Alexander Hunold 4
104 Bruce Ernst 5
105 David Austin 6
106 Valli Pataballa 7
107 Diana Lorentz 8
108 Nancy Greenberg 9
109 Daniel Faviet 10
-- For next 10 rows, you can change between values
SELECT * FROM
(select employee_id,first_name,last_name,row_number() over (order by employee_id) rn
from hr.employees) where rn between 11 and 20 order by rn;
EMPLOYEE_ID FIRST_NAME LAST_NAME RN
----------- -------------------- ------------------------- ----------
110 John Chen 11
111 Ismael Sciarra 12
112 Jose Manuel Urman 13
113 Luis Popp 14
114 Den Raphaely 15
115 Alexander Khoo 16
116 Shelli Baida 17
117 Sigal Tobias 18
118 Guy Himuro 19
119 Karen Colmenares 20
Smart way of Technology 