package project1;

import java.io.*;
import java.sql.*;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.*;



public class Main {

public static void main(String[] args)
throws SQLException
{

Connection conn = null;
OracleDataSource ods = new OracleDataSource();
ods.setUser("www");
ods.setPassword("www");
ods.setURL("jdbc:oracle:thin:@192.168.1.245:1521:ORCL");
conn = ods.getConnection();

CallableStatement cs;
cs = conn.prepareCall("{call www.pkg_test.get_cur}");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
ResultSet set;
set = (ResultSet) cs.getObject(1);
while (set.next()) {
System.out.println(set.getString(1));
}

set.close( );
cs.close( );
conn.close( );
//System.out.println (conn.toString());
}
}

А вот и пакет
CREATE OR REPLACE PACKAGE BODY PKG_TEST IS

PROCEDURE GET_INT(S OUT NUMBER) AS
BEGIN
S := 1000;
END;

PROCEDURE GET_CUR(C1 OUT SYS_REFCURSOR) AS
BEGIN
OPEN C1 FOR
SELECT * FROM SYS.V_$DATABASE;
END;

END PKG_TEST;