import java.sql.*;
import oracle.sqlj.runtime.Oracle;

public class RefCursDemo
{
  #sql public static iterator EmpIter (String ename, int empno);

  public static void main (String argv[]) throws SQLException
  {
    String name;  int no;
    EmpIter emps = null;

    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */

    /* Connect to the database */
    Oracle.connect(RefCursDemo.class, "connect.properties");

    name = "Joe Doe"; no = 8100;
    emps = refCursInAnonBlock(name, no);
    printEmps(emps);

    name = "Jane Doe"; no = 8200;
    emps = refCursInStoredProc(name, no);
    printEmps(emps);

    name = "Bill Smith"; no = 8300;
    emps = refCursInStoredFunc(name, no);
    printEmps(emps);
   }

  private static EmpIter refCursInAnonBlock(String name, int no) 
    throws java.sql.SQLException {
    EmpIter emps = null;    

    System.out.println("Using anonymous block for ref cursor.."); 
    #sql { begin
             insert into emp (ename, empno) values (:name, :no);
             open :out emps for select ename, empno from emp
           	                order by empno;
           end;
         };
    return emps;
  }

  private static EmpIter refCursInStoredProc (String name, int no)
    throws java.sql.SQLException {
    EmpIter emps = null;
    System.out.println("Using stored procedure for ref cursor.."); 
    #sql { CALL SQLJREFCURSDEMO.REFCURSPROC (:IN name, :IN no, :OUT emps)
         };
    return emps;
  }

  private static EmpIter refCursInStoredFunc (String name, int no) 
    throws java.sql.SQLException {
    EmpIter emps = null;    
    System.out.println("Using stored function for ref cursor.."); 
    #sql emps = {  VALUES (SQLJREFCURSDEMO.REFCURSFUNC(:name, :no))
                };
    return emps;
  }

  private static void printEmps(EmpIter emps) 
    throws java.sql.SQLException {
    System.out.println("Employee list:");
    while (emps.next()) {
       System.out.println("\t Employee name: " + emps.ename() + 
			  ", id : " + emps.empno());
    }
    System.out.println();
    emps.close();
  }
}