Oracle PL/SQL Query for Pagination

Oracle PL/SQL function makes the input query statement such a way that the query out put returns for a start row index to end row index.

CREATE OR REPLACE FUNCTION pagination_stmt_fn 
 (
  i_query_statement   IN   VARCHAR2,
  i_start_index       IN   NUMBER,
  i_end_index         IN   NUMBER
 )
 RETURN VARCHAR2
 IS
 o_query_statement   VARCHAR2 (32000);
 v_start_index       NUMBER           := 0;
 v_end_index         NUMBER           := 0;
BEGIN
 IF i_start_index = 0
 THEN
  v_start_index := 1;
 ELSE
  v_start_index := i_start_index;
 END IF;

 IF i_end_index = 0
 THEN
  v_end_index := 10000000;
 ELSE
  v_end_index := i_end_index;
 END IF;

 o_query_statement :=
  ' select  * from (  '
    || '  select result.*, rownum row_num  from ( '
    || i_query_statement
    || ' ) result ) '
  || ' where row_num between '
  || TO_CHAR (v_start_index)
  || ' and '
  || TO_CHAR (v_end_index);

RETURN o_query_statement;

EXCEPTION
 WHEN OTHERS
 THEN
 RETURN NULL;
END pagination_stmt_fn;
/

/************************************************************************************************

--In put parameters
-- i_query_statement:  "SELELCT * FROM EMP ORDER BY EMPLOYEE_ID"   
   (assumption:   Table EMP is exist and contains some rows)

-- i_start_index: 10
--i_end_index: 25


select pagination_stmt_fn( 'SELECT * FROM EMP ORDER BY EMPLOYEE_ID',10,25) "pagination statement" from dual;


--out put on SQL* Plus window

pagination statement
--------------------------------------------------------------------------------
 select  * from (    select result.*, rownum row_num  from ( SELECT * FROM EMP 
ORDER BY EMPLOYEE_ID ) result )  where row_num between 10 and 25


*************************************************************************************************/