Empty ResultSet after executing PreparedStatement

Question

Hi,

When I execute sql query SELECT * FROM TABLE (TEST.getDevices()) in SqlDeveloper I've got about 200 rows, but when I try to execute it in java:

//cut here
String query = "SELECT * FROM TABLE (TEST.getDevices())"; 
PreparedStatement stmt = null; 
ResultSet rset = null; 
try { 
    stmt = oracleConnection.prepareStatement(query); 
    rset = stmt.executeQuery(); 
    rset.next(); 
    System.out.println(rset.getInt(1));
//cut here

I get empty ResultSet and so on the exception is thrown. I've tried query SELECT Count(*) a FROM TABLE (TEST.getDevices()) In SqlDeveloper result is 200, in java app is 0.

What could be reason that get empty ResultSet in ma app?

Answer 1

Have you check the DB connection.

Answer 2

I assume, that TEST.getDevices() return TYPE "TABLE OF" or getDevices is PIPELINED FUNCTION? If true, you can try this trick:

CREATE VIEW VIEW_DEVICES AS SELECT * FROM TABLE(TEST.getDevices());

Than it shoud work from Java and SQLDeveloper:

DESC VIEW_DEVICES;

or select statement

SELECT * FROM VIEW_DEVICES;

You can replace you query to:

String query = "SELECT * FROM VIEW_DEVICES";

Or you can try search in google something like "how to map varray in java from oracle".