Hibernate: erro ao executar sql de consulta no Oracle

Assuntos Gerais
#1

Boa noite pessoal!

Ao tentar executar o sql abaixo no Oracle usando Hibernate está gerando o erro que está abaixo do sql.

SELECT CD_CGC_CPF,
       CD_BASE_CLIENTE,
       DT_ATUALIZACAO,
       DS_TIPO_PARECER,
       NM_USUARIO,
       ID_PARECER,
       DS_PARECER,
       DS_HORARIO,
       CD_FLUXO
  FROM (SELECT (TO_CHAR(ROWNUM, '00') || ' - dia ' ||
               TO_CHAR(T.DT_ATUALIZACAO, 'dd/mm/yyyy hh24:mi:ss')) as DS_HORARIO,
               F.CD_BASE_CLIENTE,
               F.CD_CGC_CPF,
               T.CD_SEQUENCIA_FLUXO CD_FLUXO,
               T.DT_ATUALIZACAO,
               NVL(U.NM_USUARIO, PR.DS_PROGRAMA) as NM_USUARIO,
               P.DS_TIPO_PARECER,
               DECODE(T.ID_SITUACAO_PARECER,
                      0,
                      'PENDENTE',
                      1,
                      'APROVADO',
                      2,
                      'APROVADO C/ EXCEÇÃO',
                      3,
                      'REPROVADO',
                      4,
                      'AGUARDANDO COMPLEMENTO',
                      'NÃO IDENTIFICADO') as ID_PARECER,
               T.DS_PARECER
          FROM PARECER_LIMITE_CRE_CLI   T,
               TIPO_PARECER_APV_CRE_CLI P,
               CTL_FLX_ALR_LIM_CRE_CLI  F,
               USUARIO                  U,
               PROGRAMA                 PR
         WHERE T.CD_TIPO_PARECER = P.CD_TIPO_PARECER
           AND T.CD_SEQUENCIA_FLUXO = F.CD_SEQUENCIA_FLUXO
           AND SUBSTR(T.CD_USUARIO, 1, 12) = SUBSTR(U.CD_USUARIO(+), 1, 12)
           AND SUBSTR(T.CD_USUARIO, 1, 12) = PR.CD_PROGRAMA(+)
           AND F.CD_BASE_CLIENTE = :CD_BASE_CLIENTE
           AND F.CD_CGC_CPF = :CD_CGC_CPF)  COMENTARIO
  WHERE COMENTARIO.CD_FLUXO = :CD_FLUXO

Query query = session.createSQLQuery(sql);

O erro gerado:

11/05/2011 18:40:45 com.sun.faces.lifecycle.InvokeApplicationPhase execute
WARNING: /paginas/inicio.xhtml @157,60 tabChangeListener="#{tabViewControle.onTabChange}": org.hibernate.exception.SQLGrammarException: could not execute query
javax.el.ELException: /paginas/inicio.xhtml @157,60 tabChangeListener="#{tabViewControle.onTabChange}": org.hibernate.exception.SQLGrammarException: could not execute query
	at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:104)
	at org.primefaces.component.tabview.TabView.broadcast(TabView.java:222)
	at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:775)
	at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1267)
	at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:82)
	at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
	at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:312)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:306)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:79)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:244)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:541)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:383)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:243)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:188)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:288)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.loader.Loader.doList(Loader.java:2231)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
	at org.hibernate.loader.Loader.list(Loader.java:2120)
	at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
	at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
	at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
	at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
	at br.com.rkam.dao.ParecerLimiteCreCliDAO.listarParecerLimiteCreCliPorFluxo(ParecerLimiteCreCliDAO.java:72)
	at br.com.rkam.web.controle.TabViewControle.carregarTelaParecerAprovacao(TabViewControle.java:604)
	at br.com.rkam.web.controle.TabViewControle.onTabChange(TabViewControle.java:270)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.apache.el.parser.AstValue.invoke(AstValue.java:262)
	at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:278)
	at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:98)
	... 25 more
Caused by: java.sql.SQLException: ORA-00907: missing right parenthesis

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1038)
	at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1133)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
	at org.hibernate.loader.Loader.doQuery(Loader.java:697)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
	at org.hibernate.loader.Loader.doList(Loader.java:2228)
	... 41 more
javax.faces.FacesException: /paginas/inicio.xhtml @157,60 tabChangeListener="#{tabViewControle.onTabChange}": org.hibernate.exception.SQLGrammarException: could not execute query
	at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:90)
	at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
	at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:312)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:306)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:79)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:244)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:541)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:383)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:243)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:188)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:288)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: javax.el.ELException: /paginas/inicio.xhtml @157,60 tabChangeListener="#{tabViewControle.onTabChange}": org.hibernate.exception.SQLGrammarException: could not execute query
	at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:104)
	at org.primefaces.component.tabview.TabView.broadcast(TabView.java:222)
	at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:775)
	at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1267)
	at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:82)
	... 21 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.loader.Loader.doList(Loader.java:2231)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
	at org.hibernate.loader.Loader.list(Loader.java:2120)
	at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
	at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
	at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
	at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
	at br.com.rkam.dao.ParecerLimiteCreCliDAO.listarParecerLimiteCreCliPorFluxo(ParecerLimiteCreCliDAO.java:72)
	at br.com.rkam.web.controle.TabViewControle.carregarTelaParecerAprovacao(TabViewControle.java:604)
	at br.com.rkam.web.controle.TabViewControle.onTabChange(TabViewControle.java:270)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.apache.el.parser.AstValue.invoke(AstValue.java:262)
	at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:278)
	at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:98)
	... 25 more
