java Oracle查询发出错误SQL命令未正确结束

1 周，4 日 Questions & Answers 81

我正在OracleDB上手动运行下面的查询，它运行良好，并给出了结果。问号正被正确的值所取代

DROP TABLE EventTable PURGE  CREATE GLOBAL TEMPORARY TABLE EventTable ( Module NVARCHAR2(512) NULL,EventType NVARCHAR2(512) NULL,ModuleAndEventText NVARCHAR2(512) NULL,Source NVARCHAR2(512) NULL,Severity NVARCHAR2(512) NULL,Node NVARCHAR2(512) NULL,UserSID NVARCHAR2(512) NULL,DesktopId NVARCHAR2(512) NULL,MachineId NVARCHAR2(512) NULL,FolderPath NVARCHAR2(512) NULL,LUNId NVARCHAR2(512) NULL,ThinAppId NVARCHAR2(512) NULL,EndpointId NVARCHAR2(512) NULL,UserDiskPathId NVARCHAR2(512) NULL,GroupId NVARCHAR2(512) NULL,EventID NUMBER NOT NULL,Time TIMESTAMP(6) NULL,Acknowledged NUMBER(38,0)NULL, PRIMARY KEY (EventID))  INSERT INTO EventTable ( Module,EventType,ModuleAndEventText,Source,Severity,Node,UserSID,DesktopId,MachineId,FolderPath,LUNId,ThinAppId,EndpointId,UserDiskPathId,GroupId,EventID,Time,Acknowledged ) SELECT Module,EventType,ModuleAndEventText,Source,Severity,Node,UserSID,DesktopId,MachineId,FolderPath,LUNId,ThinAppId,EndpointId,UserDiskPathId,GroupId,EventID,Time,Acknowledged FROM sdevent WHERE (UPPER(UserSID) = ?) AND (Time BETWEEN ? AND ?) AND ((UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?)) SELECT COUNT(*)  FROM EventTable E  LEFT OUTER JOIN ((SELECT* FROM ( SELECT sdevent_data.eventid, name, strValue FROM sdevent_data INNER JOIN EventTable ET ON sdevent_data.eventid=ET.eventid) SourceTable PIVOT (MAX(strValue) FOR name IN ('ApplicationId','MachineId','RDSServerId','FarmId','SessionId','UserDisplayName','DesktopDisplayName','ApplicationDisplayName','MachineName','FarmDisplayName','EndUserDisplayName','ThinAppDisplayName','ProcessName','RemoteApplicationId','RemoteApplicationDescription')) PivotTable)) strValueTable ON E.eventid=strValueTable.eventid LEFT OUTER JOIN (SELECT EventID,intValue FROM sdevent_data WHERE (Name = 'ProcessId')) ProcessId ON E.EventID = ProcessId.EventID LEFT OUTER JOIN (SELECT EventID,strBlobValue FROM sdevent_data WHERE (Name = 'TimingProfilerTree')) TimingProfilerTree ON E.EventID = TimingProfilerTree.EventID WHERE (UPPER(UserSID) = ?) AND (Time BETWEEN ? AND ?) AND ((UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?) OR (UPPER(EventType) = ?))

但当我从Java JDBC运行相同的查询时，它给了我一个SQL异常--

 java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)

下面是创建上述查询的java代码片段--

StringBuilder countQuery = new StringBuilder();

       countQuery.append(" DROP TABLE EventTable PURGE ");
       countQuery.append(" CREATE GLOBAL TEMPORARY TABLE EventTable ( ");
       countQuery.append(getTempTableAttributes());
       countQuery.append(" INSERT INTO EventTable ( ");
       countQuery.append(getEventTableAttributes());
       countQuery.append(" ) SELECT ");
       countQuery.append(getEventTableAttributes());
       countQuery.append(" FROM ");
       countQuery.append(this.eventTableName);
       if (def.filter != null) {
           countQuery.append(" WHERE ");
           countQuery.append(applyFilterToQuery(def.filter));
       }
       countQuery.append(" SELECT COUNT(*) ");
       countQuery.append(" FROM ");
       countQuery.append("EventTable E ");
       countQuery.append(getLeftJoinClause());
       if (def.filter != null) {
           countQuery.append(" WHERE ");
           countQuery.append(applyFilterToQuery(def.filter));
       }
       String query = countQuery.toString();

当我打印字符串查询时，它会打印上面的查询，效果很好。所以我不确定从Java运行时出了什么问题。是否有任何方法可以查看在Oracle数据库中运行的与MS SQL profiler类似的实际查询

Python中文网

有 Java 编程相关的问题?

java Oracle查询发出错误SQL命令未正确结束

共 (1) 个答案

# 1 楼答案