javaoraclejdbcmultiple-resultsets

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


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 ?


Solution

  • 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.