Re: RefCursor works in SQL*Plus but not JDBC

I just ran this code and it went OK...

import oracle.sql.*;
import oracle.jdbc.*;
import java.sql.*;
import java.util.*;

public class oraproc3
{
public static void main(String argv[]) throws Exception
{
java.sql.Connection conn = null;
try
{
java.util.Properties props = new java.util.Properties();

Driver d = new oracle.jdbc.OracleDriver();
props.put("user", "scott");
props.put("password", "tiger");

conn = d.connect("jdbc:oracle:thin:@JOE:1521:S9205", props);
System.out.println(conn.getMetaData().getDriverVersion() );

Statement st = conn.createStatement();

try{st.executeUpdate( "drop table joe");}catch(Exception ee){}
st.executeUpdate( "create table joe(foo int, bar int)");
st.executeUpdate( "INSERT INTO JOE VALUES (1,1)");
st.executeUpdate( "INSERT INTO JOE VALUES (2,2)");

st.executeUpdate (
" CREATE OR REPLACE PACKAGE test_pkg "
+ " as "
+ " TYPE testType is REF CURSOR; "
+ " PROCEDURE ECOM2 ( "
+ " outchar1 VARCHAR2, "
+ " outchar2 VARCHAR2, "
+ " outchar3 VARCHAR2, "
+ " data_list IN OUT testType "
+ " ); "
+ " end test_pkg; "
);

st.executeUpdate (
" CREATE OR REPLACE PACKAGE BODY test_pkg "
+ " as "
+ " procedure ECOM2 (outchar1 VARCHAR2, "
+ " outchar2 VARCHAR2, "
+ " outchar3 VARCHAR2, "
+ " data_list IN OUT testType) "
+ " IS "
+ " BEGIN "
+ " OPEN data_list FOR "
+ " SELECT * FROM JOE "
+ " where 1 = (SELECT 1 FROM DUAL); "
+ " END ECOM2; "
+ " end test_pkg; "
);

CallableStatement cs =
conn.prepareCall("{ call
SCOTT.test_pkg.ECOM2(?,?,?,?)}");

cs.setString(1,"qwe");
cs.setString(2,"qwe");
cs.setString(3,"qwe");
cs.registerOutParameter(4,OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet)(cs.getObject(4));
}
catch (Exception e) { e.printStackTrace(); }
finally // Always close connection, no matter what failure may have
occurred.
{ try {conn.close();} catch (Exception e) {} }
}
}

.