How do I paginate the output of a callable statment/prepared procedure in my JSP?

Question

I have this procedure in one of the packages:

PROCEDURE get_namelist
  (
    return_code_out OUT VARCHAR2,
    return_msg_out OUT VARCHAR2,
    id_no_in IN employee.id_no%TYPE,
    name_out OUT employee.name%TYPE,
        addr_out OUT employee.addr%TYPE
  )
  IS

  BEGIN

    return_code_out := '0000';
    return_msg_out := 'OK';

    SELECT i.name, i.addr INTO name_out, addr_out
      FROM employee i
     WHERE i.id_no = id_no_in;

END get_namelist;

I am calling this procedure like this in my Java:

DbUtil db = null;
java.sql.CallableStatement cstmt = null;


db = new DbUtil();
cstmt = db.prepareCall("{ call jack.PACK_EMPLOYEE.get_namelist(?, ?, ?, ?, ?) }");

cstmt.registerOutParameter(1,OracleTypes.VARCHAR);
cstmt.registerOutParameter(2,OracleTypes.VARCHAR);
cstmt.setString(3,this.getIdNo());
cstmt.registerOutParameter(4,OracleTypes.VARCHAR);
cstmt.registerOutParameter(5,OracleTypes.VARCHAR);
stmt.execute();

this.setName(cstmt.getString(4));
this.setAddress(cstmt.getString(5));

Currently it's pulling all data from the table. I want to change this, so it will pull a certain number of rows into multiple pages. I looked all over and found out how to do JSP pagination when I am using the SQL query in Java code itself, but I coudn't find any example that shows this using package and callable statement.

How can I modify this code so I can use JSP pagination?

Answer 1

Add Page Size and Page Number to your Procedure.

Add Row numbering over your query, and then return the records where row number > ((page size - 1) * page number + 1) and row number <= (page size * page number)

If you need a more low level answer, I can translate this into the relevant code.

---

DISCLAIMER: My Oracle Syntax may be terrible. Refer to Oracle ROW_NUMBER description (very last example) for how I attempted to construct this.

//procedure

PROCEDURE get_namelist
  (
    return_code_out OUT VARCHAR2,
    return_msg_out OUT VARCHAR2,
    id_no_in IN employee.id_no%TYPE,
    page_num IN (int datatype),              
    page_size IN (int datatype),             
    name_out OUT employee.name%TYPE,
       addr_out OUT employee.addr%TYPE,
  )
  IS

  BEGIN

    return_code_out := '0000';
    return_msg_out := 'OK';

    SELECT i.name, i.addr INTO name_out, addr_out, 
         FROM 
             (SELECT e.name, e.addr,
              ROW_NUMBER() OVER (ORDER BY e.name) as row           
              FROM employee e
              WHERE e.id_no = id_no_in) i
         WHERE row between ((page_num - 1) * page_size) AND (page_num * page_size);

END get_namelist;

//java

DbUtil db = null;
java.sql.CallableStatement cstmt = null;


db = new DbUtil();
cstmt = db.prepareCall("{ call jack.PACK_EMPLOYEE.get_namelist(?, ?, ?, ?, ?, ?, ?) }");

cstmt.registerOutParameter(1,OracleTypes.VARCHAR);
cstmt.registerOutParameter(2,OracleTypes.VARCHAR);
cstmt.setString(3,this.getIdNo());
cstmt.setString(4,pagenum);
cstmt.setString(5,pagesize);
cstmt.registerOutParameter(6,OracleTypes.VARCHAR);
cstmt.registerOutParameter(7,OracleTypes.VARCHAR);
stmt.execute();

this.setName(cstmt.getString(4));
this.setAddress(cstmt.getString(5));

You call the procedure through Java with a different pagenum whenever you need a different page than the one displayed.