ORA-00933错误排查：SQL在Java中执行失败但SQL Developer正常

核心原因：SQL末尾的分号 #

Java的PreparedStatement执行SQL时，不需要在语句末尾添加分号。你当前的SQL最后带有;，SQL Developer等客户端工具会自动忽略这个分号，但JDBC驱动会将其视为SQL语法的一部分，直接触发ORA-00933错误。

修复步骤 #

1. 移除SQL末尾的分号
   修改后的SQL字符串如下：

   String query2 = "SELECT  dock_id " +
                   "FROM ( " +
                   "SELECT  inventory_location_id AS dock_id " +
                   "FROM mtl_item_locations " +
                   "WHERE organization_id = 402 " +
                   "AND subinventory_code = 'me' " +
                   "AND inventory_location_type = 1 " +
                   "AND NOT EXISTS ( " +
                   "SELECT  1 " +
                   "FROM wms_dock_appointments_v " +
                   "WHERE dock_id = inventory_location_id " +
                   "AND ( start_time BETWEEN '30/05/2023' AND '05/06/2023' " +
                   "     OR end_time BETWEEN '30/05/2023' AND '05/06/2023' " +
                   "     OR '30/05/2023' BETWEEN start_time AND end_time " +
                   "     OR '30/05/2023' BETWEEN start_time AND end_time " +
                   "             )) " +
                   "ORDER BY inventory_location_id  " +
                   ") " +
                   "WHERE rownum = 1";
   

2. 优化建议：使用绑定变量处理日期
   硬编码日期字符串依赖Oracle会话的NLS_DATE_FORMAT设置，可能在不同环境下出现兼容问题，同时存在SQL注入风险。建议改用绑定变量传递日期参数：

   String query2 = "SELECT  dock_id " +
                   "FROM ( " +
                   "SELECT  inventory_location_id AS dock_id " +
                   "FROM mtl_item_locations " +
                   "WHERE organization_id = 402 " +
                   "AND subinventory_code = 'me' " +
                   "AND inventory_location_type = 1 " +
                   "AND NOT EXISTS ( " +
                   "SELECT  1 " +
                   "FROM wms_dock_appointments_v " +
                   "WHERE dock_id = inventory_location_id " +
                   "AND ( start_time BETWEEN ? AND ? " +
                   "     OR end_time BETWEEN ? AND ? " +
                   "     OR ? BETWEEN start_time AND end_time " +
                   "     OR ? BETWEEN start_time AND end_time " +
                   "             )) " +
                   "ORDER BY inventory_location_id  " +
                   ") " +
                   "WHERE rownum = 1";
   
   PreparedStatement stmt2 = connection.prepareStatement(query2);
   SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
   Date startDate = sdf.parse("30/05/2023");
   Date endDate = sdf.parse("05/06/2023");
   // 依次设置6个日期参数
   stmt2.setDate(1, new java.sql.Date(startDate.getTime()));
   stmt2.setDate(2, new java.sql.Date(endDate.getTime()));
   stmt2.setDate(3, new java.sql.Date(startDate.getTime()));
   stmt2.setDate(4, new java.sql.Date(endDate.getTime()));
   stmt2.setDate(5, new java.sql.Date(startDate.getTime()));
   stmt2.setDate(6, new java.sql.Date(startDate.getTime()));
   
   ResultSet rs2 = stmt2.executeQuery();
   

3. 验证拼接后的SQL
   可以在执行前打印拼接后的SQL字符串，和SQL Developer中运行的版本对比，确认是否存在空格缺失等语法问题。

内容的提问来源于stack exchange，提问作者Programming_guy