CREATE OR REPLACE FUNCTION F_PAGINATION(     
                                        P_TOTAL IN NUMBER,        
                                        P_PAGENUM IN NUMBER DEFAULT 0,        
                                        P_PAGEGROUP IN NUMBER DEFAULT 5)       
RETURN VARCHAR2       
IS       
 c_page CONSTANT NUMBER(1) := 1;       
        
 v_p NUMBER(3) := 0;       
 v_x NUMBER(3);       
 v_y NUMBER(3);       
 v_limit VARCHAR2(100) := '';        
BEGIN       
       
 IF P_PAGENUM > 0 THEN        
  v_p := P_PAGENUM;       
 ELSE       
  v_p := c_page;       
 END IF;       
        
 IF v_p IS NOT NULL AND (v_p > 0 AND v_p <= CEIL(P_TOTAL / P_PAGEGROUP)) THEN       
  v_p := v_p;       
 ELSE       
  v_p := c_page;       
 END IF;       
        
 v_y := P_PAGEGROUP;       
 v_x := (v_p - c_page) * v_y;       
 v_limit := 'OFFSET ' || v_x || ' ROWS FETCH NEXT ' || v_y || ' ROWS ONLY';       
           
 IF v_limit IS NOT NULL THEN       
  RETURN v_limit;       
 END IF;       
       
EXCEPTION                   
WHEN OTHERS                   
 THEN                   
  RETURN v_limit;            
END F_PAGINATION;

SELECT (SELECT COUNT(*) FROM HR.job_history) COUNT,  
       F_PAGINATION((SELECT COUNT(*) FROM HR.job_history),1,5)   
FROM DUAL

DECLARE   
 TYPE jobHisCur IS REF CURSOR;   
 v_cursor jobHisCur;   
 his_record HR.job_history%ROWTYPE;   
 v_total NUMBER(5) := 1000;   
 v_sql VARCHAR2(1024);   
 v_limit VARCHAR2(100);   
 v_min NUMBER(1);   
 v_max NUMBER(2) := 5;  
BEGIN   
  
/*   
 * Puede acceder al total de registros   
 * y asignarlo a la variable v_total   
 SELECT COUNT(*)    
 INTO v_total   
 FROM HR.job_history;   
*/   
   
 v_min := 1; -- Valor de la página a consultar que debe ser variable (1,2,3...n)  
  
 v_limit := F_PAGINATION(v_total,v_min,v_max);   
   
 -- DML    
 v_sql := 'SELECT * FROM HR.job_history ' || v_limit;   
   
 -- Abrir cursor    
 OPEN v_cursor FOR v_sql;   
    
 -- Recorrer cursor   
 LOOP   
  FETCH v_cursor INTO his_record;   
  EXIT WHEN v_cursor%NOTFOUND;   
  DBMS_OUTPUT.PUT_LINE (his_record.EMPLOYEE_ID||' '||his_record.START_DATE||' '||his_record.END_DATE||' '||his_record.JOB_ID||' '||his_record.DEPARTMENT_ID);   
 END LOOP;   
    
 -- Cerrar cursor   
 CLOSE v_cursor;    
EXCEPTION    
 WHEN OTHERS THEN   
  DBMS_OUTPUT.PUT_LINE('ERROR: un error ha ocurrido');   
END;