Steve, This was it exactly. Thanks!
John P Weatherman Database Administrator Replacements Ltd. -----Original Message----- Sent: Tuesday, December 03, 2002 2:44 PM To: Multiple recipients of list ORACLE-L John - your problem is that you are reusing the Statement named myStatement within your while (myTablespaces.next()) loop. just declare and use a second Statement and i suspect all will be well once again. from http://java.sun.com/j2se/1.3/docs/api/index.html : java.sql Interface Statement All Known Subinterfaces: CallableStatement, PreparedStatement public interface Statement The object used for executing a static SQL statement and obtaining the results produced by it. Only one ResultSet object per Statement object can be open at any point in time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All statement execute methods implicitly close a statment's current ResultSet object if an open one exists. -----Original Message----- Sent: Tuesday, December 03, 2002 12:25 PM To: Multiple recipients of list ORACLE-L All, I am beginning the journey into JAVA and have hit an odd behavior (well, probably not, but I can't see any reason for it). I am building a list of tablespaces and the datafiles that belong to them. The open to the database is working fine. When I use: ResultSet myTablespaces = myStatement.executeQuery( "SELECT tablespace_name " + "FROM dba_tablespaces " + "WHERE contents = 'PERMANENT'" ); while (myTablespaces.next()) { // retrieve the user from the row in the ResultSet using the // getString() method ct = ct + 1; String tablespace = myTablespaces.getString(1); System.out.println("Tablespace " + ct + " is: " + tablespace); } myTablespace.close(); I am generating a list of tablespaces and the output is as expected. When I add a second result set, the first datafile of the first tablespace returns and then the program completes: ResultSet myTablespaces = myStatement.executeQuery( "SELECT tablespace_name " + "FROM dba_tablespaces " + "WHERE contents = 'PERMANENT'" ); while (myTablespaces.next()) { // retrieve the user from the row in the ResultSet using the // getString() method ct = ct + 1; String tablespace = myTablespaces.getString(1); System.out.println("Tablespace " + ct + " is: " + tablespace); ResultSet myDataFiles = myStatement.executeQuery( "SELECT file_name " + "FROM dba_data_files " + "WHERE tablespace_name = '" + tablespace + "'" ); while (myDataFiles.next()) { String filename = myDataFiles.getString(1); System.out.println(" " + filename); } myDataFiles.close(); } myTablespaces.close(); Anybody with some Java experience have any insite? As always, TIA, John P Weatherman Database Administrator Replacements Ltd. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: STEVE OLLIG INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Weatherman INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
