Your Location is: Home > Java

How to resolve error “ORA-00933: SQL command not properly ended” for this statement? [closed]

From: Sri Lanka View: 4007 oh-to 

Question

The error is ORA-00933: SQL command not properly ended. I'm not sure because this is my first time learning JDBC. I don't know why this gives me an error. Please let me know where something went wrong. And tell me how to fix it.

public static void main(String[] args) {
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        
        Connection con = DriverManager.getConnection(
                                    "jdbc:oracle:thin:@db.pknu.ac.kr:1521:xe", 
                                    "db201712041", "201712041");
        
        System.out.println("student number? ");
        Scanner sc = new Scanner(System.in);
        int sno = sc.nextInt();
        
        String sql = "select * from (student s natural join enrol e) join course c on e.cno=c.cno where sno=?";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setInt(1,sno);
    
        ResultSet rs = pstmt.executeQuery();
        
        while(rs.next()) {
            String no = rs.getString("enrol.cno");
            String name = rs.getString("cname");
            String grade = rs.getString("grade");
            
            System.out.format("과목번호: %s 과목이름: %s 성적: %s \n", no, name, grade);
            
        }
        
        sc.close();
        con.close();
        pstmt.close();
        rs.close();
        
    } catch (SQLException se) {
        System.err.println("SQL 수행중 에러가 발생했습니다." + se.getMessage());
    } catch (ClassNotFoundException cnfe) {
        System.err.println("드라이버 클래스를 찾을 수 없습니다." + cnfe.getMessage());
    } finally {

    }
}

Best answer

I think the problem is in your query, try this and tell me if it worked!

"select * from (student s natural join enrol e) as aux join course c on aux.cno=c.cno where sno=?"

Another answer

Isolating the problem to SQL

Your issue can be isolated to the SQL statement. I extracted and reformatted for better readability (multilined with keywords in caps):

SELECT *
FROM (student s NATURAL JOIN enrol e)
JOIN course c on e.cno = c.cno
WHERE sno = ?

Important for SQL problems: know your DBMS

From the special NATURAL JOIN and the error-message prefixed ORA- I suppose your DBMS is Oracle.

Troubleshooting ORA error-codes

The error you got:

ORA-00933: SQL command not properly ended

tells us by its error-code ORA-00933 that the Oracle SQL parser failed to interpret that SQL select statement, because it seems not ending correctly, e.g. something is missing.

Missing alias for derived table-expression ?

The Derived Table Expression (the join of two tables inside parentheses) you are using needs to have an alias appended, e.g. es (enrolled students) which can be used to refer to the result of the first two tables joined: (student s NATURAL JOIN enrol e) es

This aliased table-expression can then be referenced in the second JOIN condition by es to get the courses for the enrolled student: JOIN course c on es.cno = c.cno

Alternatively remove parentheses

SELECT *
FROM student s
NATURAL JOIN enrol e
JOIN course c on e.cno = c.cno
WHERE sno = ?

This is doing the three joins one after the other. The result should be the same.

Does the fixed SQL select statement work?