<html><head><meta name="color-scheme" content="light dark"></head><body><pre style="word-wrap: break-word; white-space: pre-wrap;">// testPLSQL.java
//
// This class demonstrates calling an Oracle PLSQL function that returns a 
// database cursor, which is used as a result set.
// 
// The following commented SQL can be issued in the SQLPLUS (with the comments removed)
// to create the tables and PLSQL function used in this example.
//
// create table emp ( c1 varchar2(100), c2 varchar2(100));
// insert into emp values ('1','1');
// insert into emp values ('2','2');
//
//    rem create function that returns a cursor
//    
//    CREATE OR REPLACE PACKAGE jdbcpack2 IS TYPE rctl IS REF CURSOR return emp%ROWTYPE; END;
//    /
//    
//    create or replace function jdbctest2 ( n integer) 
//      return jdbcpack2.rctl is retval jdbcpack2.rctl;
//            begin
//     		open retval for 
//            	select * from emp;
//            return(retval);
//            end;
//     
//    /

import java.sql.*;
import java.io.*;

class testPLSQL
    {

    testPLSQL(java.sql.Connection conn)
        {
        m_conn = conn;
        }
    
    public void test() throws SQLException
        {
        System.out.println("testPLSQL.test");
        CallableStatement mycall = m_conn.prepareCall("{call jdbctest2(?)}");
        mycall.setInt(1, 0);
        ResultSet rs = mycall.executeQuery();
        while(rs.next())
            {
            System.out.println(rs.getString(1) + ", " + rs.getString(2));
            }
        }

    public void test2() throws SQLException
        {
        System.out.println("testPLSQL.test2");
        CallableStatement mycall = m_conn.prepareCall("{call jdbctest2(?)}");
        mycall.setInt(1, 0);
        mycall.execute();    // let the execute figure out what we are calling
        ResultSet rs = mycall.getResultSet();
        while(rs.next())
            {
            System.out.println(rs.getString(1) + ", " + rs.getString(2));
            }
        }

    java.sql.Connection m_conn;

    }</pre></body></html>