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 *************************************************************************************************/
This blog is great check it out
Oracle Training in Medavakkam / Best Oracle Training in Medavakkam
Oracle Training Course in Chennai / Best Oracle Training Institute in Chennai