[jboss-jira] [JBoss JIRA] (JBAS-4733) "Another ORA-00907: missing right parenthesis" bug with Oracle

[Thomas Scheibelreiter (JIRA)]

    [ https://issues.jboss.org/browse/JBAS-4733?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12996796#comment-12996796 ] 

Thomas Scheibelreiter commented on JBAS-4733:
---------------------------------------------

Hi, since this issue pops up at the top of Google search results, I want to inform everybody who stumbles upon this problem, that it persists in later releases. But there is a workaround: Leave the "SELECT xyz" from the subquery. Without it, there are no parenthesis created in the SELECT-clause.

> "Another ORA-00907: missing right parenthesis" bug with Oracle
> --------------------------------------------------------------
>
>                 Key: JBAS-4733
>                 URL: https://issues.jboss.org/browse/JBAS-4733
>             Project: Application Server 3  4  5 and 6
>          Issue Type: Bug
>      Security Level: Public(Everyone can see) 
>          Components: JPA / Hibernate
>    Affects Versions: JBossAS-4.2.1.GA
>         Environment: hibernate-entitymanager.jar (3.3.1ga)
> hibernate-annotations.jar (v 3.3.0ga)
> hibernate-commons-annotations.jar (within Hibernate Annotations.rar\lib)
> ejb3-persistence.jar (within Hibernate Annotations.rar\lib)
> hibernate3.jar (v 3.2.5.ga)
> Oracle 10g
> JBossAS-4.2.1GA
>            Reporter: Dean Pullen
>            Assignee: Steve Ebersole
>             Fix For: No Release
>
>   Original Estimate: 1 hour
>  Remaining Estimate: 1 hour
>
> I'm using JBoss 4.2.1GA, with the Hibernate environment described above, using Oracle 10g, and attempting to perform a query.
> I've been receiving a "ORA-00907: missing right parenthesis" error when attempting to perform the query.
> Now a similar bug had this down to Hibernate using the 'as' keyword in the query, aliasing the table name - something that has been fixed in Hibernate 3.3.0ga.
> (Aliasing a table in the SQL spec is optional, Oracle doesn't support it)
> But I still get the same error. The query still contains the 'as' keyword (but not aliasing a table)
> Here is an example:
> I have two tables. CSIUsers and CSIUserSurveys.
> There is a one-to-many mapping between CSIUsers to CSIUserSurveys.
> Query query = entityManager.createQuery("FROM " + CSIUser.class.getSimpleName()
> + " cu LEFT JOIN cu.csiUserSurveys us"
> + " WHERE NOT EXISTS (SELECT us FROM cu.csiUserSurveys us WHERE us.csiUserId = cu.csiUserId)");
> Result:
> [[14 Sep 2007 15:43:52] DEBUG org.hibernate.util.JDBCExceptionReporter - could n
> ot execute query [select csiuser0_.CSI_USER_ID as CSI1_15_0_, csiusersur1_.CSI_U
> SER_ID as CSI1_16_1_, csiusersur1_.SURVEY_TYPE_CODE as SURVEY2_16_1_, csiuser0_.
> COMPANY_CODE as COMPANY2_15_0_, csiuser0_.EMAIL_ADDRESS as EMAIL3_15_0_, csiuser
> 0_.FAILED_LOGINS as FAILED4_15_0_, csiuser0_.LOCALE as LOCALE15_0_, csiuser0_.PA
> SSWORD as PASSWORD15_0_, csiuser0_.PURCHASE_DATE as PURCHASE7_15_0_, csiuser0_.S
> TATUS as STATUS15_0_, csiuser0_.USER_NAME as USER9_15_0_, csiuser0_.USER_PRIVILE
> GE as USER10_15_0_, csiusersur1_.SURVEY_INVITE_DATE as SURVEY3_16_1_ from CSI_US
> ERS csiuser0_ left outer join CSI_USER_SURVEYS csiusersur1_ on csiuser0_.CSI_USE
> R_ID=csiusersur1_.CSI_USER_ID where not (exists (select (csiusersur2_.CSI_USER_
> ID, csiusersur2_.SURVEY_TYPE_CODE) from CSI_USER_SURVEYS csiusersur2_ where csiu
> ser0_.CSI_USER_ID=csiusersur2_.CSI_USER_ID and csiusersur2_.CSI_USER_ID=csiuser0
> _.CSI_USER_ID))]
> java.sql.SQLException: ORA-00907: missing right parenthesis 



--
This message was sent by Atlassian JIRA
(v6.3.1#6329)