different results from query in java code and on oracle db server: but no connection error

Question

I have java code that connects to a remote oracle 11g EE db server. If i run a particular query in sqlplus it returns one result

SQL> SELECT COURSENAME from COURSES where skillID=1;

COURSENAME
--------------------
basic

But if I run the same query from the java code below it returns no results. I can copy the query syntax out of the query variable in the java debugger and running it on oracle so I know there is no syntax issue with the query. Also, it is not SQL exceptions or class not found exceptions so it seems to be running the query successfully -- just returning zero results.

What might be going on?

    private String getCourseForSkill(int skillID){
    try{
        Class.forName("oracle.jdbc.OracleDriver"); 
        String query="SELECT COURSENAME from COURSES where skillID=" + skillID ; 
        con = DriverManager.getConnection(url, user, password);
        Statement stmt = con.createStatement();
        rs = stmt.executeQuery(query);
        rs.next();
        return rs.getString("COURSENAME");
    }
    catch (ClassNotFoundException ex){
        System.out.println(ex.getMessage());
    }
    catch (SQLException ex) {
         System.out.println(ex.getMessage());
    }
    return null;
}

Answer 1

1. I think you're connecting to different Oracle instances, or more likely, as different Oracle users in the two cases

   @GreyBeardedGeek the URL looks like "jdbc:oracle:thin:@website:port:orcl I get to the manual query by doing ssh@website, authenticating and then running command=sqlplus

   Safer to run sqlplus <username>/<password>@<orainstancename>, because you can explicitly specify the oracle instance ID. In your case, it seems your program is using jdbc connection jdbc:oracle:thin:@website:port:orcl, so your orainstancename would be 'orcl' - just ensure that your tnsnames.ora file has the instance 'orcl' with the same 'port' as used by the jdbc connection

2. How to debug a little more

   Run the following code:

   con = DriverManager.getConnection(url, user, password);
   con.setAutoCommit(false);
   String insert="INSERT INTO COURSES (SKILLID, COURSE)"+ // can add other columns
                "values (?, ?) );"                       // add ? for other columns    
   PreparedStatement ps = con.createPreparedStatement();
   ps.setInt(1, 999);
   ps.setString(2, "Complete Bullwarks");
   // can set other columns
   ps.executeUpdate(insert);
   con.commit();
   

   NOW connect manually, re-run your original select statement & see if the added row is there. If no error in java and no new row in Oracle: extremely likely you're using 2 different Oracle instances/schemas.

   ALSO rerun your original java select code, but with SkillID=999 - extremely likely it will work.

Cheers