// -------------------------------------------------------------------- // Sample SQLJ program that fetches from a REF CURSOR // retuned from a package. // Frank Naude - Apr 2003 // -------------------------------------------------------------------- // -------------------------------------------------------------------- // Database declaration: // // CREATE OR REPLACE PACKAGE emp_pkg AS // TYPE emp_cursor_type IS REF CURSOR RETURN EMP%ROWTYPE; // FUNCTION getRefCursor RETURN emp_cursor_type; // END emp_pkg; // / // // CREATE OR REPLACE PACKAGE BODY emp_pkg AS // FUNCTION getRefCursor RETURN emp_cursor_type IS // emp_cur emp_cursor_type; // BEGIN // OPEN emp_cur FOR SELECT empno, ename FROM EMP; // RETURN emp_cur; // END getRefCursor; // END emp_pkg; // / // // -------------------------------------------------------------------- import sqlj.runtime.*; import sqlj.runtime.ref.*; import java.sql.*; public class SQLJTst6 { #sql public static iterator EmpIter (int empno, String ename); public static void main(String[] args) { Connection c = null; String URL = "jdbc:oracle:thin:@localhost:1521:orcl"; String userid = "scott"; String passwd = "tiger"; try { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); c = DriverManager.getConnection(URL, userid, passwd); System.out.println("Successfully conencted to Oracle..."); // You need to set a Context for SQLJ, otherwise - // SQLException: found null connection context DefaultContext.setDefaultContext( new DefaultContext(c)); // Get REF CURSOR from database EmpIter iter; #sql iter = { VALUES(emp_pkg.getRefCursor()) }; // Fetch values from REF CURSOR while (iter.next()) { String empname = iter.ename(); int empnum = iter.empno(); System.out.println("Empno# " + empnum + ", ename= " + empname); } iter.close(); // Close the database connection #sql { rollback work }; c.close(); System.out.println("Disconected..."); } catch (SQLException e) { e.printStackTrace(); } } }