Search This Blog

Sunday, November 23, 2008

How to use reference cursor in java

<%@ page contentType="text/html;charset=windows-1252"%>
<%@ page import= "java.sql.*" %>
<%@ page import= "project1.*" %>
<%@ page import= "java.sql.CallableStatement" %>
<%@ page import= "oracle.jdbc.OracleTypes" %>
<%@ page import= "oracle.jdbc.driver.*" %>



try
{

String selection = "s";
String triple = "(?S ?P ?O)";
String urls = "(urlname='http://localhost/regression.rdf') OR (urlname='http://localhost/technical_girls_and_guys.rdf')";
String query_string = "plat";
int to_ = 1;
int from_ = 10;

JDBConnection jdbc = new JDBConnection();
CallableStatement cstmt = null;
ResultSet cursor = null;

cstmt = jdbc.conn.prepareCall("{call UOC_RDF_QUERY.BQUERY(?,?,?,?,?,?,?)}");

cstmt.setString(1,selection);
cstmt.setString(2,triple);
cstmt.setString(3,urls);
cstmt.setString(4,query_string);
cstmt.setInt(5,to_);
cstmt.setInt(6,from_);
cstmt.registerOutParameter(7,OracleTypes.CURSOR);
cstmt.execute();


cursor = (ResultSet) cstmt.getObject(7);
while (cursor.next())
{
System.out.println("Record: "+cursor.getString(1));

%>

<%

}

cstmt.close();
jdbc.conn.close();

}
catch (Exception e)
{
System.out.println(e.getMessage());
}

%>

How to use Refence Cursor in Oracle

--- Tested enviroment Oracle11g
---- Win Xp

CREATE OR REPLACE PACKAGE A1 IS
PROCEDURE test(result_ OUT SYS_REFCURSOR);
END A1;
/

CREATE OR REPLACE PACKAGE BODY A1
AS
PROCEDURE test(result_ OUT SYS_REFCURSOR)
IS
BEGIN
OPEN result_ FOR SELECT C1 FROM VARCHARTAB;
END test;
end ;
/

INSERT INTO VARCHARTAB VALUES (' Michael Georgiou');
commit;

declare
result_ SYS_REFCURSOR;
r varchar2(4000);
begin
A1.test(result_);
LOOP
FETCH result_ INTO r;
EXIT WHEN result_%NOTFOUND;
dbms_output.put_line(r);
END LOOP;
CLOSE result_;
end;
/