SOLUTION:
The fundamental basis of this solution is the following query:
SELECT
TMP.RN,
TMP.TITLE1,
TMP.DOCUMENT_NUMBER
FROM
(
SELECT
ROWNUM RN,
TITLE1,
DOCUMENT_NUMBER
FROM
REFERENCES
WHERE
TITLE1 LIKE '%BLOOD%'
) TMP
WHERE
TMP.RN BETWEEN 41 AND 60;
once you see that works the following solution comes out of it:
CREATE OR REPLACE FUNCTION "GETTITLEREFCOUNT" (v_title IN VARCHAR2)
RETURN INTEGER
IS
rowcount INTEGER;
BEGIN
select Count(ROWID) TOTAL into rowcount from REFERENCES where
TITLE1 LIKE '%' || v_title || '%';
RETURN rowcount;
END GETTITLEREFCOUNT;
--------------------------------------------------------------------------
-
CREATE OR REPLACE FUNCTION "GETDOCNUM" (
v_title IN VARCHAR2,
v_startrow IN INTEGER,
v_endrow IN INTEGER
)
RETURN Types.ref_cursor
AS
title_cursor types.ref_cursor;
title_search varchar2(200);
v_query varchar2(500);
BEGIN
title_search :='%' || v_title || '%';
v_query:='SELECT TMP.RN, TMP.TITLE1, TMP.DOCUMENT_NUMBER FROM
(SELECT ROWNUM RN, TITLE1, DOCUMENT_NUMBER FROM references where TITLE1
like :1) TMP WHERE TMP.RN BETWEEN '|| v_startrow ||' and ' || v_endrow;
dbms_output.put_line(v_query);
OPEN title_cursor FOR
v_query using title_search;
RETURN title_cursor;
END;
--------------------------------------------------------------------------
--
import oracle.jdbc.OracleTypes;
import java.util.*;
import java.sql.*;
import javax.sql.*;
/**
*
* @author cwgraham
*/
public class TestStoredProc {
private static final int PAGESIZE = 20;
private static final int PAGENUMBER = 3;
private static final String TITLESEARCH = "BLOOD";
/** Creates a new instance of TestStoredProc */
public TestStoredProc() {
}
private Connection makeDBConnection() throws SQLException,
ClassNotFoundException
{
String
JDBC_CONNECTION_STRING="jdbc

racle:thin:@host:1521:km01";
String username= "user";
String password= "xxxxx";
Class.forName("oracle.jdbc.driver.OracleDriver");
return
DriverManager.getConnection(JDBC_CONNECTION_STRING,username,password);
}
public int getRowcount(String title, Connection conn) throws
SQLException
{
String query="{ call ? := gettitlerefcount(?) }";
CallableStatement stmt = conn.prepareCall(query,
java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
java.sql.ResultSet.CONCUR_READ_ONLY);
// register the type of the out param - an Oracle specific type
stmt.registerOutParameter(1, OracleTypes.INTEGER);
// set the in param
stmt.setString(2, title); //title
// execute and retrieve the result set
stmt.execute();
return stmt.getInt(1);
}
public int calcStartPos(int pagenum, int recordcount, int pagesize)
{
return (pagesize*(pagenum-1))+1;
}
public int calcEndPos(int pagenum, int recordcount, int pagesize)
{
return (pagesize*pagenum);
}
public ResultSet getPage(int pagenum, int recordcount, int pagesize,
String title, Connection conn) throws SQLException
{
String query="{ call ? := getdocnum(?,?,?) }";
CallableStatement stmt = conn.prepareCall(query,
java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
java.sql.ResultSet.CONCUR_READ_ONLY);
// register the type of the out param - an Oracle specific
type
stmt.registerOutParameter(1, OracleTypes.CURSOR);
// set the in param
stmt.setString(2,title); //title
stmt.setInt(3,this.calcStartPos(pagenum,recordcount,pagesize)); //title
stmt.setInt(4,this.calcEndPos(pagenum,recordcount,pagesize));
//title
// execute and retrieve the result set
stmt.execute();
return (ResultSet)stmt.getObject(1);
}
private void displayResults(ResultSet rs, int start) throws
SQLException
{
while(rs.next())
{
System.out.println("#"+start+":"+rs.getString("DOCUMENT_NUMBER")+":
"+rs.getString("TITLE1"));
start++;
}
}
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
TestStoredProc sptest = new TestStoredProc();
try{
Connection conn = sptest.makeDBConnection();
if(conn.getMetaData().supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_I
NSENSITIVE, ResultSet.CONCUR_UPDATABLE))
{
System.out.println("options concurrency are
supported");
}
if(conn.getMetaData().supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSIT
IVE)) {
System.out.println("options type are supported");
}
int count = sptest.getRowcount(TITLESEARCH, conn);
System.out.println("found "+count+" records
containing:"+TITLESEARCH);
System.out.println("starting record for page
#"+PAGENUMBER+"="+sptest.calcStartPos(PAGENUMBER,count,PAGESIZE));
System.out.println("ending record for page
#"+PAGENUMBER+"="+sptest.calcEndPos(PAGENUMBER,count,PAGESIZE));
ResultSet rs = sptest.getPage(PAGENUMBER, count, PAGESIZE,
TITLESEARCH, conn);
sptest.displayResults(rs,
sptest.calcStartPos(PAGENUMBER,count,PAGESIZE));
conn.close();
}
catch(ClassNotFoundException cnfex)
{
System.out.println("error:"+cnfex.getMessage());
}
catch(SQLException sex)
{
System.out.println("error:"+sex.getMessage());
}
}
}
---------------------------------------------------
[ December 03, 2003: Message edited by: Clay Graham ]