JDBC : returning multiple result sets via a single database invocation - not working for Oracle

Question

This post showed executing multiple queries in a single JDBC invocation (against a SQL Server database) by separating them with semicolons. When I tried to do the same with Oracle 10G, an error "invalid character" propped up :

class db
{
    public static void main(String aa[])throws Exception
    {
        Class.forName("oracle.jdbc.driver.OracleDriver"); 
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//192.168.10.29:1521/ttt","username","password");
        PreparedStatement stat = conn.prepareStatement("select voila from app where rownum<4; select code from process where rownum<4");
        stat.execute();
        while (stat.getMoreResults()){
            ResultSet rs = stat.getResultSet();
            while (rs.next()){
                System.out.println(rs.getString(1));        
            }
        }
        conn.close();
    }
}

What am I doing wrong ?

Quadrumanous

Answer 1

You are doing nothing wrong (except to assume that all DBMS work the same)

Oracle (and its JDBC driver) simply does not support this.

You need to run each SELECT individually.

Btw: this is one of the reason that some SQL injection attacks don't work with Orace - especially the famous "little bobby tables" cartoon.

Archdeaconry

Thanks. But is there any way such that I don't have to make 2 separate db calls ? – Quadrumanous

If the two selects return the same types you can use union. But it's a real BAD practice – Columbous

@Daud: no, you need two calls. – Archdeaconry

Answer 2

It's possible to get multiple result sets back from Oracle into JDBC in a single call. There are a few ways to do it; a good post at Oracle-Base shows how.

The mechanism I use is to make an anonymous block in a callable statement, then bind a SYS_REFCURSOR for each result set as an output parameter.

Here's some code that does just that. It's lazy for error handling, but it gets the idea across:

public void getMultiple() throws Exception {

    // get connection
    Connection conn = DriverManager.getConnection(TestConfig.JDBC_URL, TestConfig.DB_USERNAME, TestConfig.DB_PASSWORD);

    // here's the statement; it uses an anonymous block. In that block,
    // we've declared two SYS_REFCURSOR objects which are opened over our
    // SELECT statements.  Once the statements are opened, we bind the
    // SYS_REFCURSOR objects so they can be retrieved from JDBC
    String s =
            "DECLARE" +
            " l_rs1 SYS_REFCURSOR; " +
            " l_rs2 SYS_REFCURSOR; " +
            "BEGIN "+
            "   OPEN l_rs1 FOR " +
            "      SELECT 'Moose' FROM DUAL;" +
            "   OPEN l_rs2 FOR " +
            "      SELECT 'Squirrel' FROM DUAL; " +
            "   ? := l_rs1;" +
            "   ? := l_rs2;" +
            "END;";

    // prepare the callable statement, registering
    // the output parameter we want
    CallableStatement cs = conn.prepareCall(s);
    cs.registerOutParameter(1, OracleTypes.CURSOR);
    cs.registerOutParameter(2, OracleTypes.CURSOR);

    // execute the callable statement
    cs.execute();

    // retrieve the result sets by getting the bound output objects and
    // casting them to Java ResultSet objects
    ResultSet rs1 = (ResultSet) cs.getObject(1);
    ResultSet rs2 = (ResultSet) cs.getObject(2);

    // advance the first result set and print the string it yields
    rs1.next();
    System.out.printf("Result set 1 has '%s'\n", rs1.getString(1));

    // advance the second result set and print the string it yields
    rs2.next();
    System.out.printf("Result set 2 has '%s'\n", rs2.getString(1));

    // close everything up
    rs2.close();
    rs1.close();
    cs.close();
    conn.close();
}

I hope that helps you out!

Logography