JDBC catching login failure exception and re-promt

try{
Class.forName ("oracle.jdbc.driver.OracleDriver"); // identify

Connection con = DriverManager.getConnection
 ("jdbc:oracle...",userid,password); 


// create Statement and execute sql statement after
} catch (SQLException ex) {
        Logger.getLogger(Transcript.class.getName()).log(Level.SEVERE, null, ex);
    }

The only exception I can find is SQLException, but how can it distinguish from login failure and incorrect sql statement?

I want it to re-promts the user for another chance of entering id and password, do I put that in the catch block?

if so, do i need another nested try-catch to handle the exception?

As I want to handle the two situations separately, but it seems like there is only a SQLException I can use.

Actually, you'll get a few more:

If you don't have the right Oracle driver in the classpath in Class.forName:
Exception in thread "main" java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

If DriverManager.getConnection fails because of a wrong jdbc url syntax:
Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:oracle...

If login/password fails at DriverManager.getConnection
Exception in thread "main" java.sql.SQLException: ORA-01017: invalid username/password; logon denied

In case of an incorrect SQL statement:
Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

In case of an incorrect SQL statement:
Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

In case of an incorrect SQL statement:
Exception in thread "main" java.sql.SQLException: Invalid column name

You can separate the login failure from SQL failures because they happen at different place in your code.

public class Oracle {

    public static void main(final String[] args) throws ClassNotFoundException, SQLException {
        final Connection con;

        try {
            Class.forName ("oracle.jdbc.driver.OracleDriver"); 
        } catch (final Exception e) {
            throw new RuntimeException("Driver failure");
        }

        try {
            con = DriverManager.getConnection ("jdbc:oracle:thin:schema/password@host:port:sid");
        } catch (final Exception e) {
            throw new RuntimeException("Login failure");
        }


        try {
            final Statement stmt = con.createStatement();

            final String sql = "select 1 from dual";
            final ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()) {
                // do something with the data
            }
            rs.close();
        } catch (final Exception e) {
            throw new RuntimeException("SQL failure");
        }
    }
}

Note: it is a brute-force example, in a production code you may need more precise exception handlers, and you shall ensure that resources are always closed. For example, in case of an SQL error while reading a resut set and having an error because of referring to an invalid column, you may need to close the statement as well as the result set.

Two more Oracle-specific hints:

Do not pass login and password as a param in DriverManager.getConnection. An Oracle connection has a lot more parameters, it is easier to construct a JDBC url which contains everything: "jdbc:oracle:thin:schema/password@host:port:sid"
- Note that host:port is needed. Many times multiple Oracle server instances can share the same port. You can use the service id (SID) to distinguish amongst them.
In case of a real Oracle exception, the exception is wrapped to an SQLException or an SQLSyntaxErrorException, sometimes this, sometimes that. I found pretty handy to put an own logic on top of this:
- I catch any exception with try..catch. If the error message starts with ORA-, then it is an Oracle error, and I wrap it to a custom exception class and re-throw.
- It might be handly to parse the Oracle error code in your custom exception. You can use that to differentiate between custom Oracle exceptions (-20000..-20999) what you throw in your PL/SQL (and can signal business level error). The rest of error codes always tell a technical error, i.e. something is wrong in your code or in the database structure.

To close a resource (pre-java7), use try..finally. Note the pessimistic handling of possible further exceptions when actually closing a resouce fails.

Statement stmt=null;
ResultSet rs=null;
try {
    try {
        final Statement stmt = con.createStatement();
        final String sql = "select 1 from dual";
        rs = stmt.executeQuery(sql);
        while(rs.next()) {
            // do something with the data
        }
        rs.close();
        rs=null;
        stmt.close();
        stmt=null;
    } catch (final Exception e) {
        throw new RuntimeException("SQL failure");
    }
} finally {
    if (rs!=null) {
        try {
            rs.close();
        } catch (Exception e) {
            // ignore - we can't do too much
        }
    }
    if (stmt!=null) {
        try {
            stmt.close();
        } catch (Exception e) {
            // ignore - we can't do too much
        }
    }
}

To close the resources on Java7 and above, you can benefit of the try with resource block. See How should I use try-with-resources with JDBC?