Caused by: java.sql.SQLException: ORA-00907: missing right parenthesis

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1038)
	at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1133)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
	at org.hibernate.loader.Loader.doQuery(Loader.java:697)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
	at org.hibernate.loader.Loader.doList(Loader.java:2228)
	... 41 more
#2

O erro se refere a algum parênteses que está faltando, mas você já deve saber disso. Procurei e realmente não consegui achar nenhum parênteses que foi aberto mas não foi fechado.
Resolvi buscar no google a mensagem de erro e descobri isso: http://www.dba-oracle.com/sf_ora_00907_missing_right_parenthesis.htm
Existe algum bug no Oracle que gera este erro, mas já foi corrigido a partir da versão 10.2.0.3, qual versão você está usando?
A correção que ele sugere não faz sentido (para mim pelo menos) no que vi no seu código, mas se você conseguir entender, talvez seja a solução do seu problema.

#3

Bom dia visola!

Na tentativa de contornar o problema do Parênteses fizemos uma alteração no sql. Agora dá erro de caracter inválido! Segue abaixo o sql:

hql.append("	SELECT CD_CGC_CPF,	 ");
hql.append("	       CD_BASE_CLIENTE,	 ");
hql.append("	       DT_ATUALIZACAO,	 ");
hql.append("	       DS_TIPO_PARECER,	 ");
hql.append("	       NM_USUARIO,	 ");
hql.append("	       ID_PARECER,	 ");
hql.append("	       DS_PARECER,	 ");
hql.append("	       DS_HORARIO,	 ");
hql.append("	       CD_FLUXO	 ");
hql.append("	  FROM (SELECT (TO_CHAR(ROWNUM, '00') || ' - dia ' ||	 ");
hql.append("	               TO_CHAR(T.DT_ATUALIZACAO, 'dd/mm/yyyy hh24:mi:ss')) as DS_HORARIO,	 ");
hql.append("	               F.CD_BASE_CLIENTE,	 ");
hql.append("	               F.CD_CGC_CPF,	 ");
hql.append("	               T.CD_SEQUENCIA_FLUXO CD_FLUXO,	 ");
hql.append("	               T.DT_ATUALIZACAO,	 ");
hql.append("	               NVL(U.NM_USUARIO, PR.DS_PROGRAMA) as NM_USUARIO,	 ");
hql.append("	               P.DS_TIPO_PARECER,	 ");
hql.append("	               DECODE(T.ID_SITUACAO_PARECER,	 ");
hql.append("	0	 ");
hql.append("	                      'PENDENTE',	 ");
hql.append("	1	 ");
hql.append("	                      'APROVADO',	 ");
hql.append("	2	 ");
hql.append("	                      'APROVADO C/ EXCEÇÃO',	 ");
hql.append("	3	 ");
hql.append("	                      'REPROVADO',	 ");
hql.append("	4	 ");
hql.append("	                      'AGUARDANDO COMPLEMENTO',	 ");
hql.append("	                      'NÃO IDENTIFICADO') as ID_PARECER,	 ");
hql.append("	               T.DS_PARECER	 ");
hql.append("	          FROM PARECER_LIMITE_CRE_CLI   T,	 ");
hql.append("	               TIPO_PARECER_APV_CRE_CLI P,	 ");
hql.append("	               CTL_FLX_ALR_LIM_CRE_CLI  F,	 ");
hql.append("	               USUARIO                  U,	 ");
hql.append("	               PROGRAMA                 PR	 ");
hql.append("	         WHERE T.CD_TIPO_PARECER = P.CD_TIPO_PARECER	 ");
hql.append("	           AND T.CD_SEQUENCIA_FLUXO = F.CD_SEQUENCIA_FLUXO	 ");
hql.append("	           AND SUBSTR(T.CD_USUARIO, 1, 12) = SUBSTR(U.CD_USUARIO(+), 1, 12)	 ");
hql.append("	           AND SUBSTR(T.CD_USUARIO, 1, 12) = PR.CD_PROGRAMA(+)	 ");
hql.append("	           AND T.CD_USUARIO = U.CD_USUARIO(+)	 ");
hql.append("	           AND T.CD_USUARIO = PR.CD_PROGRAMA(+)	 ");
hql.append("	           AND F.CD_BASE_CLIENTE = :cdBase	 ");
hql.append("	           AND F.CD_CGC_CPF = :cgcCpf)  COMENTARIO	 ");
hql.append("	  WHERE COMENTARIO.CD_FLUXO = :cdFluxo	 ");

Query query = session.createSQLQuery(sql);

O erro gerado é java.sql.sqlexception ora-00911 invalid character

#4

Bem complicado isso aí hein? Como eu não tenho um parser de SQL na cabeça e nem você, sugiro pegar a SQL e quebrá-la na menor parte funciona possível e depois ir adicionando aos poucos para ver o que funciona e tentar encoontrar o erro assm. Complicado mas um SQL desse tamanho não vai ter muito jeito. :-/

#5